APR annual percentage rate formula/function

  • Thread starter Thread starter warnimont
  • Start date Start date
W

warnimont

Is there a function, or perhaps you know a formula that
will calc an APR for my home loan which includes fees.
I dont want a canned internet calculator, i'd like the
formula and/or function.
 
Hi warnimont!

Use the RATE function:

Description:
Returns the interest rate per period of an annuity
Syntax:
=RATE(nper,pmt,pv,fv,type,guess)
Nper: The number of periods
pmt: The payment made each period
pv: The Present Value (the loan)
fv: Future Value (Optional)
type: 0 = Payment in arrears; 1 = payment in advance (Optional)
guess: a guide for the function to work on (Optional and not often
required)

Typically:

Loan 100000
Term 20 years paid monthly
payments 740 inclusive of fees
fv: no balloon
type: payments at the end of each month
fees 2% of loan

=RATE(240,-740,100000*(1-2%),0,0)
Returns: 0.555111736656528%

This is effective rate per month.

So APR:
=RATE(240,-740,100000*(1-2%),0,0)*12
Returns: 6.66134083987834%

And Annual Effective:
=(1+RATE(240,-740,100000*(1-2%),0,0))^12-1
Returns: 6.86852988699223%
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
What about when the APR is also effected by PMI? For
example, a simple 30 year loan of $100,000 at 6%, let's
assume no fees so we can just concentrate on how PMI
effect APR, has a payment of $599.55. If the sale price
was also $100,000, the lender will charge PMI until the
loan balance drops below 80%, or here $80,000. The PMI
payment is $65.83. If there was no PMI the APR would
equal the rate at 6%, and if the PMI wasn't canceled
until the loan was paid off, the PMI is still easy to
find at 7.001%. The problem is, how do you calculate the
APR when the PMI drops off in the middle? It's easy to
find the number of payments that have PMI and the number
that don't, and also to add up all of the interest over
the life of the loan or even each month etc., but no
matter what I do I can't seem to get the same APR that
the computer's loan software, the one loan companies use,
finds. Here's some examples of the PMI dropping of at
different percentages of the starting loan amount, and
the APR, all with the loan criteria from above.
1) % at which PMI cancels
2) # of payments that have PMI
3) APR
4) Total finance charge (Interest + PMI payments)
1) 2) 3) 4)
100 0 6.000 115838.44
90 82 6.421 121236.50
80 140 6.629 125054.64
70 185 6.749 128016.99
60 221 6.825 130386.87
50 252 6.878 132427.60
40 279 6.917 134205.01
30 303 6.946 135784.93
20 324 6.969 137167.36
10 343 6.987 138418.13
0 360 7.001 139537.24

Please let me know if you can come up with something.
Thanks!
Scott
 
Back
Top