A Loan amortization schedule with a variable interest rate allows the borrower 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.
Click here to enlarge the image
Download the Excel Template
Download Excel TemplateFor: Excel 2007 or later
License: Private Use
How to Use This Template
Instructions:
- Open the template and enter your values in the blue shaded area in the Loan Details column.
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.
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.
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
- Loan Amortization Schedule in Excel with Moratorium Period
- EIDL Loan Amortization Schedule Excel
- Excel Actual/360 Amortization Calculator Template
- Excel 30 Year Amortization Schedule Template
- Excel Weekly Amortization Schedule
- Excel Quarterly Amortization Schedule
<< Go Back to Amortization Schedule | Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
May I have this template
https://www.exceldemy.com/loan-amortization-schedule-excel-with-variable-interest-rate/
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!
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
Mr. Bhavnesh
Please read the following article and the 1st comment. Hopefully, you will get your answer.
Creation of a Mortgage Calculator with Taxes and Insurance in Excel
For further queries, please email us at [email protected].
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.
Hello ROSEMARIE LINFOOT,
We have attached an Excel workbook with the necessary instructions to this reply. You can download the file using the link below.
Loan-Amortization-Schedule-with-Variable-Interest-Rate-And-OCR.xlsx
If you have further queries please attach a sample workbook and contact us at [email protected].
Regards,
ExcelDemy
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
Hello
I’ve downloaded this template, it’s great. I’m wondering how I can add in more interest rates. 10 rates are provided for, but I guess I need about 20 (ECB had 10 increases and now starting to decrease thankfully). I see on your tips it says to add them in manually, I assume I’m missing something totally obvious, but I cannot see how to do it.
Any feedback, greatly appreciated.
Thank you!
Hello Aisling,
Thanks for your appreciation. To add more interests you’ll need to extend the interest rate list manually and adjust the formulas to include the new entries.
Here’s how to do it:
1. Add the additional 10 interest rates (or however many you need) in the interest rate table.
2. Modify any formulas that reference the original range to accommodate the extended list.
If you’re facing difficulties, ensure all formulas reflect the new range.
Regards
ExcelDemy