Actual 360 Amortization

Justin Perez
Justin Perez used Ask the Experts™
on
I had this question after viewing Excel amortization formula for actual 365, actual 360.
I was given an amortization schedule by the lender.

Principal= $5,150,000, Interest rate= 4.25%, Loan term= 10 years, Amortization term=25 years, and payment= $27,899.51.

I was told by the lender that amortization method being used is Actual 360. When I try to put the following loan terms into excel I get a different loan payment.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
Here:  https://compass1.org/java/MortgageLoan2.html?gclid=EAIaIQobChMI1sP2vuKs4AIVlLrACh2PjQDREAAYASAAEgJyW_D_BwE

I got $27,899.51 as you have above.

What did you get when you tried?  Loan period is not relevant, only the Amortization term (25 years)

There are many Excel templates available. Search for "actual 360 amortization calculator excel template"
Top Expert 2016

Commented:
ACTUAL/360 (AKA 365/360)
This method has seen its day in court because borrowers challenged that this method is deceptive and hides from borrowers the true cost of borrowing. However, the lenders prevailed due to the fact that it was fully disclosed how they are calculating interest. Thus, Actual/360 is an interest calculating method that is here to stay.

When using the Actual/360 method, the annual interest rate is divided by 360 to get the daily interest rate and then multiplied by the days in the month. This creates a larger dollar amount in interest payments because dividing the annual rate by 360 creates a larger daily rate then dividing it by 365.
https://www.adventuresincre.com/lenders-calcs/

Author

Commented:
This is amortization scheduled provided to me by the lender. As you can see interest fluctuates. I need an excel formula that can help me account for these fluctuations.
AMORTIZATION-SCHEDULES---1_Redacted.pdf
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
Interest moves steadily. Many excel templates

Author

Commented:
Please review the amortization schedule provided. 03/01/17 interest amount drop by $2,000 and the following month increases by $1,800. I know this is because of the actual 360 amortization but I need a formula in excel so I can track it on a yearly basis.
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
I looked at it and the chart does not disclose or track events such as late, missed, or additional payments. Straight amortization interest moves in a very level fashion changing only slightly each month.
Anthony MellorChartered Accountant

Commented:
I use the HP12C for this sort of thing. I prefer the physical calculator, but there are apps that emulate it.

How it works is in the manual here http://www.hp.com/ctg/Manual/c00363319.pdf

search on 360 - page 33 simple int, amortisation page 56 - page 187 for the formulas including 360 basis.


p.s. isn't the question in error where it says Amortisation term = 25 years? It's only a ten year loan, does it mean amount at period 25 of 120?

.. this is an uneven cashflows with a nearly 4m end/balloon.
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
I have and use an HP12C here.

Justin - please come back and comment about the most recent posts here.
Anthony MellorChartered Accountant

Commented:
maybe the 25 years is the amortisation schedule for whatever the asset is..


originally the HP38C from around 1979 ? these both being proper calculators :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial