Loan Amortization Schedule with Variable Interest Rate in Excel [Free Download]

Get FREE Advanced Excel Exercises with Solutions!

Loan amortization schedule is an essential tool to visualize one’s loan repayment. If a borrower takes a loan for a certain period at a certain or variable interest, he/she has to pay back the loan within that deadline through a regular payment. 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.

Generally, the interest rate for the loan is fixed. But, for any reason, the interest rate for the loan can be changed anytime during the loan term by the lender. In this case, the person will be charged more or less interest depending on the interest rates which will result in a change in the EMI to pay back the loan within the fixed term.

In this free downloadable template, loan amortization schedule Excel with a variable interest rate, 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 for respective periods.

This template is of great importance to financial planners, investors, bankers, students, and any type of borrowers and lenders who are looking forward to taking or giving loans.

Loan Amortization Schedule with Variable Interest Rate

Click here to enlarge the image

Download Excel Template

Download Excel Template

For: Excel 2007 or later
License: Private Use


What Is Loan Amortization Schedule?

A loan amortization schedule is a visualization tool for the loan repayment process. This schedule takes some necessary inputs like loan term, annual percentage rate, loan balance, etc., and shows output values of due payment, principal paid, interest paid, and remaining balance after each payment. So, a borrower will be able to visualize his every payment by visualizing which amount he/she is paying each period how the principal and interest are getting paid at each period, and how the loan balance decreases after each payment.

What Is Variable Interest Rate?

A variable interest rate means that the interest rate of the loan is not fixed over the loan term. Due to many reasons, the interest rate may vary for the borrower within his/her loan term. In this case, the individual interest rates are to be taken into consideration for respective payment periods. So, the EMI would change according to this variable interest rate to pay off the loan within the fixed deadline.


Excel Loan Amortization Schedule with Variable Interest Rate Template

This loan amortization template would take inputs as loan balance, loan term, variable interest rates along with applicable periods, extra payments, etc., and would give an amortization schedule along with a summary table showing the outputs of the total amount paid, total interest paid, estimated interest savings and time saved. A chart will also be generated to visualize the loan balance, principal paid, and interest paid trend.

How to Use This Template

Follow the instructions below to use this template.

Instructions:

  • Open the template and insert your input values in the blue shaded area according to the Loan Details column.
Insert Required Inputs

Click here to enlarge the image

  • After inserting the initial loan parameters, insert the variable interest rates in the 2nd table’s blue shaded area along with the From and To column values.
Insert Variable Interest Rates with Periods

Click here to enlarge the image

  • You will get your automated amortization table based on given inputs and a summary chart showcasing your most important results such as the total amount to be paid, total interest to be paid, estimated interest savings, etc.
  • You will also get a summary chart displaying the trend for 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 Excel with Variable Interest Rate Tips

  • Insert all the loan values properly according to the loan parameters and select the dropdowns carefully.
  • Your inserted annual percentage rate would be automatically the first interest rate.
  • If you have other interest rates applicable at other periods, insert the variable interest rates one by one, and don’t forget to insert the From and to periods of the respective interest rates.
  • If you want to make some irregular extra payments, you have to enter those manually at those specific payment periods.
  • If your estimated interest savings value is negative, it means you could not save any interest, rather extra interest has been accrued.

Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

8 Comments
    • 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!
      Regards!

  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.
    DETAILS;
    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.
    THE PROBLEM
    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.
    Thanks.

  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?

    • Hello RICHARD MUTALE BWALYA,

      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.

      Regards,
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo