I don't believe there is nothing wrong with what I posted previously.
But
it occurred to me that I made some assumptions about the context of the
inquiry that might be incorrect. Let me take a step back and explain
things from a different perspective.
Note: Again, all of the following is from a US perspective.
B6 - $139,000.00 (loan amount)
B7 - 240 (total number of payments)
B8 - 12 (number of payments per year)
B11 - 7.00% (interest rate)
B15 - 365/360 (Interest method)
[....]
I am getting $1107.43 for the monthly payment, but it should be
$1086.22
If a lender has told you that the monthly payment is $1086.22, then the
true annual interest rate should be about 7.1023%, computed by:
=12*RATE(B7, C2, -B6)
(Assume that formula is in C1. Assume the monthly payment, 1086.22, is
in
C2.)
If the interest method is 365/360, then the true annual interest rate
(7.1023%) was determined by dividing the advertised rate by 360 and
multiplying by 365. So the advertised rate should be about 7.0050%,
computed by:
=C1/365 * 360
I believe the minimum required precision for advertised and disclosed
rates is 1/100th of percentage point. In any case, a stated interest
rate
is considered accurate if it is with +/- 0.125%. So 7.0050% is
consistent
with the rate stated in B11.
F15 - $0.00 (Fees for the mortgage, if any)
To calculate the APR, I am using the following:
=RATE(B7,PMT(B11/12,B7,B6),B6-F15)*12
My results are 7% and it should be 7.126%.
If a lender has told you that the APR is about 7.126%, taking mortgage
loan fees into account, then the loan fees (F15) are about $253.34,
computed by:
=PV(C3/12, B7, C2) + B6
(Assume the APR is in C3. Recall that C2 contains the lender's monthly
payment, 1086.22.)
Alternatively, if the loan fees are truly zero (!), the difference
between
the true annual interest rate (7.1023%) and the APR (7.126%) might be
due
to additional monthly charges, e.g. PMI. The additional monthly
charges
are about $1.98 (!), computed by:
=PMT(C3/12, B7, -B6) - PMT(C1/12, B7, -B6)
(Recall that C1 contains the true annual interest rate.)
Note: Off-hand, that amount seems too low to be PMI. But I'm not
taking
the time to vet it.
And of course, the difference between the APR and the true annual
interest
rate might be due to a combination of loan fees and monthly charges.
That
is impossible to determine without knowledge of those specifics.
All of this is quite speculative. As you can see, there are a lot of
variables.
If you already have a loan, or if you are considering a loan, you
should
be able to fill in the details by asking the lender or by reading the
disclosure documentation carefully.
1st - I am using the following number:
A38 - 0.0735439979799297
This number is calculated from the formula:
=IF(B15="365/365",1*(1+B11/365)^365-1,
IF(B15="30/360",1*(1+B11/360)^30-1,
IF(B15="365/360",1*(1+B11/360)^365-1,
IF(B15="30/365",1*(1+B11/365)^30-1,"")))).
[....]
So, the monthly payment is calculated as
=IF(B15="365/365",PMT(A38/12,B39*12,-B6),
IF(B15="365/360",PMT(A38/12,B39*12,-B6),
IF(B15="30/360",PMT(A38,B39*12,-B6),
IF(B15="30/365",PMT(A38,B39*12,-B6),""))))
Giving this more thought -- although I still believe it does not apply
to
US mortgage loans -- I think those formulas are just plain wrong.
If the advertised simple annual interest rate (not the APR if other
fees
are included) is in B11, then the true annual interest rate based on
the
various methods is (in A38):
=IF(OR(B15={"365/365","30/360"}), B11,
IF(B15="365/360", B11/360 * 365,
IF(B15="30/365", B11/365 * 30*12, NA())))
Since that always results in an annual rate, the monthly payment
formula
is simply:
=PMT(A38/12, B7, -B6)
(Of course, I believe that should be rounded or truncated to the 2
decimal
places; that is, the smallest coin of the realm.)
Does this help? Any other questions?
----- original message -----
I am working on a worksheet that shows payment information for
mortgages.
However, the results I get for the payment is off by just a little
bit
(nothing consistent) and the APR is consistently wrong (always the
same
as
the interest rate).
It might help to know what jurisdiction you are talking about. My
comments below are for the US. Professional US loans are controlled
by
the Truth in Lending Act, aka "Reg Z". For the APR calculation, see
Appendix J at
http://www.fdic.gov/regulations/laws/rules/6500-1400.html .
(Technically, private loans are not regulated. But prudent
individuals
will follow Truth in Lending regulations to minimize confusion.)
F15 - $0.00 (Fees for the mortgage, if any)
To calculate the APR, I am using the following:
=RATE(B7,PMT(B11/12,B7,B6),B6-F15)*12
[....] Every time I use this
calculation, the APR comes out the same as the Interest Rate.
Your calculation is correct (except for a nitpick).
The APR is the same as the annual interest rate in this case because
it
should be, since there are no other finance charges. If F15 were
non-zero, you would see a change.
FYI, my nitpick is: in the real world, PMT() should be rounded (or
truncated) at least to the smallest coin of the realm because, for
example, in the US, we cannot pay fractional pennies.
But that has other consequence. It's a detail that might be better to
address after you have everything else under control to your
satisfaction.
My results are 7% and it should be 7.126%.
Who says?
If the APR should be about 7.126%, the loan fees (F15) should be about
$1,346.25.
1st - I am using the following number:
A38 - 0.0735439979799297
This number is calculated from the formula:
=IF(B15="365/365",1*(1+B11/365)^365-1,IF(B15="30/360",1*(1+B11/360)^30-1,IF(B15="365/360",1*(1+B11/360)^365-1,
IF(B15="30/365",1*(1+B11/365)^30-1,"")))).
If payments are made monthly, the use of anything but 30/360 is
questionable in the US. A Google search reveals that there might even
be
case law against it.
I don't want to get into the legal issues here. But if you are a
professional lender, it would behoove you to consult with an attorney.
In any case, the lender is free to determine the annual interest rate
in
any way that he wishes. But the periodic rate is determined by
dividing
the annual interest rate by the payment frequency, in accordance with
Appendix J of the Reg Z.
So, the monthly payment is calculated as
=IF(B15="365/365",PMT(A38/12,B39*12,-B6),IF(B15="365/360",PMT(A38/12,B39*12,-B6),
IF(B15="30/360",PMT(A38,B39*12,-B6),IF(B15="30/365",PMT(A38,B39*12,-B6),""))))
B39 = 20 (number of years)
I am getting $1107.43 for the monthly payment, but it should be
$1086.22.
Again, who says?
1107.43 is the correct number because, as I noted above, the periodic
rate is determined by dividing the annual interest rate by the payment
frequency, exactly as you did.
However, if that is how you are computing the monthly payment, that is
what you must use in the RATE() formula above as well. Your annual
interest rate is about 7.3544%, not 7%.
(Note: Of course, if you advertise or disclose only 7% instead of
7.3544%, there might be another legal issue that you need to discuss
with
an attorney.)
Returning to 1086.22 ....
For the 365/360 mode, if I replace A38/12 with (1+A38)^(1/12)-1, I do
get
about 1087.50 when B15 is "365/360".
That might suggest how 1086.22 was derived. But that would be wrong.
Again, the periodic rate is determined by dividing the annual interest
rate by the payment frequency, not by taking the 1/12th root.
Does this help? Any other questions?
----- original message -----
I am working on a worksheet that shows payment information for
mortgages.
However, the results I get for the payment is off by just a little
bit
(nothing consistent) and the APR is consistently wrong (always the
same
as
the interest rate).
Here is an example of what I have:
B6 - $139,000.00 (loan amount)
B7 - 240 (total number of payments)
B8 - 12 (number of payments per year)
B11 - 7.00% (interest rate)
B15 - 365/360 (Interest method)
F15 - $0.00 (Fees for the mortgage, if any)
To calculate the APR, I am using the following:
=RATE(B7,PMT(B11/12,B7,B6),B6-F15)*12
My results are 7% and it should be 7.126%. Every time I use this
calculation, the APR comes out the same as the Interest Rate.
Also, my monthly payment is incorrect. To calculate it, I am doing
the
following:
1st - I am using the following number:
A38 - 0.0735439979799297
This number is calculated from the formula:
=IF(B15="365/365",1*(1+B11/365)^365-1,IF(B15="30/360",1*(1+B11/360)^30-1,IF(B15="365/360",1*(1+B11/360)^365-1,
IF(B15="30/365",1*(1+B11/365)^30-1,"")))). I was told on a previous
post
that this is how I would work with the different interest types.
So, the monthly payment is calculated as
=IF(B15="365/365",PMT(A38/12,B39*12,-B6),IF(B15="365/360",PMT(A38/12,B39*12,-B6),IF(B15="30/360",PMT(A38,B39*12,-B6),IF(B15="30/365",PMT(A38,B39*12,-B6),""))))
B39 = 20 (number of years)
I am getting $1107.43 for the monthly payment, but it should be
$1086.22.
Does anyone see what I am doing wrong? I have looked at this until I
am
blue in the face and have no idea what to do.
Thank you in advance for your time.