Calculating APR on a Mortgage

S

Sean B

Can anybody help me determine APR using the following example:

30 year amortization
7% Rate
$100,000.00 loan amount
 
F

Fred Smith

Haven't you already specified the APR (7%)?

If you want to check to see if 7% is correct, we would need to know the payment.
You can also calculate it yourself using the Rate function.
 
S

Sean B

No, APR is different than interest rate, it takes into account the costs
associated with the loan and adds that to the actual interest charged during
the life of the loan. Then it backs into what the interst rate is given those
costs + interest rate = APR. I just don't have a clue how to get there from
here. any ideas? anybody?
 
S

Sean B

Payment would be $665.30
I used the PMT calc for that which is no big deal, but I gotta tell ya, the
APR thing has me stumped...
Thanks Fred and anybody else out there who can help!
Sean
 
P

Peo Sjoblom

Then the APR is 6.78%

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
 
S

Sean B

Thank you Peo. Now can you help me with the excel formula so I can
incorporate it into a spreadsheet that has all of the other info present? I
could email you a bare bones setup...
Thanks (hopefully),

Sean
 
P

Peo Sjoblom

The formula would be

=RATE(30*12,-650.30,100000)*12

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
 
S

Sean B

Thank you Peo!

Peo Sjoblom said:
The formula would be

=RATE(30*12,-650.30,100000)*12

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
Click to expand...
 
J

joeu2004

Sean said:
No, APR is different than interest rate, it takes into account the costs
associated with the loan and adds that to the actual interest charged during
the life of the loan. Then it backs into what the interst rate is given those
costs + interest rate = APR.
Click to expand...

That is correct. So I presume you want to know how to compute the US
"Truth in Lending" APR. Right?

With respect to US mortgages, "APR" is a technical term defined in
Regulation Z and Appendix J. It should not be confused with the
(nominal) annual interest rate.

There seems to be two common ways to compute APR, exhibited by the
following online calculators:
http://www.mortgageloan.com/calculators/aprcalc.cgi [1] and
http://www.lendingtree.com/cec/tools/Calculators/MortgageAPR.asp [2].

For the first method, the actual payment is determined based on the
actual loan amount. Then an effective monthly rate is computed based
on the actual loan amount minus the applicable closing costs, which is
then multiplied by 12 per US regulation. In your example, the APR
would be:

=12 * rate(12*30, pmt(7%/12, 12*30, -100000), -100000+2000)

The result of the formula (7.2014%) matches the result of the
mortgageloan.com calculator.

For the second method, a fictious payment is determined based on the
actual loan amount plus the applicable closing costs. Then an
effective monthly rate is computed based on the actual loan amount,
which is again multiplied by 12. Note that this fictious payment is
solely for the purpose of computing an APR; it is not the actual
payment. In your example, the APR would be:

=12 * rate(12*30, pmt(7%/12, 12*30, -100000-2000), -100000)

The result of that formula (7.1973%) matches the result of the
lendingtree.com calculator.

Despite the apparent difference in the results of the two methods, note
that in this case at least, they are the same when rounded to a basis
point (1/100 of a percent), which is well within the accuracy
requirements of the federal regulations. I have not examined the
underlying math formulas to see if perhaps the two methods are in fact
identical mathematically.

Having said all that, it should be noted that despite attempts by
federal law to create a standardized "rate" to use when comparing loan
offerings, the fact is: the APR is virtually useless, IMHO. The main
reason is that different lenders include different fees in what I
called "applicable closing costs". Federal regulations specify only a
minimum set. More importantly, federal regulations permit the
exclusion of many fees that are highly variable among lenders. As a
consequence, the APR fails to give a truly comparable value of the cost
of lending. Refer to http://www.mtg-net.com/sfaq/faq/apr.htm for
details.

Bottom line: Other than as an academic exercise, ignore the APR.


-----
[1] At one time, I found an online explanation of the first APR method,
exhibited by http://www.mortgageloan.com/calculators/aprcalc.cgi .
But, sigh, I cannot find that link again.

[2] An explanation of the second APR method, exhibited by
http://www.lendingtree.com/cec/tools/Calculators/MortgageAPR.asp , can
be found by clicking View Report on that web page.
 
J

joeu2004

Peo said:
Then the APR is 6.78%
Click to expand...

Peo Sjoblom wrote later:
The formula would be
=RATE(30*12,-650.30,100000)*12
Click to expand...

That RATE() formula does result in 6.78%. Bjut I am curious: where
does $650.30 come from? Just a typo?

The OP computed a payment of $665.30 based on the terms of the loan,
namely PMT(7%/12, 12*30, -100000), I presume. And I believe that is
the correct payment to use for the loan.

Some methods of computing the APR do involve computing a fictitious
payment based on a modified loan amount. But the one method I am
familiar with would compute $678.61.

If you are using some other method, could you elaborate for my
edification?
 
Top