How to Create an Amortization Schedule in Excel

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 $537.85.

Total Payments

Calculate the total payments using the total function. Enter =total(monthly payment*loan term) in a cell. For example, if the monthly payment is $537.85 and the loan term is 360 months (30 years), the formula is =total(537.85*360). This will give you the total payments of $193,626.

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. Enter =PPMT(interest rate/12, row number-1, loan term, loan amount) in the cell. For example, if you want to calculate the principal paid in the second month, the formula is =PPMT(0.05/12, 1, 360, 100000). This will give you the principal paid in the second month, which is $169.90.

The “Interest” column

In the “Interest” column, use the IPMT function to calculate the interest paid each month. Enter =IPMT(interest rate/12, row number-1, loan term, loan amount) in the cell. For example, if you want to calculate the interest paid in the second month, the formula is =IPMT(0.05/12, 1, 360, 100000). This will give you the interest paid in the second month, which is $367.95.

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

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.