Is there a formula to figure mortgage APR in excel?

[Toby]

I am trying to create a simple formula to accurately figure APR. Nothing
have used so far is close to the true answer. Is there a formula for this?
Example Paramaters:

loan amount: 150,000
Term: 30 years
Rate: 6.5%
Closing costs that affect APR: 2,000

 

[Niek Otten]

<is close to the true answer>

If you tell us what you think the "true answer" is, it might save a lot of time.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to create a simple formula to accurately figure APR. Nothing
| have used so far is close to the true answer. Is there a formula for this?
| Example Paramaters:
|
| loan amount: 150,000
| Term: 30 years
| Rate: 6.5%
| Closing costs that affect APR: 2,000

 

[joeu2004]

I am trying to create a simple formula to accurately figure APR. Nothing
have used so far is close to the true answer. Is there a formula for this?
Example Paramaters:
loan amount: 150,000
Term: 30 years
Rate: 6.5%
Closing costs that affect APR: 2,000

For a US fixed-rate loan, try:

=rate(30*12, round(pmt(6.5%/12, 30*12, -150000), 2), -(150000-2000))

Also try it without rounding (just pmt(...)).

The formula might need to be modified for certain jurisdictions,
notably Canada. Please indicate.

See my response at
http://groups.google.com/group/micr...2011e7/fd80537c3697f6e7?#doc_fd80537c3697f6e7
for an explanation of why the APR might never be "the true answer", if
by that you mean you are trying to match the results of a mortgage
calculator.

Notably, there might be hidden assumptions about the amount of prepaid
interest.

Finally, another potential mistake just occurred to me: some people
might incorrectly include extraneous amounts in the periodic payment,
such as property taxes and mortgage insurance. Those should not be
included for the purposes of computing a Truth-in-Lending-compliant APR.

 

[joeu2004]

Errata....

=rate(30*12, round(pmt(6.5%/12, 30*12, -150000), 2), -(150000-2000))

That should be ("12 *" added):

=12 * rate(30*12, round(pmt(6.5%/12, 30*12, -150000), 2),
-(150000-2000))

 

[Toby]

The correct APR should be 7.4459

 

[joeu2004]

[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/mortgages/view_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.

 

[joeu2004]

The correct APR should be 7.4459

Who says? That is, where does that APR figure come from?
[....]
For a US loan with monthly payments based on the parameters above, the
correct APR is:
[....]
6.6295%, whether or not PMT(...) is rounded.

Conversely, an APR of 7.4459% corresponds to total "loan fees" of about
$13,683.50.
[....]
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.

All of the above computations ass-u-me a fixed-rate US loan for the
duration of the mortgage term, because you did not specify index and
margin rates, which would be the telltale signs of an ARM. However,
the APR for ARM might very well much higher than 6.63% (rounded).

Also, all of the above computations ass-u-me that payments are regular
and equal, since you did not specify otherwise. But irregular payments
-- for example, fractional periods in the beginning or at the end --
might have a dramatic impact on the APR, even for a fixed-rate loan.

Please be sure that you have provided all of the information possible.