Interest and Payment Calculations

[sg]

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.

 

[JoeU2004]

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 -----

 

[JoeU2004]

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.

 

[JoeU2004]

PS....

To calculate the APR, I am using the following:
=RATE(B7,PMT(B11/12,B7,B6),B6-F15)*12

When computing the APR, you should use the actual payment, 1086.22, which I
put into C2. So your formula should be:

=12*RATE(B7, -C2, B6-F15)

Recall that in my previous posting, I determined that F15 should be about
$253.34, not zero.


----- original message -----

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.

 

[sg]

Thanks for your quick reply. You're correct in that it is for the US. I am
having to do this work for someone else and they are telling me what their
other software is showing as APR and payment amounts so, unfortunately, its
not up to me to say that what I already have is correct.

I will read through your posts and see if I can get my numbers to come out
"right".

I'll post back if I have questions!

PS....

To calculate the APR, I am using the following:
=RATE(B7,PMT(B11/12,B7,B6),B6-F15)*12

When computing the APR, you should use the actual payment, 1086.22, which I
put into C2. So your formula should be:

=12*RATE(B7, -C2, B6-F15)

Recall that in my previous posting, I determined that F15 should be about
$253.34, not zero.


----- original message -----

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.

 

[JoeU2004]

I am having to do this work for someone else and they are
telling me what their other software is showing as APR and
payment amounts

Matching the results of other software can be very tricky. As you say, it
is not a question of the right or wrong way to do things, but simply how the
original software interpreted things.

You might discover other areas that require interpretation. I will
summarize and build upon what I wrote before.

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)
[....]
A38 - 0.0735439979799297 [[true interest rate -- J]]
[....]
B39 = 20 (number of years)

I presume that B8 can be any of the following payment frequencies: 1, 2, 4,
12, 24, 26 or 52.

So I presume that B7 is:

=B39 * B8

Expanding on what I wrote previously, A38 should be:

=IF(OR(B15={"30/360","365/365","actual/365","actual/actual"}), B11,
IF(OR(B15={"365/360","actual/360"}), B11/360 * 365,
IF(OR(B15={"30/365", "30/actual"}), B11/365 * 30*12, NA())))

Note: Some software might use 366 for "actual" instead of 365. Other
variations are possible. Remember: this adjustment is outside the scope of
US regulations. In any case, you notice minor discrepancies when using
"actual" interest methods.

Ostensibly (but see comments below), the periodic payment (C2) is:

=PMT(A38/B8, B7, -B6)

which should be rounded or truncated according to the lender's policy.

The APR is:

=12 * RATE(A38/B8, C2+C3, -(B6 - F15))

That assumes that C3 contains additional periodic "finance charges", which
might include PMI premium payments and "transaction fees" (e.g. for biweekly
payments).


Regarding weekly, biweekly and semi-monthly payments....

If the payments are made to a loan service, not directly to a bank, it is
not uncommon for the bank loan to be structured with monthly payments, and
the loan service holds the borrower's payments in the interim.

In that case, the payment (C2) is:

=PMT(A38/12, B39*12, -B6) * 12 / B8

which should be rounded or truncated according to the loan service's policy.

Note: I would round-up the PMT() result to ensure that it covers the bank's
rounding policy, which we cannot know. The software you are emulating might
or might not do that.


----- original message -----

Thanks for your quick reply. You're correct in that it is for the US. I
am
having to do this work for someone else and they are telling me what their
other software is showing as APR and payment amounts so, unfortunately,
its
not up to me to say that what I already have is correct.

I will read through your posts and see if I can get my numbers to come out
"right".

I'll post back if I have questions!

PS....

To calculate the APR, I am using the following:
=RATE(B7,PMT(B11/12,B7,B6),B6-F15)*12

When computing the APR, you should use the actual payment, 1086.22, which
I
put into C2. So your formula should be:

=12*RATE(B7, -C2, B6-F15)

Recall that in my previous posting, I determined that F15 should be about
$253.34, not zero.


----- original message -----

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.

 

[sg]

The last post you added helped tremenduously. I have what I think is close
enough now to be ok with all of this. I'm waiting to hear back from the
powers that be to know for sure.

Thank you for your help. You have been very thorough and helpful with your
answers.

I am having to do this work for someone else and they are
telling me what their other software is showing as APR and
payment amounts

Matching the results of other software can be very tricky. As you say, it
is not a question of the right or wrong way to do things, but simply how the
original software interpreted things.

You might discover other areas that require interpretation. I will
summarize and build upon what I wrote before.

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)
[....]
A38 - 0.0735439979799297 [[true interest rate -- J]]
[....]
B39 = 20 (number of years)

I presume that B8 can be any of the following payment frequencies: 1, 2, 4,
12, 24, 26 or 52.

So I presume that B7 is:

=B39 * B8

Expanding on what I wrote previously, A38 should be:

=IF(OR(B15={"30/360","365/365","actual/365","actual/actual"}), B11,
IF(OR(B15={"365/360","actual/360"}), B11/360 * 365,
IF(OR(B15={"30/365", "30/actual"}), B11/365 * 30*12, NA())))

Note: Some software might use 366 for "actual" instead of 365. Other
variations are possible. Remember: this adjustment is outside the scope of
US regulations. In any case, you notice minor discrepancies when using
"actual" interest methods.

Ostensibly (but see comments below), the periodic payment (C2) is:

=PMT(A38/B8, B7, -B6)

which should be rounded or truncated according to the lender's policy.

The APR is:

=12 * RATE(A38/B8, C2+C3, -(B6 - F15))

That assumes that C3 contains additional periodic "finance charges", which
might include PMI premium payments and "transaction fees" (e.g. for biweekly
payments).


Regarding weekly, biweekly and semi-monthly payments....

If the payments are made to a loan service, not directly to a bank, it is
not uncommon for the bank loan to be structured with monthly payments, and
the loan service holds the borrower's payments in the interim.

In that case, the payment (C2) is:

=PMT(A38/12, B39*12, -B6) * 12 / B8

which should be rounded or truncated according to the loan service's policy.

Note: I would round-up the PMT() result to ensure that it covers the bank's
rounding policy, which we cannot know. The software you are emulating might
or might not do that.


----- original message -----

Thanks for your quick reply. You're correct in that it is for the US. I
am
having to do this work for someone else and they are telling me what their
other software is showing as APR and payment amounts so, unfortunately,
its
not up to me to say that what I already have is correct.

I will read through your posts and see if I can get my numbers to come out
"right".

I'll post back if I have questions!

PS....

To calculate the APR, I am using the following:
=RATE(B7,PMT(B11/12,B7,B6),B6-F15)*12

When computing the APR, you should use the actual payment, 1086.22, which
I
put into C2. So your formula should be:

=12*RATE(B7, -C2, B6-F15)

Recall that in my previous posting, I determined that F15 should be about
$253.34, not zero.


----- original message -----

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