Loan Amortization Schedule with a Variable Interest Rate in Excel – Free Download

A Loan amortization schedule is an essential tool to visualize a loan repayment. Through this loan amortization schedule with a variable interest rate, the borrower will be able to trace his/her loan repayment by calculating the principal paid, interest paid, and remaining loan balance after each payment.

In this free downloadable template, you will be able to insert 10 variable interest rates during your loan repayment and get an automated amortization schedule depending on the interest rates.

Loan Amortization Schedule with Variable Interest Rate

Click here to enlarge the image

Download the Excel Template

Download Excel Template

For: Excel 2007 or later
License: Private Use


How to Use This Template


  • Open the template and enter your values in the blue shaded area in the Loan Details column.
Insert Required Inputs

Click here to enlarge the image

  • In the blue shaded area of the 2nd table, enter the variable interest rates and the From and To values.
Insert Variable Interest Rates with Periods

Click here to enlarge the image

  • You will see your automated amortization table and a summary chart showcasing important results, such as the total amount to be paid, total interest to be paid, estimated interest savings, etc.
  • You will also see a summary chart displaying the trend for the principal paid, interest paid, and loan balance.
Loan Amortization Schedule Excel with Variable Interest Rate

Click here to enlarge the image

Read More: Excel Monthly Amortization Schedule

Loan Amortization Schedule with Variable Interest Rate -Tips

  • If you have other interest rates applicable to other periods, insert the variable interest rates one by one.
  • If you want to make extra payments, you have to enter those manually.
  • If your estimated interest savings value is negative, you will not save any interest.

Related Articles

<< Go Back to Amortization Schedule | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

    • Hello Mr. Fazal,
      You can download the attached Excel file and use that as a template.
      All you need to do is input the number of years, periods per year, and balance. All the columns have their corresponding formula applied. As you provide the required information, Excel will automatically calculate the Loan Amortization Schedule for you.
      Last but not the least, you have to update the variable annual interest rate (AIR) manually. If you have any lump sum amount in your consideration don’t forget to update that too!

  1. Can be possible client wise auto update loan amotozation table?
    Also if possible interest rate change so auto update automatic in excel
    Extra Payments means (Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments) Additional Payment already showing in your video ,Variable or Fixed Rate ,Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ? how to create in excel & Suppose provide only interest

  2. Reply
    Rosemarie Linfoot Feb 14, 2023 at 10:21 AM

    Thank you very much for this excellent tutorial. I have been able to follow it without any problems at all. However, I need a variation to work with a different set of rules. If there is an example that covers this, please advise point me to the link.

    If not, perhaps you may be able to help me to solve the problem. This is a loan that ties the interest rate AIR, to the our country’s Reserve Bank Official Cash Rate (OCR). The OCR is increased in order to stop an over inflated economy.
    1. These are long term, fixed period loans. (Penalties are charged if lump sum payments are made to pay the loan off sooner than due date, except if the property is sold).
    2. Fixed installment for term of the loan.
    3. If the OCR goes up, the banks will follow.
    4. If the OCR goes down, the banks will follow, but never lower than the original AIR.
    5. The timing of the change does not match the timing of the due date of the installment, so split daily interest calculations have to be made to allow for the increase. Not a problem, just insert a row to enter the split for the new rate calculated for each set of days, both calculations based on the balance as at the date of the last principal payment.
    6. At the point when any increase in the OCR will cause the total amount owed to go up, that part of the excess interest increase has to be calculated, and is then added onto the fixed installment payment. This is done so the loan will repaid on the final period date, with the final installment. New loans are affected the most as there is no cushion from any earlier OCR decreases that may exist on a loan that has been in place for 4 or 5 years.

    I can do the calculation manually after the event, but I need to be able to have the changes calculated automatically as projections for the future based on political information ahead of time for budgeting purposes.

    Any help gratefully received.

  3. Reply
    Richard Mutale Bwalya Mar 8, 2023 at 9:17 AM

    Is it a legal practice to transform say, what started off as a fixed rate loan into a variable rate loan, somewhere down the life of a loan because of a sudden happening recession?


      Thank you for your question. Changing a fixed-rate loan to a variable rate depends on the terms and conditions of the loan agreement, in addition to the applicable laws and regulations in the jurisdiction where the loan was made.

      So, we believe it is best that you contact a financial advisor who can suggest the proper course of action based on your agreement.


Leave a reply

Advanced Excel Exercises with Solutions PDF