Caculating A.P.R. in a mortgage

[John B]

Can some one help me please.
I tried Rate(term,payment,sum(loan
amount+monthlyinterest+tax service+mortgage insurance))
*12 and my APR is lower than the interest raate. It
should be higher.
What part of this formular is incorrect?

 

[Norman Harker]

Hi John!

Assuming the persona of the borrower:

Deduct initial setup costs from the amount of the loan
Deduct (eg) monthly charges from the negatively signed loan

Example

I borrow 100000 and pay set up costs of 2% of the loan. I also pay $30
per month account service charge. The loan is over 10 years with first
repayment 1 month after draw down of loan. The quoted APR is 6%.

First the repayments:
A1:
=PMT(6%/12,10*12,100000,0,0)
Returns: -1110.20501941652

Then the APR:
B1:
=RATE(10*12,A1-30,100000-(100000*2%),0,0,0)*12
Returns: 7.04634008500682%

Or as one formula:
=RATE(10*12,PMT(6%/12,10*12,100000,0,0)-30,100000-(100000*2%),0,0,0)*1
2
Returns: 7.04634008500682%

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

 

[JohnB]

Thank you for your answer. What a small world an Aussie in
USA getting an answer from down under.
Thanks mate.

 

[Norman Harker]

Hi John!

Pleased to help especially a fellow Aussie away from the civilizing
influence of cricket <vbg>

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