# Loan Amortization Schedule with Variable Interest Rate in Excel

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.

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. #### Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

1. Reply • Reply  