Hi,
I have the below information/beginning of amortization schedule. Even though Term is only 10 years, I took the schedule out to the full 25 year amortization just to confirm my payment calculation was correct.
The PMT formula (below) was giving me a payment of 9,279.87 and an ending balance of 2,792.71 after 300 months. I had to add $4.29705 to the monthly payment in order to get the ending balance to zero after 300 months.
The interest is calculated as actual/360.
My PMT formula is PMT(((5.5%/12)*(365/360)),300 months,-1,500,000,0)+4.29705
I have a feeling the issue is with leap years over the full 25 year term, but I just want to confirm with people here. Is there an excel formula tweak that I need to make?
Obviously the discrepancy is small, especially when looking at the balloon payment in year 10.
Most schedules have beginning balance plus current month interest = final pmt trigger (balloon payment) when the loan ends/is paid off.
I tried other mortgage calculators online and was getting the same results. Is this just a known discrepancy? Any tricks to get the schedule to fully amortize without resulting in crude PMT adjustments?
Someone correct me if I'm wrong, but I think there are two points to be made here:
1) - Your PMT formula is wrong. Actual/360 loans should have the same amortizing monthly payment as a 30/360 loan - Actual / 360 just adds additional interest / reduced principal paydown in 31-day months. It looks like you're calculating days interest correctly based on the excel snippet...
2) - I want to say that Actual / 360 loans conceptually never result in full principal paydown. It's a product built for ballooning loans to squeeze out interest $.
Don't have a spreadsheet open in front of me to check but try replacing (5.5%/12) with ((1+5.5%)^(1/12)-1)
Thanks for the suggestion. It makes the problem worse by lower the PMT amount--the daily interest amount being charged is less with ^(1/12) than 5.5%/12.
You just need to take the (365/360) out of your formula.
PMT((5.5%/12),300,-1,500,000,0)
You account for Actual/360 in your ammort table when determining the periodic interest & based on the actual days in each month.
Hi,
Anything that reduces the interest (like removing the 365/360) just makes the payment lower and my final ending balance even larger after 300 months. Here is what is calculated with the 365/360 taken out.
Your methodology of trying to solve for a $0 ending balance as a means to proof your formula is throwing you off. Your payment is your payment - it's neither too high nor too low. The math in the formula I gave you is correct.
Actual/360 (a/k/a 365/360) increases the amount of interest paid through the term of the loan. It does not impact the actual payment amount - it just allocates more of the payment to interest vs principal. As a result of paying less principal, your ending balance is naturally going to be larger than the same loan that accrues interest using 30/360.
Use the formula I gave you and change the "Days" column to all be 30. That should give you a zero balance...
Sorry this isn’t helpful at all but quick question for ya - I’ve seen the 365/360 convention on a bunch of models but never full understood it’s purpose. Any chance you could explain it quickly? Does it have something to do with the 30 year amort (360 months) or am I off base here?
365/360 is an approach to calculating annual interest due. It does not have to do with the amortization period.
The standard payment calc assumes equal interest paid in each period - for deals with a quoted annual rate of interest & monthly payments, we adjust our rate in excel by doing (rate/12) (a/k/a rate * 1/12) to solve for our monthly rate of interest. A traditional fully amortizing mortgage can then be thought of as having a 30/360 interest structure, which arithmetically simplifies to 1/12 but also conceptualizes the idea that you're paying 30 days of periodic interest regardless of how many days are in each month.
365/360 simply means that you will be paying 31-days worth of interest ((rate/360)*31) for 31-day months & 30-days worth of interest ((rate/360)*30) for 30-day months. This is still based on the same annual rate of interest, so this interest calc convention has no real impact on the actual payment amount but instead just increases the amount of interest paid over the life of the loan.
I believe Actual/360 was conceived by the CMBS industry to juice returns, and is primarily applicable on ballooning mortgages because it does fuck with your overall "full" amortization (as OP is seeing in trying to build out his am table).
I hope this explanation is somewhat helpful lol.
Ran into a similar issue once. The PMT formula assumes equal periods so yearfrac doesn't work. Every month needs to be the exact same length. For monthly amortization, use 1/12 instead of yearfrac when calculating interest.
Thank you. This is the answer. Similar to how IRR assumes equal length periods. Doing ((Interest Rate/12)*365/360) in the PMT formula will give you a more accurate payment--or at least a payment that will get the loan to the lowest balance with a full amortization, but can never be 100% accurate.
I think one reason is that I was creating the amortization schedule from scratch using just a term sheet. It listed a daily interest amount so I knew it was Actual/360, but no monthly payment amount or sample amortization schedule was provided. The variances are trivial given the time frame and sum of money, but the fact that it wasn't tying was annoying me.
Honestly, why the fuck did you come to the real estate forum to just not listen to the feedback given to you by someone that works in the field?
Using (rate/12)*365/360 DOES NOT give you a more accurate payment. If you're looking at a real estate loan w/ a known rate of amortization, the fixed monthly payment is PMT(Rate/12,Amort Months,-PV). Full stop.
365/360 interest calculations simply means you pay interest based on the actual number of days in each period, but your full payment amount (principal + interest) would be the exact same as a loan that accrued interest in 30-day equal increments.
In the world of finance, getting an answer right or wrong isn't trivial. You either understand the concept and apply the formula correctly or you don't.
I'm done now.
Dolores qui hic ducimus ut culpa maiores nobis. Tenetur rerum tempore velit nulla perspiciatis facere eaque. Amet laborum aut corrupti alias et libero.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...