If the logical test is TRUE, the corresponding function is calculated; if FALSE, an empty string is returned. The tutorial shows how to build an amortization schedule in Excel to detail periodic payments on an amortizing loan or mortgage. In the first period column, enter «1» as the first period and then drag the cell down. In our case, we need 120 periods since a 10-year loan payment multiplied by 12 months equals 120.

1. The last two arguments are optional; the residual value defaults to zero, and payable in advance (for one) or at the end (for zero) is also optional.
2. It allows the borrower to track his/her loan repayment process easily.
3. The formula uses a combination of principal under a period ahead of the cell containing the principal borrowed.
4. In this blog post, we will guide you on how to create a simple amortization schedule in Microsoft Excel.
5. First, here’s how to calculate the monthly payment for a mortgage.

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. The process of paying back a loan can be challenging, particularly in terms of organization and accountability. To help with that, Excel can calculate and schedule your loan repayments. This may make the process of paying back the loan more achievable.

What Is a Monthly Amortization Schedule?

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.

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.

Calculating the Principal portionof each payment in the amortization schedule

Because the loan amount is a positive number and principal is a negative number, the latter is actually subtracted from the former. To check whether your calculations are correct at this point, add up the numbers in the Principal and Interest columns. The sum should be equal to the value in the Payment column in the same row. An amortizing loan is just a fancy way to define a loan that is paid back in installments throughout the entire term of the loan. The above adds the interest that will accumulate while you are not paying off your loan. These include the Total Years, Payments per Year, loan Amount, and Interest Rate.

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.

Double-check your formulas and make sure that you are using the appropriate functions and cell references. 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. 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.

Interest Rate

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.

A 2024 Guide to Google Sheets Date Picker

The interest is calculated for each period—for example, the monthly repayments over 10 years will give us 120 periods. The «payment type» option lets you choose whether payments are made at the beginning of the period or end of the period. If you choose the «beginning of period» option, no interest is paid in the first payment, and the Payment amount will be slightly different. You may need to change repayment schedule in excel this option if you are trying to match the spreadsheet up with a schedule that you received from your lender. This spreadsheet doesn’t handle prorated or «per diem» periods that are sometimes used in the first and last payments. There are two scenarios in which you could end up with negative amortization in this spreadsheet (interest being added to the balance).

If you need to extend the life of the loan by more than 30 years, you can do so by clicking the “30-Year Mortgage” tab at the bottom of the spreadsheet. Then, select the bottom line to click and drag it to the cells beneath. When you open the loan amortization Excel template, you will see two distinct sections. If you don’t want to make a template from scratch, don’t worry because you can use one of mine that I’ve built already. While these are aimed at mortgages, you can customize them according to your situation by editing the number of years and loan type, such as auto loans or unsecured personal loans.

How Does Excel Treat a Street Address

This quick one-time solution works well for a specific loan or mortgage. With this amortization table Excel template, I added a section where you can add and track the extra payments. It works the same way as the last template, but there is a cell for Extra Payments in the input section and a column that shows them in the table. Some loans in the UK use an annual interest accrual period (annual compounding) where a monthly payment is calculated by dividing the annual payment by 12.

This spreadsheet includes a second worksheet (the Loan Payment Schedule) that allows you to record the actual payment instead. When an amortization schedule includes rounding, the last payment usually has to be changed to make up the difference and bring the balance to zero. This might be done by changing the Payment Amount or by changing the Interest Amount. Changing the Payment Amount makes more sense to me, and is the approach I use in my spreadsheets. This spreadsheet-based calculator creates an amortization schedule for a fixed-rate loan, with optional extra payments.

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.