## Variable Rate Amortization – Day/Year Count & Last Payment Options

Remember to enter dates and rates in yellow cells only. The worksheet is protected, except for the yellow input cells, so you are not tempted to type over a formula. There is no password, so you can unprotect the sheet.

Back in November of 2014 I posted “Amortization Schedule With Variable Rates“, and an Excel spreadsheet that could handle an amortization schedule with rates that varied throughout the term of the loan. Janice commented that it would be nice to have the variable rate amortization, but with an actual/360 (day/year) count rather than the 30/360 used in the original spreadsheet.

The new spreadsheet attempts to answer two basic problems when working with a variable amortization schedule. The first problem is how to handle the last payment. With interest rates changing throughout the loan term, the original last loan payment is unlikely to pay off the loan.

I gave the user three options in this new spreadsheet:

Option #1. If you use option number one (yellow dropdown box) the loan will pay off on the last payment date, regardless of the loan balance. If interest rates average higher than the original rate the payment was calculated on, you are liable to have a very large last payment. Of course the loan can pay off before the stated last payment if rates average lower than the original rate.

Option #2. This option recalculates the payment after each rate change. This will help reduce any large last payment, by spreading the cost over future months.

Option #3. Negative Amortization. If the first two options are not acceptable, the third option will require additional payments until the loan is paid off, if rate move up. If rates average lower, the loan will pay off sooner.

Sample Loan

First, we will look at the rate changes. The settlement date is the date the loan starts to accrue interest. We are assuming a day count of actual/360 days in this example, so from the settlement until the first payment is 27 days. If we were using 30/360 the first payment would accrue interest for 26 days. The assumption is that the first payment might be (doesn’t have to be) a partial monthly interest period.

In this example, the rate will remain at 3.00% for 12 months through 12/1/2016. Starting on 1/1/2017 the rate will change to 4.00% until 12/1/2024. This can be confusing if you don’t realize that the date column is the date the rate changes.  Therefore, 1/1/2025 is not the last or 108th payment, 12/1/2024 is. If you use the negative amortization option for the last payment, the rate will remain at the last rate given, until the loan is paid off.

The day/year count assumptions are also in a yellow cell drop down box. The options are:

30/360

Actual/360

Actual/365

Actual/Actual

With actual/actual the base year will remain 366 during a leap year for each month in that year, even if the loan did not start until after February of that year.

The day/year count are handled with two helper columns. Data from these two columns are used to calculate the monthly interest.

I put this together in a day. Since I am retired, I have not tested the results in all possible combinations or tested the results against other amortization schedules. This is a template to be changed to suit your needs. As always, use at your own risk. Enjoy

Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

1. Hi… I love your spreadsheet!! I have Q or challenge for you that I need help with. I owner financed a property that sold in 2010 with a 7 year balloon. if the property was not refinanced, the Maturity Interest Rate on the loan increased from 8.90% to 18.0%.
Since the maturity Interest Rate kicked in, they continued making the original payment for 2+ years before they started making the new larger payment. That increased their loan balance significantly due to the negative amortization. even thought they eventually started making the larger payment, the still are in negative amortization, due to the higher principal balance and the higher interest being paid.
They now are trying to refinance their loan (YEAH!!) but I’m having issues trying to calculate their payoff, as my standard amortization spreadsheet is not designed to handle these variables!!
CAN YOU HELP ME?? Thank you in advance for your time and consideration on this matter!
Bruce Goldfarb
713-398-8288
Bruce@FCAM.biz

1. Hi Bruce,

I am going to send you a spreadsheet. Let me know if it works for you.
Don

2. […] Variable Rate Amortization – Day/Year Count & Last Payment Options March 4, 2016 […]