APR

Depending on the data at hand, some extraction may be needed.

The basic syntax is: =RATE(nper"#ofPayments", pmt"basePayment", pv"presentValue", fv"futureValue", Type"0=End, 1=beginningOfPeriod", guess"whatYouThingTheRateWillBe")

Odd periods: date of first payment if different from normal payment date as number of odd days. The number of odd days is divided by the length of the normal payment period, that is 30 for monthly. This gives the fractional portion to be added to the actual number of payments as: nper.

Then pmt is the normal payment amount adjusted for the first partial payment if needed.

Then pv is the amount of the loan.

Then fv is not used, it is by default 0, it is the cash you want out after the last payment.

Then Type is when the payments are made, first of the month or last of the month. Note, if the payment is in some portion of the month which is not the first or last day then, type = 0 and is treated as end-of-the-month.

Guess is a tool to help converge the calculation to zero as Excel will only do 20 passes on the calculation, a good guess at the RATE will be between 0 and 1. Note the default guess is 10% or 0.1.

Plug all your data into the RATE formula then multiply the RATE result by 12 the annual period to get the APR or Annual Percentage Rate. JSW

Ok. So i do all this and it does not work. Is nper the total number of payments (ie 48 for a four year loan) or the number in one year? I'm sure this is where I am going wrong.

Simon,

The RATE function returns the period interest of an annuity. As Joe said, there are 6 arguments with that function. To keep things simple, let's say you know how many total pay periods there are in the loan (example, 48, kept in A1); and you know how much each period payment is (example $200, kept in B1); and what the original "present value" of the loan amount was (example $8000, kept in C1). In D1 you could enter
=RATE(A1,B1,C1) to find the period rate (.77%), or
=RATE(A1,B1,C1)*12 to find the annual rate (9.2%), assuming that this loan involves monthly payments, which is common.

Remember to enter B1 (the monthly payment) as a negative number, and format D1 (the formula) with a couple decimal places.

Any help?

Tom Urtis

rate function

can someone tell me how to program the rate function or tell me what's the formula for the rate function