Tristan said:
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/calcs/CRE0.cgi/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.)