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.
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:
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