Annual Percentage Rate for Mortgage
 

I am trying to calculate an APR based on a mortgage. The formula that I have
input is as follows:
=RATE(A4,(-(PMT(A2,A4,A1,0,0))),A1+A3,0,0,0)*12
My Data is as follows:
A1 = 150,000.00 (loan amount)
A2 = 5.500 (Interest Rate)
A3 = 3000 (Closing Costs / Fees)
A4 = 360 (Term in months)
As far as I can tell I am entering all of the data in the correct format and
order but continue to receive a #NUM! error. Can anyone tell me what I am
missing or doing wrong. Thanks.

Tristan wrote:
I am trying to calculate an APR based on a mortgage.
The formula that I have input is as follows:
=RATE(A4,(-(PMT(A2,A4,A1,0,0))),A1+A3,0,0,0)*12
My Data is as follows:
A1 = 150,000.00 (loan amount)
A2 = 5.500 (Interest Rate)
A3 = 3000 (Closing Costs / Fees)
A4 = 360 (Term in months)
[...] continue to receive a #NUM! error.

You have several errors.

First, it is helpful to "debug" such problems by
breaking down the expression. If you put PMT() in a
separate cell, I'm sure you will see part of the problem.

The first reason for the #NUM! error is because you have
"5.5" instead of "5.5%".

After you correct that, you will still get a #NUM!
error. That is because you should use PMT(), not -PMT().

The sign of the PMT should be opposite of the sign of
the PV in this case. As you have written the PMT()
parameters, PMT() returns a negative value.

(Note: Some people use PMT(...,-A1) so that PMT is a
positive number. In that case, -PMT() would be correct.)

Those are the mechanical errors. Now for the many
logical errors.

First, you should use A2/12, not A2, to compute the
monthly PMT.

Second, you should use A1-A3, not A1+A3. Refer to
http://www.calcbuilder.com/cgi-bin/c.../yahoo_banking
for an example/explanation.

Finally, bear in mind that this will only approximate
the APR. It will probably not equal the lender's APR,
even if the lender computes it the same way you do.

(Note: There is no agreement on how lender's should
compute the APR.)

The reason is that even A2/12 is an estimate of the
monthly interest rate. I believe the monthly rate is
RATE(12,0,1,-(1+A2)). The good news is: that is less
than A2/12. So if you use A2/12, you will have a more
conservative estimate (i.e, too high).

(Note: Be sure to format all interest rate cells with
at least Percentage 2 normally. But in this case, you
need at least Percentage 3 to see the difference.)

Moreover, I believe that banks now amortize on a daily
basis, even though the payments are monthly.

(Double-check that. My memory might be wrong, or it
might vary from lender to lender.)

I wrote:
I believe the monthly rate is RATE(12,0,1,-(1+A2))

I wish I had written RATE(12,0,-1,1+A2).

The result is the same. But I think -PV and +FV
are conceptually easier to understand in this case.
Morever, the expression is easier to write the second
way.

Thanks for the help Joe, Got it working now.

" wrote:

I wrote:
I believe the monthly rate is RATE(12,0,1,-(1+A2))

I wish I had written RATE(12,0,-1,1+A2).

The result is the same. But I think -PV and +FV
are conceptually easier to understand in this case.
Morever, the expression is easier to write the second
way.