Toby wrote:
[Toby wrote earlier:]
Example Paramaters:
loan amount: 150,000
Term: 30 years
Rate: 6.5%
Closing costs that affect APR: 2,000
[....]
The correct APR should be 7.4459
Who says? That is, where does that APR figure come from?
What jurisdiction are you talking about? For example, US or Canadian?
What is the periodic payment? What is the frequency of payment; for
example, monthly, semimonthly or biweekly?
For a US loan with monthly payments based on the parameters above, the
correct APR is:
=12 * RATE(30*12, ROUND(PMT(6.5%/12, 30*12, -150000), 2),
-(150000-2000))
which results in 6.6295%, whether or not PMT(...) is rounded.
Conversely, an APR of 7.4459% corresponds to total "loan fees" of about
$13,683.50. Actually, between $13,682.83 and $13,684.17 because
"7.4459%" can be the rounded value of a number between 7.44585% and
7.445949...9%. This can be computed as follows:
=150000 - PV(7.4459%/12, 30*12, -ROUND(PMT(6.5%/12, 30*12, -150000),
2))
That seems to be too great a difference to be explained by "hidden loan
fees". I wonder if the loan amount above ($150,000) does not take the
down payment into account. But if that is the case, that changes
everything above.
PS:
I really do not know how Canadian's determine APR. But based on a
comment at one web site [1], I believe the following does the trick.
=2 * (FV(RATE(30*12, ROUND(PMT(RATE(6, 0, -1, 1+6.5%/2), 30*12,
-150000), 2), -(150000-2000)), 6, 0, -1) - 1)
That results in 6.6324%; 6.6323% if PMT(...) is not rounded.
Footnote
-------------
[1]
http://www.rbcroyalbank.com/products...iew_rates.html
states that if there are no borrowing charges, the APR and interest
rate are the same. That led to me to develop the formula above for the
Canadian APR. I know the PMT(RATE(...)...) part is correct. And
2*(FV(...)-1) works fine when there are no borrowing charges; that is,
it returns the original interest rate. But I could not find a Canadian
mortgage calculator online that computes the APR with borrowing
charges, and I could not find a web page that explains the math or
Excel formulas for computing the Canadian APR. So I cannot confirm my
computation.