A loan amortization schedule can be prepared using one of the two ways. The first one is with a fixed interest rate and the second one is a variable interest rate. In this article, you will learn to prepare a loan amortization schedule with a variable interest rate in Excel.

## Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

## What Is a Loan Amortization Schedule?

A **Loan Amortization Schedule** is a table of period payments against the loan. In the table, the total number of periods to pay back the loan, the remaining balance to pay, how much money you are paying for the interest, and the original amount all are stated.

## How to Prepare a Loan Amortization Schedule with Variable Interest Rate in Excel

### Steps to Prepare a Loan Amortization Schedule with Variable Interest Rate in Excel

I will show you to prepare a loan amortization schedule based on the following data.

**Total Loan Balance = $50,000.**

**The number of Years = 2.**

**Loan Payment Frequency per Year = 12.**

**Total Number of Payments Against the Loan = (The number of Years x Loan Payment Frequency per Year) = 2 x 12 = 24.**

Thus, in the **Period **column, we have taken a serial number from 1 to 24.

#### Step-1: Calculate the Payment Amount, PMT

In the **PMT **column, we will **calculate the amount of loan payment per month**. For that, we will use the **PMT function**.

Now follow the steps below to calculate the **PMT**.

❶ To begin with, select cell **C10**.

❷ Now insert the following formula:

`=-PMT(G10/$D$5,COUNT(B10:$B$33),F9)`

In this formula:

**G10**refers to the annual interest rate in the**AIR****$D$5**refers to the**Periods per Year.****COUNT(B10:$B$33)**returns the remains number of payments against the loan.**F9**refers to the total loan balance remaining.**-PMT(G10/$D$5,COUNT(B10:$B$33),F9)**calculates the payment amount. Originally the function returns a negative value. Thus the minus sign**(-)**before the function will return a positive value.

❸ Finally hit the **ENTER **button to execute the formula.

#### Step-2: Calculate the Amount of Interest Paid

The payment amount is used to pay both the interests and the principal. But to calculate the amount of interest, follow the steps below:

❶ Select cell **D10 **first.

❷ Then insert the following formula in cell **D10**:

`=F9*G10/$D$5`

Here,

**F9**is the remains balance to pay back.**G10**is the annual interest rate.**$D$5**refers to the**Periods per Year.**

❸ Finally hit the **ENTER **button to get the value.

#### Step-3: Calculate the Amount of Principal Paid

To calculate the amount of principal paid,

❶ Select cell **E10 **first.

❷ Then insert the following formula:

`=C10-D10`

Here,

**C10**refers to the**PMT**.**D10**refers to the amount of interest paid.

❸ After that hit the **ENTER **button.

**Read More: How to Use Formula for Mortgage Principal and Interest in Excel**

#### Step-4: Figure out the Remaining Original Balance Considering the Lump Sum End Payment

Now it’s time to figure out the remaining original balance.

Sometimes the loan borrowers want to pay back all the money or the bigger portion of the money. In that case, the lump sum end payment should also be considered.

Thus, you can calculate the remaining original balance considering the lump sum end payment using the following steps:

❶ First, select the cell **F10**.

❷ Then insert the following formula:

`=F9-E10-H10`

Here,

**F9**refers to the previous value of remains balance to pay back.**E10**is the principal paid back amount.**H10**is the lump sum end payment amount.

❸ After that hit the **ENTER **button.

## Things to Remember

- To make a loan amortization schedule just change the values such as the
**Original Balance, Number of Years, Annual Interest Rate (AIR),**and**Lump Sum.** - Adjust the
**Period**according to the**Number of Years.**

## Conclusion

To sum up, we have discussed the procedure to prepare a loan amortization schedule with a variable interest rate in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website **Exceldemy** to explore more.

## Further Readings

**Interest Only Mortgage Calculator with Excel Formula (A Detailed Analysis)****Excel Interest Only Amortization Schedule with Balloon Payment Calculator****How to Use Formula for Car Loan Amortization in Excel (with Quick Steps)****Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel****How to Use Formula for 30 Year Fixed Mortgage in Excel (3 Methods)****Creation of a Mortgage Calculator with Taxes and Insurance in Excel****Mortgage Calculations with Excel Formula (5 Examples)**

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 1stg comment. Hopefully, you will get your answer.

Creation of a Mortgage Calculator with Taxes and Insurance in ExcelFor 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.xlsxIf 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