Loan Amortization Schedule and Calculator

For example, if the interest rate is 5%, enter “0.05” in a cell. Because some of the formulas cross reference each other (not circular reference!), they may display wrong results in the process. So, please do not start troubleshooting until you enter the very last formula in your amortization table. In this example, Period 0 is in row 9 and Period 1 is in row 10. If your amortization table begins in a different row, please be sure to adjust the cell references accordingly.

First, you need to set up the parameters for calculating loan schedule. The ability to type efficiently anywhere, anytime is more powerful than you think. For the latter, open Excel, go to the Home section, and select «More Templates.» Type Amortization in the search box and you’ll see the Simple Loan Calculator. Then save the newly created workbook as an Excel template and reuse whenever you want.

How to compare two Excel sheets using VLOOKUP? [FREE Template]

To detail each payment on a loan, you can build a loan amortization schedule. The first three arguments are the annual rate of the loan, the monthly payment needed to repay the loan, and the principal borrowed. The last two arguments are optional, the residual value defaults to zero.

In this formula, the Payment No references are in the cell in the same row. First create a range of cells like below to do all the calculations. The Commercial Version allows you to use the spreadsheet in your loan or financial advisory business. The spreadsheet is not password protected, which means you can see the calculations and customize the spreadsheet. Enter your basic information in the Loan Terms section and then use the drop-down lists to pick those additional details. Optionally, switch between the Amortization Schedule and Payment Schedule views, and turn Rounding off or on.

If you have variable additional payments, just type the individual amounts directly in the Extra Payment column. Because Excel’s built-in functions do not provide for additional payments, we will have to do all the math on our own. In this article, I’ll explain the benefits of an amortization schedule and how to create one in Microsoft Excel. To make things easier, I have also created two amortization schedule Excel templates for you to download.

1. To view the summary information about your loan at a glance, add a couple more formulas at the top of your amortization schedule.
2. In the Period column, insert the maximum number of payments you are going to allow for any loan, say, from 1 to 360.
3. The last two arguments are optional, the residual value defaults to zero.
4. With this amortization table Excel template, I added a section where you can add and track the extra payments.

How Does Excel Treat a Street Address

The formula uses a combination of principal under a period ahead of the cell containing the principal borrowed. This period begins to change when we copy and drag the cell down. The table below shows that at the end of 120 periods, our loan is repaid. The third column is the principal that will be repaid monthly. For example, for the 40th period, we will repay $945.51 in principal on our monthly total amount of $1,161.88. They help visualize the outstanding balance of a loan over time, especially when comparing basic interest loan offerings or home loans.

Set up the amortization table

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. Errors in your formulas can cause your calculations to be incorrect, leading to an inaccurate amortization schedule.

Because you now have many excessive period numbers, you have to somehow limit the calculations to the actual number of payments for a particular loan. This can be done by wrapping each formula into an IF statement. The logical test of the IF statement checks if the period number in the current row is less than or equal to the total number of payments.

If you want to go beyond basic, take a look at this next template from Vertex42. With it, you can manage your payment repayment schedule in excel frequency, compound period, payment type, and rounding along with extra payments you make. 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.

However, when creating an amortization schedule, it is the interest rate per period that you use in the calculations, labeled rate per period in the above spreadsheet. Start by entering the total loan amount, the annual interest rate, the number of years required to repay the loan, and how frequently the payments must be made. Then you can experiment with other payment scenarios such as making an extra payment or a balloon payment. Make sure to read the related blog article to learn how to pay off your loan earlier and save on interest. From Spreadsheet123, this amortization schedule gives you those bonuses you want along with a convenient chart.

Adding a Moratorium Period to Your Template

Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? In this article, let me explain the logic and formulas we can use Excel for this. As you can see, with a 30 year payment of $100,000 loan at 5.35% interest rate, more than half of the payments (50.26%) go towards interest. We can use PMT function to calculate the equated payment or installment amounts. See how the principal part increases and the interest part decreases with each payment.

It is also possible to calculate the principal and interest repayment for several periods, such as the first 12 months or the first 15 months. Note that the corresponding data in the monthly payment must be given a negative sign. Once you have added all the details, the Excel spreadsheet will recalculate and show the monthly payment details.

Create a loan amortization table with the headers shown in the screenshot below. In the Period column enter a series of numbers beginning with zero (you can hide the Period 0 row later if needed). The prior formulas allow us to create our schedule period by period, to know how much we will pay monthly in principal and interest, and to know how much is left to pay. Additionally, you can use the input section if you have regular extra payments, or you can manually enter random extra payments into the table throughout the life of your loan.