Amortization Schedule With Variable Rates

Note: I have updated this post with more options.

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

Have you ever wanted an amortization schedule where you can set the rate for one term and then change the rate for another term, and change the rate and term a total of six times? If you have, try the workbook “AmortizationChangeRate”. As usual the yellow cells are input cells. The example below shows a $135,000 loan at 2% for the first 6 months, 3% for the next 6 months and 4% for the last six months.  The payment changes each time the rate changes. There is also a column for extra payments.

At the bottom of the input rates is the average weighted rate. In this case 3.00%. Making a loan at 3% for the full 18 months is not the same as this variable rate structure. The present value of the payments for an 18 month, 3% loan discounted at 3% would be $135,000, just as you would expect. This loan structure discounted at 3% is $134,550.90. If we change the structure from 6 month terms to 90 day terms and left the rates the same, the present value difference discounted at 3% is $128,339.81 vs. $135,000.

This might come in handy when considering restructuring a loan to see the changes in present value.

Download workbook “AmortizationChangeRate” from:

http://www.pistulka.com/Excel_Shared/

Downloads Written in Excel 2013

 

 

Don Pistulka
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.

8 Comments

  1. this spreadsheet is awesome and exactly what i was looking for! thank you for providing. would you be so kind to let me know how i could adjust for a 10 year length please?

  2. this spreadsheet is awesome and exactly what i was looking for! how can i make this for a 10 year loan term please?

    1. Molli,
      Are you looking to change the rate more than 6 times during the life of the loan or less than six times? For 6 times or less just make sure that cell G9 (Total Months) equals 120. See attachment. Let me know if that works for you.
      Don

  3. Do the methodology and excell sheet can be used for FRN instrument ?
    Thanks..

    1. Niko,
      The basic methodology would work for calculating the internal rate of return, however the payment schedule would need to be changed to quarterly from monthly. The IRR function would also need to be changed to quarterly. The principal paydown would most likely need to be set to zero. In addition, the number of rate changes would probably be needed to be increased.The note’s interest would also need to be based on a 30/360 day count.
      You would be better off just using the variable rate concept, and building your own spreadsheet from scratch.

      Don

Leave a Reply

Your email address will not be published. Required fields are marked *