Excel Finance Trick #6: RATE function and Loan Points

I am weighing weather I should pay off my house or invest the money I have.

Is there somewhere I can download an Excel sheet that would figure my home loan intrest and another that would figure how much I would get on an investment (in a 1 year period)?

I know the fuctions are in excel but I don't know which to use.

I don't think the explanations of what they do are very good so if I can download something it would be much easier.

Thank You

Please need your help
Loan of 100,000 rate 3.65% for 10 years and a balloon of 10,000
The first two years the rate is 3.50% and from the third year to 10th for every year increase 0.5% (3th = 4.00%, 4th 4.5% etc).
I cannot use the PMT as this formula is for fix rate.
Thank you in advance.

Hi,

I need to amortise a loan and charge a fixed spread over a 3 month LIBOR rate to account for the credit risk. Total charge will be 3 Month + fixed Spread- does anyone I have an example I could take a look at?

Thanks,

Paul

Hey all.. I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?

Here is the effective rate calcs on a random normal amortization loan:

Code:

this is in B2, and answer is 7%
=RATE(B4*B5,-((B3+B7)/B6),B7)*12

B3 = Total*Interest	  279017.8
B4 = #*Years*in*Loan	  30
B5 = #*Payments*/*Year	  12
B6 = Total*Payments	  360
B7 = Beginning*Principal  200000
B8 - Ending*Balance	  0

problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan and I have no clue how to do that... hoping I'm clear..

Here is the example I'm working on... A client's loan is the following:
Loan amount - 131,538
interest rate - 6.15
30 year amortization
10 years interest only

normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan. Geez I hope this makes sense.. LOL.. Thanks for any help!

John

I need to write an Excel function that returns best price of a government bond.

The actual handwritten formula is quite complicated, impossible to display
here, and the input values for this formula are-----------------
Accrued days, Number of coupon period days, Days from settlement to next
coupon date, coupon periods per year, number coupon periods between
settlement & redemption, annual yield, discount rate.

Am unable to find answer to this question. In one site I found EMI comparison, which calculates interest rate as i wanted but am unable to find the formula used there. Please Help!!

I've successfully created a simple interest amortization schedule using the PMT() function, where a fixed payment per period is returned. However, I am trying to setup a schedule where each payment_per_period (p(n), p(1), p(2), p(3), etc.) incrementally increases in a linear fashion. In this function I will input a fixed monthly interest rate (i), the beginning principal (P), and the number of periods (N). The output should be such that i+P=p(n) and p(2)-p(1)=p(3)-p(2)=p(4)-p(3)...etc. Essentially I need N payments @ i rate where the difference between p(n) and p(n-1) does not vary.

I'm sure there's a better way to explain what I'm trying to accomplish, but this is why I'm reaching out to you. Please help. Thank you.

Hi,

Can anyone explain the Rate function to me please? I'm trying to get an idea of the theory behind it.

I want to write tsql code to perform the same function.

Cheers

-Alan

Hey everyone I am new to programing in Excel and I do not know enough to wright a function that finds variance of a portfolio. This is what i have so far its messy and does not work.

Function VARPORT(Sa,Wa,Sb,Wb,Sc,Wc,Sd,Wd,Se,We)

va=VAR(Sa)
vb=VAR(Sb)
vc=VAR(Sc)
vd=VAR(Sd)
ve=VAR(Se)


cab=Covar(va,vb)
cac=Covar(va,vc)
cad=Covar(va,vd)
cae=Covar(va,ve)
cbc=Covar(vb,vc)
cbd=Covar(vb,vd)
cbe=Covar(vb,ve)
ccd=Covar(vc,vd)
cce=Covar(vc,ve)
cde=Covar(vd,ve)

VAPORT=((Wa^2)*va)+((Wb^2)*vb)+((Wc^2)*vc)+((Wd^2)*vd)+((We^2)*ve)+(2*Wa*Wb*cab)+(2*Wa*Wc*cac)+(2*Wa *Wd*cad)+(2*Wa*We*cae)+(2*Wb*Wc*cbc)+(2*Wb*Wd*cbd)+(2*Wb*We*cbe)+(2*Wc*Wd*ccd)+(2*Wc*We*cce)+(2*Wd*W e*cde)
End Function

The S stands for a list of stock returns.
The W is what percentage of the portfolio is spent on that stock.
The Covar and Var functions are to calculate the variance of each stock and the covariance between the five.
The three letters are C for covariance and then what stock will be looked at.

This is very confusing to me and I know I just made a big mess, can anyone help or point me int he right direction.

Work four summers, starting at age 16

Save the income in a Roth IRA account

Invest it in a simple, low-cost equity portfolio

Simmer slowly for 47 years

Serve ungarnished (and untaxed) at age 67

If your money is invested in common stocks and you achieve the average
compound annual rate on large-capitalization U.S. stocks, 10.7%, your
account will grow to $9,378 at the end of the fourth year. You will be
20 years old. Invested in the same way, with no additional savings, the
account will grow to:
$25,917 by the time you are 30

$71,625 by the time you are 40

$197,943 by the time you are 50

$547,037 by the time you are 60

And $1,114,423 by the time you are 67

is there a function that will give me this answer without using
multiple rows and columns to calculate this.