Actual 360 Amortization

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.
Justin PerezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnBusiness Consultant (Owner)Commented:

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"
David Johnson, CD, MVPRetiredCommented:
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.
Justin PerezAuthor 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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

JohnBusiness Consultant (Owner)Commented:
Interest moves steadily. Many excel templates
Justin PerezAuthor 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)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 AccountantCommented:
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

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)Commented:
I have and use an HP12C here.

Justin - please come back and comment about the most recent posts here.
Anthony MellorChartered AccountantCommented:
maybe the 25 years is the amortisation schedule for whatever the asset is..

originally the HP38C from around 1979 ? these both being proper calculators :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.