Mortgage APR Calculation

[Cindy Hanson]

Hi I am looking for a program that automatically
calculates the APR on a loan - can't find it in the Excel
functions.

Cell A has Loan Amount
Cell B has % Interest Rate
Cell C has closing costs

Where can I find the APR formula?
Thanks
Cindy

 

[Norman Harker]

Hi Cindy!

By APR I understand you to mean Annual Nominal compounded monthly. [I
have to specify this as in the UK one legislative definition of APR is
the Annual Effective Rate)

I have:
The loan in A1 [100000]
The Nominal compounded monthly in A2 [5%]
The Closing fees in A3 [2000]
The term in A4 [120]

=RATE(A4,PMT(A2/12,A4,A1,0,0),A1-A3,0,0,0)*12
For my data in [ ] above this returns: 5.4401823103332%


The loan repayments are based on the loan but the effective rate is
based upon the effective loan.



--
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.