Amortization 360 vs 365 Days

johndrew

Board Regular
Joined
Apr 21, 2007
Messages
100
I have a loan for $755,812 at 6.0% for 5 years. I put this in as pmt and got a montly payment of $14,611.98. The bank has a payment of $14,644.10.

I asked the bank why the difference and they said it was the difference between 360 days vs 365.

How do I calculate it to get their montly payment and make an amortization schedule with interest and principal?

Thanks
 
Thanks. I get this as well, but the bank says the payment is $58,360.23. I was hoping someone can show me how they are arriving at that number.

Ask your teacher! ;) ;)

If you are taking out a loan of $11M, you should have enough clout to ask the bank to detail the calculations. Alternatively, you should have an attorney or CPA on retainer who can explain the calculation or encourage the bank to.

It might help to know what country the loan is in. For example, Canada, the EU and the USA each has a very different way of specifying interest rates. (But I've tried them all, though, to no avail.)

I suspect there is some misunderstanding. For example, perhaps the actual interest rate is about 4.049025%, and 3.99% is some sort of theoretical rate. Or $58,360.23 is composed of $58,001.33 to pay off the loan plus some other monthly fees (e.g. PMI).

But I will continue to see if there is a "simpler" explanation.

PS.... In the future, it is better to start a new discussion instead of piggybacking an old one, especially when you have a very different question.
 
Last edited:
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
PS (too late to edit)....
It might help to know what country the loan is in. For example, Canada, the EU and the USA each has a very different way of specifying interest rates. (But I've tried them all, though, to no avail.)

It might also help to know the date when you receive the loan funds, and the date of the first payment.

Usually, the first payment is one month after receiving the loan funds. But an "odd" period might explain the disparity between the stated interest rate and the fixed monthly payments.

Also, is this a fixed-rate or variable-rate loan?

In theory, that should not affect the calculation of the payment during initial term. But it might the source of some misunderstandings.
 
Upvote 0
PS (too late to edit)....


It might also help to know the date when you receive the loan funds, and the date of the first payment.

Usually, the first payment is one month after receiving the loan funds. But an "odd" period might explain the disparity between the stated interest rate and the fixed monthly payments.

Also, is this a fixed-rate or variable-rate loan?

In theory, that should not affect the calculation of the payment during initial term. But it might the source of some misunderstandings.

This is in the US. Fixed rate loan and the first payment is one month after receiving the funds. Actual / 360, interest paid for the actual number of days in each month.
 
Upvote 0
The bank says the payment is $58,360.23. I was hoping someone can show me how they are arriving at that number.

I've had experience replicating these calculations across several financial institutions. Typically, the larger the institution, the more sophisticated the calculation algorithm. It's always possible to replicate calculations to the cent, but only when you know exactly how the calculation works.

On the face of it, the quoted repayment is too high, i.e. it's too big to be explained by slight differences in calculation methodology. As others have pointed out, perhaps the interest rate you're quoting isn't the "real" interest rate.

There are other possibilities, e.g. there may be an application fee capitalised into the loan amount, or monthly administration fees included in the repayments.

In summary, there are too many unknowns. If you clarify with your loan provider exactly how the calculation has been made, it will be easier to verify accurately.
 
Upvote 0
This is in the US. Fixed rate loan and the first payment is one month after receiving the funds. Actual / 360, interest paid for the actual number of days in each month.

I can confirm that those facts are probably correct.

Refer to "japes amort sched.xls", downloadable from https://app.box.com/s/tr1i738yxcb5d0sf5ssh. Ignore any preview errors and just download.

When I set up an amortization schedule (below) with the monthly payment of 58,360.23 and monthly interest based on actual/360, Solver determines that the actual annual interest rate is between 3.99045988210% and 3.99045988522%.

Note that it rounds to 3.99%. Such differences between documented and actual interest rates are permitted by US "Truth in Lending" regulations (Reg Z).

Also note that we cannot calculate the actual interest rate using Excel RATE because the function effectively calculates a monthly rate based on 30/360.

And we cannot calculate the actual payment using Excel PMT, in part because we did not know the exact interest rate used by the bank, and in part because the function calculates payment to the full machine precision, not the real-world rounding to 2 decimal places.

The following demonstrates how to set up the amortization schedule.


A
B
C
D
E
F
G
H
I
1
Loan
11,000,000.00
Pmt#
PmtIntBal

2
Term300months
6/1/2014

11,000,000.00

3
Annl_rate3.99045988365865%
17/1/201458,360.2336,579.2210,978,218.99
4
Actl pmt58,360.23
28/1/201458,360.2337,723.6810,957,582.43
5
Min rate3.99045988210%
39/1/201458,360.2337,652.7710,936,874.97
6
Max rate3.99045988522%
410/1/201458,360.2336,369.3010,914,884.04










300



2984/1/203958,360.23597.55116,134.42
301



2995/1/203958,360.23386.1958,160.38
302



3006/1/203958,360.23199.850.00
303







2.59E-07Actl_bal

<tbody>
</tbody>

Formulas:

E3: =EDATE($E$2,D3)
F3: =$B$4
G3: =H2*(E3-E2)*$B$3/360
H3: =H2+G3-F3

Copy E3:H3 down through row 302.

-----

Note that for comparison purposes, the Reg Z annual interest rate (based on 30/360) is 4.04902518141567% = 12*RATE(300,58360.23,-11000000).

The difference between that and the bank's actual annual interest rate (based on actual/360) is about 0.059%, which is within the 0.125% difference permitted by Reg Z.
 
Last edited:
Upvote 0
PS....
When I set up an amortization schedule (below) with the monthly payment of 58,360.23 and monthly interest based on actual/360, Solver determines that the actual annual interest rate is between 3.99045988210% and 3.99045988522%.

If this is a real-world example, it is more likely the annual interest rate is 3.990459% or 3.990460%. That makes the last payment slightly less or more, which is not unusual.

Note that it rounds to 3.99%. Such differences between documented and actual interest rates are permitted by US "Truth in Lending" regulations (Reg Z).
[....]
Note that for comparison purposes, the Reg Z annual interest rate (based on 30/360) is 4.04902518141567% = 12*RATE(300,58360.23,-11000000).

The difference between that and the bank's actual annual interest rate (based on actual/360) is about 0.059%, which is within the 0.125% difference permitted by Reg Z.

Then again, I'm probably taking this just a little too seriously. ;)
 
Upvote 0
You may need to ask them what interest assumptions they are using. They may be using something like a 360/365 assumption which you can't do with the PMT function.
 
Upvote 0
If this is actual/360 (I didn't see your post prior to my previous post) then it looks correct. I wrote an amortization schedule to do actual/360 and got a payment of 58,357.39. That payment will change depending upon the loan date.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top