Download Microsoft Excel Simple Loan Calculator Spreadsheet: XLSX Excel Basic Loan Amortization Schedule Template

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.

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.

By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above. For example, for the 40th period, we will repay $945.51 in principal on our monthly total amount of $1,161.88. We can use below SCAN function to get the balance at the end of each payment in our amortization table. For this we can use the PMT, IPMT, PPMT functions along with SEQUENCE dynamic array function. Once you’ve set up the first row, you can drag these formulas down to fill out the rest of your schedule.

Have a question about Amortization Schedule Template?

This Excel template also allows a user to enter extra payments that are used to pay down additional principal. Since the principal is being paid down even faster, then the loan will be repaid before its stated maturity. However, this does require substantial modifications from the previous worksheets. With a monthly payment schedule, we will need to make sure we convert any annual numbers to monthly.

Amortization schedule Excel template

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.

How to create a loan calculator in Excel?

If you can live with a bunch of superfluous period numbers displayed after the last payment, you can consider the work done and skip this step. If you strive for perfection, then hide all unused periods by making a conditional formatting rule that sets the font color to white for any rows after the last payment is made. All the arguments are the same as in the PMT formula, except the per argument that specifies the payment period. This argument is supplied as a relative cell reference (A8) because it is supposed to change based on the relative position of a row to which the formula is copied. Borrowing money can help you reach your financial goals, such as purchasing a home or moving up the career ladder. Using a loan calculator can show you how much you’ll pay each month and help you decide whether borrowing is the right move.

Formula for Interest portion for each payment in amortization schedule

As the result, you have a correctly calculated amortization schedule and a bunch of empty rows with the period numbers after the loan is paid off. Another simple way to lower your interest rate is to see if your lender offers a discount if you sign up for automated payments. Student loan lenders often offer a slight discount, such as 0.25%, to borrowers who sign up for automatic payments. 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. Finally, don’t be afraid to customize your amortization schedule to fit your personal needs. Maybe you want to add notes for each payment or include a column calculating cumulative interest.

Use color to highlight data

1. As we need to calculate this value for all the periods, we can use the SPILL RANGE in C10# as the payment_number.
2. Also, we will provide steps to create a comprehensive loan amortization table in Excel and how to set up a loan repayment schedule in Excel download for offline use.
3. When you refinance a mortgage, you will end up paying closing costs, much as you did when you got the first loan.
4. Paying loans back involves using an amortization schedule, breaking the loan into equal monthly payments of principal and interest.
5. I like this approach because it leverages the dynamic array formulas that spill into the number of rows needed.
6. For example, if the first payment is December 1, 2019, the second payment is January 1, 2020.

Fort Pitt Capital Group is an investment advisory firm and does not offer loans or any other banking services. She holds a Bachelor of Science in Finance degree from Bridgewater State University and helps develop content strategies. In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch. If you have an older version of Excel (other than 365), use the “non-365” worksheet to run the calculations.

1. Before starting the refinancing process, compare the cost of getting a new mortgage to what you’ll save over time to see if it’s worth it.
2. Each periodic payment includes both a principal portion and an interest portion.
3. While amortization schedules are great to understand and model cashflows (or plan for future payments), they are quite rigid and do not reflect real-world scenarios.
4. Likewise, if a loan is quoted in years, we will need to multiply the number of years by 12 to calculate the monthly payment properly.
5. Another simple way to lower your interest rate is to see if your lender offers a discount if you sign up for automated payments.
6. Amortization builds principal and interest into each payment, ensuring you pay both, and structures predictable payments for the borrower.
7. This is where Excel truly shines, as it will automatically adjust the cell references for each row, saving you a ton of manual work.

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.

Some credit cards offer 0% rates on balance transfers,  meaning you only have to worry about paying the principal due each month. If you take this route, make sure you pay the transferred balance off in full by the time the 0% offer ends. Another option for student loan borrowers, particularly those with federal repayment schedule in excel loans, is to see if they qualify for an income-based repayment plan. An income-based plan determines your monthly payment based on your earnings.

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.

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.