How to Create an Amortization Schedule in Excel
To build an amortization schedule in Excel, calculate the level monthly payment with =PMT(rate/12, term, -loan), then use IPMT and PPMT to split each payment into interest and principal and subtract the principal from the running balance. Copy those formulas down one row per payment and the table will show how your loan is paid off over time.
Microsoft Excel is a powerful tool that enables you to create financial sheets and calculate loan payments with ease. If you’re planning to borrow money to buy a house or to invest in a business, creating an amortization schedule can help you see how much you will pay in principal and interest over time. An amortization schedule is a table that shows the repayment schedule for a loan, including the total balance, monthly payment, interest, and principal. In this blog post, we will guide you on how to create a simple amortization schedule in Microsoft Excel.
Introduction
Creating an amortization schedule in Excel is easy and can help you plan your loan payments. It will show you how much you will pay in interest and principal over the life of your loan. In this blog post, we will show you how to create an amortization schedule in Microsoft Excel.
Step 1: Set Up Your Table
The first step in creating an amortization schedule is setting up your table. Open Microsoft Excel and create a new workbook. In the first row, create column headers for date, payment, principal, interest, and balance.
Step 2: Enter Loan Information
Enter the loan information in the appropriate cells. This includes the total loan amount, interest rate, loan term, and start date. Use the appropriate formulas to calculate the monthly payment and total payments.
Loan Amount
Enter the total loan amount in a cell in your spreadsheet. For example, if your loan is $100,000, enter “100000” in a cell.
Interest Rate
Enter the annual interest rate in a cell in your spreadsheet. For example, if the interest rate is 5%, enter “0.05” in a cell.
Loan Term
Enter the length of the loan in months in a cell in your spreadsheet. For example, if the loan term is 360 months (30 years), enter “360” in a cell.
Monthly Payment
Calculate the monthly payment using the PMT formula. Enter =PMT(interest rate/12, loan term, loan amount) in a cell. For example, if the interest rate is 5%, the loan term is 360 months, and the loan amount is $100,000, the formula is =PMT(0.05/12, 360, 100000). This will give you the monthly payment of -$536.82 (the result is negative because it represents money leaving your account; enter the loan amount as a negative number, or wrap the formula in a minus sign, to show it as a positive figure). If you want a deeper walkthrough of the function arguments, see our guide to the PMT function in Excel and how to calculate a monthly payment in Excel.
Total Payments
Calculate the total payments by multiplying the monthly payment by the loan term. Enter =monthly payment*loan term in a cell. For example, if the monthly payment is $536.82 and the loan term is 360 months (30 years), the formula is =536.82*360. This will give you total payments of about $193,255.
Step 3: Create the Amortization Table
Create the amortization table by inputting the formulas that calculate payments, principal, interest, and balance. Use the fill handle to copy the formulas down to fill in the entire table.
The “Date” column
In the first row of the date column, enter the start date of the loan. In subsequent rows, use the EDATE function to calculate the date of each payment. Enter =EDATE(start date, row number-1) in the cell. For example, if the start date is January 1, 2021, and you want to calculate the date of the second payment, the formula is =EDATE(C2,1). This will give you the date of the second payment, which is in February.
The “Payment” column
In the “Payment” column, enter the monthly payment. This will be the same for every row. For example, if the monthly payment is $537.85, enter that amount in each row of the “Payment” column.
The “Principal” column
In the “Principal” column, use the PPMT function to calculate the principal paid each month. The syntax is =PPMT(rate, per, nper, pv), where per is the payment number. Enter =PPMT(interest rate/12, payment number, loan term, loan amount) in the cell. For example, the principal paid in the first month is =PPMT(0.05/12, 1, 360, 100000), which returns -$120.15 (negative because it is an outgoing payment).
The “Interest” column
In the “Interest” column, use the IPMT function to calculate the interest paid each month. The syntax is =IPMT(rate, per, nper, pv). Enter =IPMT(interest rate/12, payment number, loan term, loan amount) in the cell. For example, the interest paid in the first month is =IPMT(0.05/12, 1, 360, 100000), which returns -$416.67. Notice that PPMT and IPMT for the same period always add up to the total PMT amount.
The “Balance” column
In the “Balance” column, use the previous balance minus the principal paid to calculate the balance for each month. Copy the formula down to fill in the entire column.
Conclusion
Congratulations! You have created an amortization schedule in Microsoft Excel. You can now use this schedule to make financial decisions and keep track of your loan payments. Remember to update the table if you make a payment early or late, or if your interest rate changes.
Additional Tips for Creating an Amortization Schedule in Excel
Here are some additional tips that can make your amortization schedule more accurate and helpful:
Include Extra Payments
When you make extra payments on your loan, it reduces the principal balance, which affects your amortization schedule. To include extra payments in your schedule, create a new column labeled “Extra Payment” and deduct the extra payment from the balance each month. If you would rather start from a pre-built layout, our Excel loan payment template handles these columns for you. This will give you a more accurate representation of your payments and how they impact the life of your loan.
Use Graphs to Visualize Your Schedule
You can create graphs or charts to visualize your amortization schedule and see how much you will pay in interest versus principal over time. Use Excel’s chart tools to create a graph that shows your balance and payments. This can help you get a better understanding of your loan and make decisions based on the data.
Double-check Your Formulas
Errors in your formulas can cause your calculations to be incorrect, leading to an inaccurate amortization schedule. Double-check your formulas and make sure that you are using the appropriate functions and cell references. Locking your rate and loan-amount cells with an absolute reference in Excel keeps the formulas pointing at the right inputs as you copy them down.
Save Your Schedule and Update Regularly
Save your amortization schedule and update it regularly, especially when changes occur, such as interest rate changes or extra payments. By keeping your schedule up-to-date, you will have a better idea of your loan’s progress and be able to make informed decisions regarding your finances.
Conclusion
Creating an amortization schedule in Excel is a simple and effective way to manage your loan payments and stay on top of your finances. By following the steps outlined in this article, you can easily create your own schedule and make informed decisions about your loan.
Frequently Asked Questions
Here are some common questions that people have regarding amortization schedules in Excel:
What is an amortization schedule?
An amortization schedule is a detailed table that outlines the payment schedule of a loan. It shows how much of each payment goes towards the principal and how much goes towards the interest, as well as how much is left in the balance after each payment.
Why is it important to create an amortization schedule?
Creating an amortization schedule can help you stay organized and keep track of your loan payments. It can also help you see how much of each payment goes towards interest versus principal, and how long it will take you to pay off your loan.
Can I change the frequency of my loan payments in an amortization schedule?
Yes, you can create an amortization schedule for any loan payment frequency, such as monthly, bi-weekly, or weekly. Simply change the formula for your monthly payment calculation to reflect the new frequency.
Can I use Excel’s built-in templates for creating an amortization schedule?
Yes, Excel has built-in templates for creating an amortization schedule. However, creating your own schedule gives you more control over the table and allows you to add additional features, such as graphs or extra payment columns.
What should I do if I miss a payment on my loan?
If you miss a payment on your loan, it’s important to contact your lender as soon as possible and make arrangements to catch up on your payments. This may involve paying a late fee or making a larger payment in the next period.
Which Excel functions do I need for an amortization schedule?
You need three core functions. PMT(rate, nper, pv) returns the fixed periodic payment, IPMT(rate, per, nper, pv) returns the interest portion of a given payment, and PPMT(rate, per, nper, pv) returns the principal portion. For all three, divide the annual rate by 12 for a monthly schedule and enter the loan amount (pv) as a negative number if you want the results to display as positive. PMT for a given period always equals IPMT plus PPMT. If you only need the payment figure, see the dedicated PMT function guide.
How is an amortization schedule different from compound interest?
An amortization schedule tracks how a fixed payment gradually pays down a loan’s principal while interest is charged on the shrinking balance. Compound interest, by contrast, measures how a balance grows when interest is added back to the principal over time. They are related ideas, but for savings or investment growth you would use the approach in our guide to calculating compound interest in Excel rather than the loan functions above.
Can I use this to plan a home loan?
Yes. A mortgage is simply a long-term amortizing loan, so the same PMT, IPMT, and PPMT formulas apply — just use your mortgage’s rate, term in months, and loan amount. For a focused walkthrough, see how to calculate a mortgage payment in Excel.
