Debt helps make things happen—a new car, a college education, or even a new home. However, you must repay it. You can ask for a loan statement, but it's not always easy to understand. However, if you can see where your money goes, it's easier to see the light at the end of the loan.
This is where the amortization schedule comes in. You'll better visualize the loan by understanding how your debt works and how your payments affect it. You can even see how a little extra payment can significantly affect it. Here's how you can make one in Microsoft Excel.
What Is an Amortization Schedule?
According to Investopedia, "A loan amortization schedule is a complete table of periodic loan payments, showing the amount of principal and the amount of interest that comprise each payment until the loan is paid off at the end of its term."
So, when you create a schedule, you'll see how much you pay every month and how it's split between paying interest and the principal amount. Based on your monthly payments, you'll also see how long it takes to repay the loan. And, if you pay extra, you'll also see how it affects the overall payment schedule.
The amortization schedule helps you see how long it takes to pay off your debt and how every dollar you put towards paying it off affects it. You can also use it to plan major purchases, so you'll see how much you're paying every month and how much you'll save if you pay early.
How to Compute Your Amortization Details in Excel
For this example, let's say you're planning to buy a 2022 Mustang Mach 1 Premium. Its estimated net price is $68,529, and your monthly payment is $1,164. This computation is based on a $6,853 down payment for a 60-month term and a 5% annual percentage rate.
Input the data in Excel to confirm if they're correct. You can determine your monthly payments, annual interest rate, months to repay the loan, or total loan amount using the following formulas, respectively: PMT, RATE, NPER, and NPV. You can also use these formulas if the bank or dealer didn't include one data point (like interest rate).
These formulas also include FV, TYPE, and GUESS, but you can safely ignore them as they're irrelevant to your application.
So, if you look at the given data above, PMT=1,164, RATE=5%, NPER=60, and PV=61,676 (estimated net price less down payment). But if you have incomplete data, this is how you can compute for them.
Monthly Payments
To compute the monthly payments, use the PMT function:
=-PMT(RATE,NPER,PV,[FV],[TYPE]) Just remember to divide the RATE by 12, as we're computing for the monthly payments.
Interest Rate
If you need to determine the interest rate, use the RATE function:
=RATE(NPER,-PMT,PV,[FV],[TYPE],[GUESS]) This formula will give you the monthly rate, so don't forget to multiply the final result by 12.
Payment Period
When you're unsure how many months you're going to pay a loan, but you know how much you're borrowing and how much you can afford to repay every month, you can use the NPER function:
=NPER(RATE,-PMT,PV,[FV],[TYPE]) Again, divide the RATE by 12 since you're computing the number of months you will repay a loan.
Total Loan Amount
Finally, if you want to know how much you can borrow given a specific interest rate, the amount you can afford monthly, and the number of months you're willing to pay the loan, use the PV function:
=PV(RATE,NPER,-PMT,[FV],[TYPE] RATE should always be divided by 12 when your NPER is based on months; otherwise, you'll get a severely deflated value.
Now that you know how to find any missing value, it's time to create the amortization table.
Building a Fixed Interest Rate Loan Amortization Table
First, you need to add the details of your loan. This includes the Total Amount, Down Payment, Loan Amount, Interest Rate, Loan Period, and the Monthly Payment. To ensure you have the correct data, you can use the PMT formula above, especially if you want to experiment with the loan period or loan amount.
Alternatively, you can use the other formulas mentioned if you want to play with your monthly amortization, interest rate, or total loan amount figures.
After adding your details, create the loan amortization table. First, add the following columns under your initial data: Period, Beginning Balance, Monthly Payment, Principal Payment, Interest Payment, Cumulative Principal, Cumulative Interest, Ending Balance, Interest Rate, and Lump Sum Payments.
The First Row of Your Amortization Schedule Table
Once you've created the columns, fill out the first row (row 9) under Period with Month 1. After doing so, use Excel's autofill functionality to quickly fill the Period column. Select the cell and put your cursor on the small green box at the selected cell's lower-right corner, where the cursor should turn into a small black cross.
When your cursor transforms, click on the small green box, then drag your mouse downward. A small indicator will appear next to the mouse, indicating the number of months Excel will apply to the column. Stop and release the mouse button when it reaches Month 60.
After filling out the Period column, it's time to fill the rest of the first row. Under Beginning Balance, type =$B$3 to refer to the cell where you placed the Loan Amount. Don't forget to add a $ sign in front of the column letter and row number to ensure that it doesn't change when auto-populating the rest of the table.
- Under Monthly Payment, type:
=$B$6
This copies the interest rate you've input with your initial data. Don't forget to add the $ sign, so it doesn't change when you auto-populate your table.
- Under Principal, use the following formula:
=C9-E9 This subtracts the interest payment from the monthly payment.
- Under Interest, compute the amount with the following formula:
=B9*(I9/12) This multiplies the monthly payment with the monthly interest rate.
- For Cumulative Principal, type:
=D9 - For Cumulative Interest, type:
=E9 - Under Interest Rate, type:
=B4 This copies the interest rate you've computed previously. However, if your bank uses variable interest rates, you can change this down the line.
- Under Ending Balance, use the formula:
=B9-D9 This removes the principal amount you've paid from your beginning balance.
- Keep Lump Sum Payments blank unless you plan to pay an extra amount this period.
Completing the Loan Amortization Table
Now that you've populated the first row, it's time to add the rest of the table starting from the second row (row 10).
- Under Beginning Balance, type the following formula:
=H9-I9 This copies the ending balance you've previously paid, plus any advance payments you've made.
- For the Monthly Payment, Principal, Interest, and Ending Balance columns, copy the formula from the row above.
- Under Cumulative Principal, type the following:
=F9+D10 This adds the total principal amount you've paid in the last period to the principal payment you made this period.
- Under Cumulative Interest, type the following:
=G9+E10 This adds the total interest amount you've paid in the last period to the interest payment you made this period.
- Keep Lump Sum Payments blank unless you plan to pay an extra amount this period.
Once you've entered the formulas above, select the data from row 10, from the Beginning Balance to the Lump Sum Payments column. After selecting the cells, press Ctrl + C to copy them.
Select the cell under Beginning Balance for Month 3 (row 11). After doing so, scroll down to the row for Month 60 (row 68), press Shift on your keyboard, and then select the cell under theLump Sum Payments column (cell I68).
When you complete the selection, press Ctrl + V to insert the formulas you created; you'll then see your entire amortization table filled out with the needed information.
You can then send this to your partner if you're making a major purchase to help get to a decision. Even if they're using Google Sheets, they can import the Excel file to Sheets to see your computations.
Experiment With Your Data
You can change your data to see how it will affect your payment schedule. For example, if you think you can make sporadic additional payments (like an annual company bonus payout), add your estimated extra amount under the correct period for Lump Sum Payments.
You can also change the number of months you want to make payments. For example, if you want to pay off your loan in 48 months instead of 60, just change the number of months in your initial data, and your table will adjust to show how much you should pay every month.
Once your Beginning Balance falls below zero, that means your loan is fully paid! Just note that this table only works for fixed-rate loans. If your loan has a variable rate, you'll have to use a different approach.
