Create a loan amortization schedule in Excel with extra payments

Loan term in years – most fixed-rate home loans across the United States are scheduled to amortize over 30 years. Some foreign countries like Canada or the United Kingdom have loans which amortize over 25, 35 or even 40 years. As an extra precaution, we wrap this and all subsequent formulas in the IFERROR function. This will prevent a bunch of various errors if some of the input cells are empty or contain invalid values. To view the summary information about your loan at a glance, add a couple more formulas at the top of your amortization schedule. Because a loan is paid out of your bank account, Excel functions return the payment, interest and principal as negative numbers.

For example, if you’re purchasing a house or car, increasing your down payment can mean you end up with a smaller mortgage or car loan. Alternatively, you can look for homes or cars with lower prices to get a loan that better aligns with your budget. To create a loan schedule, we will use the different formulas discussed above and expand them over the number of periods. We find the arguments, rate, length, principal, and term (which are mandatory) that we already saw in the first part with the formula PMT.

2. PMT Formula Notes

Microsoft Office 365 allows you to take your Excel spreadsheet to the cloud. That way, you can access your amortization table from anywhere you have a device and an Internet connection. AI tools can assist you in quickly identifying errors or suggesting formula improvements. If you’re using a tool like Excel’s integrated AI features, you might already see some options popping up as you type.

An amortizing loan is repaid via periodic installments over the lifetime of the loan. Each periodic payment includes both a principal portion and an interest portion. For example, a fully amortizing loan for 24 months will have 24 equal monthly payments. Each payment applies some amount towards principal and some towards interest. To detail each payment on a loan, you can build a loan amortization schedule.

Set up input section for loan amortization table

1. To create a loan schedule, we will use the different formulas discussed above and expand them over the number of periods.
2. For businesses, they can be used to manage loans for capital investments, equipment purchases, or real estate transactions.
3. Over a year, we would pay $10,419.55 in principal and $ 3,522.99 in interest.
4. Microsoft Office 365 allows you to take your Excel spreadsheet to the cloud.

The calculator updates results automatically when you change any input. 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 result is shown in the screenshot above, “Loan Decomposition” over the period analyzed, which is “one;” that is, the first period or the first month. We pay $1,161.88 broken down into $856.20 principal and $305.68 interest. Using Excel is a great way to keep track of what you owe and to come up with a schedule for repayment that minimizes any fees you might end up owing.

Our first step is to convert the annual interest rate into a monthly interest rate by dividing it by 12, which leaves us with a monthly interest rate of 0.42%. To make this more dynamic, we will create a dynamic header using the ampersand (“&”) symbol in Excel. We can then change the loan term and the header will update automatically, as shown below. Of course, many amortizing term loans are longer than one year, so we can further enhance our worksheet by adding more periods and hiding those periods that are not in use. As mentioned earlier, each payment consists of an interest portion and a principal portion.

Exercise 3: Bonus – Additional Columns

The interest portion of an amortizing term loan is higher at the beginning of repayment schedule in excel the loan than at the end. This is because the loan’s principal is reduced over the life of the loan, resulting in lower interest expense as the loan reaches maturity. If your amortization table begins in a different row, please be sure to adjust the cell references accordingly.

1. To help make this easier, we’ve created a fully customizable template that you can export into a completed Excel spreadsheet with just a few clicks.
2. We find the arguments, rate, length, principal, and term (which are mandatory) that we already saw in the first part with the formula PMT.
3. The minus sign before the loan amount indicates that this is an outgoing payment.
4. This video shows a quick overview of the payment schedule template, and how it works.
5. Whether you’re managing data or visualizing it, Bricks AI can streamline the process for you, bringing all your productivity tools into one place.
6. Using our earlier example, the principal paydown in the first month is the difference in the total payment amount of $8,652.67 and the interest payment of $583.33, or $8,069.34.
7. In column E, the formula shows how much of the monthly payment went toward paying off the principal amount.

Fortunately, you can duplicate Excel templates to create multiple amortization schedules with just a few mouse clicks. Save them with different names — such as “mortgage amortization table” — and you’re good to go. For instance, you can make your payments in the amortization schedule colored red.

Bricks makes it easy to create docs, reports, presentations, charts, and visuals backed by your data. These advanced techniques can provide valuable insights into your financial situation, helping you make more strategic decisions about your loan management. The formula of each Excel function used in each column is as follows. Explore conventional mortgages, FHA loans, USDA loans, and VA loans to find out which option is right for you. Then save the newly created workbook as an Excel template and reuse whenever you want. If you prefer to have all the results as positive numbers, put a minus sign before the PMT, IPMT and PPMT functions.

This function helps determine the periodic payment for a loan based on constant payments and a constant interest rate. In this Excel loan payment schedule template, enter your loan information at the top of the worksheet, in the green cells. This video shows a quick overview of the payment schedule template, and how it works.

In each row, it’s the original loan amount, minus the total principal amounts in the rows above. Now, let’s calculate the amount of the payment applied to the principal each period with the PPMT (principal payment) function. Now that we understand how the SEQUENCE and SCAN functions work, we can move on to creating a dynamic amortization schedule. The first argument of SEQUENCE defines the number of rows, which in this case is the number of values we’d like it to return. It has additional arguments that can be helpful in other situations, so definitely check them out. Since the borrowing term is denoted in years, we’ll also adjust that input to be expressed on a monthly basis by multiplying it by twelve.

Please pay attention that we put a minus sign before the PMT function to have the result as a positive number. To prevent errors in case some of the input cells are empty, we enclose the PMT formula within the IFERROR function. In the Period column, insert the maximum number of payments you are going to allow for any loan, say, from 1 to 360.

If your payments are too high, an income-based repayment plan can give you a reprieve. However, it can also mean you end up owing more in the long run if your payments aren’t enough to cover the interest due on the loan. If you already have a loan, there are several ways to reduce the monthly payment. When you refinance, you take out a new loan and use the principal from the new loan to pay off the current one.