When you owe a bank or lending institution, they usually give you a statement to see how much you’ve paid. However, that’s about it. They can calculate how much you have left to pay and for how long, but you’ll have to ask customer service to ask.
Some institutions also offer online calculators, but these usually only work with fixed interest rates. If your loan uses variable interest rates, you’ll have to figure that out yourself. Luckily, you can create an Excel amortization schedule with changing interest rates. Here’s how you can build it.
Build the variable rate loan amortization table
Before building the table, you must have the following information:
- The total amount of the loan
- The duration of the loan (number of years to pay)
- The number of payments per quarter (number of payment periods in a year)
You should also know the following formula:
-PMT(RATE,NPER,PV)
ASSESS refers to the annual interest rate applicable to this payment, NPER refers to the number of payments remaining, and PV refers to the loan balance remaining before payment for said period.
Once you have this information, enter it into your Excel file. For this example, we’ll use a 20-year, $5,000,000 mortgage paid monthly.
After adding your initial data, you can start creating the amortization table.
Creating the columns of your amortization schedule
You need to create the following columns for your table:
- Payment deadline: indicates the payment term.
- Payment amount: the total amount that must be paid for the period.
- Interest paid: amount of payment that goes to interest.
- Capital paid: payment amount to reduce the initial balance.
- Loan Balance: total amount of the loan remaining to be repaid.
- Annual interest rate: the annual interest rate for this payment period.
- Lump sum payment: Records prepayments, if any.
Fill in fixed data
Once you’ve created your columns, it’s time to fill in the predetermined information. Under the Payment deadline column, write 0 for your initial data, 1 in the next line to display the first payment period, and 2 in the next row. After that, select the three cells.
Once you’ve selected them, hover your cursor over the green box in the lower right corner of the selection box until it turns into a thin black cross. From there, click on the box and then drag it down.
You should see a small box appear to the right of your cursor. This number indicates the last number Excel will fill in – release the mouse button when you reach the total number of payment periods for your loan (240 in our example).
Below Ready Balancelink the total loan amount of period 0 to the original amount Total loan amount. For our example, use the formula =C1. Leave the rest of the lines blank.
Below Annual interest rate, enter the interest rate for said payment period. If you have a 5/1 ARM (variable rate mortgage), the first five years of your loan have a fixed rate. Then, it will adjust annually, depending on the market, after this period.
For this example, we’ll use an interest rate of 3.57% for the first five years, and then we’ll use floating rates based on historical rates.
Adding formulas
Once you’ve placed your shadow interest rates, it’s time to determine your first payment amount. Enter the payment formula mentioned below in the Payment period 1 row under the Payment amount column.
=-PMT(RATE,NPER,PV)
Below ASSESS, choose the cell that lists the current annual interest rate (cell F6 in our example), then divide it by the number of payments per period. Add the $ sign before the column and row indicator (cell CA$3 in our example) to make sure it doesn’t change when you apply the formula to other cells later.
For NPERtype COUNT(. Then select the first Period cellhurry gap on your keyboard, then select the last period cell (the current cell). Again, don’t forget to add the $ sign before the column and row number in the formula that indicates the last cell (AU$245 in our example) to make sure it won’t change later.
To input PVselect the cell containing the total loan amount in Period 0 (cell E6 in our example). Also note that the EMP The formula is preceded by a negative sign, so you get a positive value in your array. If you don’t add it, you will get a negative value.
Under the Interest paid column, calculate the amount that goes to interest on your monthly payment. Use the following formula:
=Balance Remaining*(Annual Interest Rate/Payments Per Period)
For our example, this is what the formula would look like:
=E6*(F7/$C$3)
Where E6 is the remaining balance you owe, F7 is the current applicable annual interest rate, and C$3 is the number of payments you make per term.
Once you have determined the amount of Interest paidsubtract that from the Payment amount to get the Capital paid.
In our example, the formula is:
=B7-C7
You can get the Loan Balance Of the by subtracting the current Payment amount and Lump sum payment the loan balance of the last period (period 0).
Here’s what the formula looks like in our example:
=E6-B7-G7
Once you’ve added all of the plans, you should prepare all of your first month’s data.
Complete the rest of your amortization schedule
With all the initial formulas ready, you can now populate the rest of the table with a simple drag and drop. First, select the cells with a formula (Payment Amount, Interest Paid, Principal Paid, and Loan Balance under Period 1).
Next, hover your cursor over the green box in the lower right corner of the selection box until it turns into a thin black cross. From there, click on the box and then drag it down to the last payment period. Once you reach the last row, release your mouse button and Excel will fill in the rest of the cells for you.
Alternatively, you can Copy the cells with the formulas, then select all of the Payment Period rows below it. From there, select Dough or press Ctrl+V and Excel will automatically fill your table with the correct values.
To avoid accidentally changing formulas, select all cells with a formula (cell A6 to cell E246), then fill it with any color. You can also use conditional formatting to change the color of your cells once your loan balance is fully paid off.
Experiment with your data
Now that you have completed your initial table, you can freely modify the Total loan amount to see its effect on your total loan. You can also change the Annual interest rate by payment period, allowing you to enter the actual data once your bank has sent you the effective rate of your loan. And if you have a little extra, you can make advance payments under Lump sum payment.
Any changes you make are reflected in your formula, so you get accurate results regardless of what data you enter, as long as it’s valid. If you have a fixed rate loan but want to experiment with the term, term, or monthly payment of the loan, learn how to create a fixed rate amortization schedule in Excel.
Knowing can help you plan financially
With the variable rate amortization table, you can plan your loan and your payments. This way you can be prepared even if the interest rate changes. And with the Lump Sum column, you can even see how much small advance payments can help you pay off your loan.