How to Make a Loan Amortization Schedule in Excel

You can also see how much you still owe on the loan at any given time with the outstanding balance after a payment is made. A loan amortization plan in Excel is a spreadsheet that tracks how your loan payments are divided between principal (what you borrowed) and interest (the cost) over time. 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.

What is a Personal Loan?

But over the course of the borrowing term, the percentage attributable to principal payments increases (and the interest payments decline). Create the amortization schedule using the calculated payment, interest, and principal amounts. The PMT function is designed to calculate payments for loans based on constant payments and a constant interest rate. Make sure to input the parameters correctly – the rate (interest rate divided by the number of payments per year), nper (total number of payments), and pv (present value or loan amount).

Content of Loan Amortization Template

For this we can use the PMT, IPMT, PPMT functions along with SEQUENCE dynamic array function. The payment frequency can be annual, semi-annual, quarterly, bi-monthly, monthly, bi-weekly, or weekly. If a borrower can earn extra money, they might want to make some extra payments with their regular payments. To create a loan schedule, we will use the different formulas discussed above and expand them over the number of periods. The result is shown in the screenshot above, “Cumul 1st year,” so the analyzed periods range from repayment schedule in excel one to 12 of the first period (first month) to the twelfth (12th month). Over a year, we would pay $10,419.55 in principal and $ 3,522.99 in interest.

Excel Functions Tutorials

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. If you like the idea of a template that offers more like additional payments, payment frequency, and payment type, but appreciate a bit of visuals for your summary, this template is for you. From Spreadsheet123, this amortization schedule gives you those bonuses you want along with a convenient chart. The payment schedule will automatically show the scheduled repayment dates, the beginning balance, total payment, interest and principal payments, and the ending balance for each period.

1. Using Excel, you can get a better understanding of your mortgage in three simple steps.
2. You can download the template, use it in Excel for the web, or open it from the templates section of Excel on your desktop.
3. The simple interest loan calculator, unlocked and distributed under commercial use license, giving you rights to use this calculator with your clients (resale or distribution is not permitted).
4. If you want an easy way to view the schedule for your loan, you can create an amortization table in Microsoft Excel.
5. If a different loan term is specified, such as 10 years instead of 15 or 30 years, extra calculation results will be hidden.
6. As an extra precaution, we wrap this and all subsequent formulas in the IFERROR function.

Simple Interest Loan Calculator

This way, you can create a loan calculator and generate a payment schedule in Excel. An amortization schedule is a list of payments for a mortgage or loan, which shows how each payment is applied to both the principal amount and the interest. The schedule shows the remaining balance still owed after each payment is made, so you know how much you have left to pay. To create an amortization schedule using Excel, you can use our free amortization calculator which is able to handle the type of rounding required of an official payment schedule. You can use the free loan amortization schedule for mortgages, auto loans, consumer loans, and business loans. If you are a small private lender, you can download the commercial version and use it to create a repayment schedule to give to the borrower.

Whether you need a home loan amortization schedule in Excel or a car loan amortization schedule in Excel, we’ve got you covered. 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. By the end of this guide, you’ll have a powerful tool to manage your loans effectively and make informed financial decisions and will learn How to Make a Loan Amortization Schedule in Excel. The final step involves hiding cells with negative values in the loan repayment schedule.

SCAN is a new Excel 365 function that scans a list or array and runs a calculations on it. Then it returns the results the values for each step of the calculation. We can use PMT function to calculate the equated payment or installment amounts. The Commercial Version allows you to use the spreadsheet in your loan or financial advisory business.