Easy-to-use amortization schedule Excel template

02 May Easy-to-use amortization schedule Excel template

Loans help us buy things that are too expensive to pay for with cash in hand, such as homes, cars, and business assets. Paying loans back involves using an amortization schedule, breaking the loan into equal monthly payments of principal and interest. You can change the loan amount, annual interest rate, or number of months, and the schedule will update accordingly. The formulas will use exactly the number of rows needed based on the number of periods.

The beginning loan amount changes each month since a portion of the principal balance is being repaid as part of the monthly payment. In this article, we will show how to calculate a payment and create a loan amortization schedule in Excel. The following table shows locally available mortgage rates which you can use to help calculate your monthly home loan payments.

Using Excel Formulas for Automation

The PMT function in Excel determines the total payment owed each period—inclusive of the interest and principal payment. The total payment, unlike the other two components, will remain constant over the entire borrowing term. However, if the interest rate changes, we use the PMT function to obtain the new scheduled payment.

1. Please download my loan amortization schedule template and use it to see the schedule for your data.
2. We add 1 at the end of the formula (see the screenshot below) to account for month “zero,” where there is no payment.
3. Right click on any of the above images to save a copy of our Excel loan calculator spreadsheet, or click on them to directly open it.
4. The next short video shows how the PMT function works, and the written instructions are below the video.
5. Note that each monthly payment is the same, the interest part decreases over time as more of the principal part is paid, and the loan is fully repaid by the end.

What are some examples of Excel amortization schedule templates?

Let’s use the PMT (payment) function to calculate the monthly payment based on the loan amount, annual interest rate, and number of months. For the purposes of this template, we will assume that payments are made monthly, and we will begin by assuming that each payment is for the same amount. Equal monthly payments would be the case if the interest rate is fixed and there are no additional principal reductions. 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.

This indicates that you’re subtracting this amount from your loan balance. It also helps you locate that column more quickly among all the words and numbers within your template. To do this, add another column to your table labeled “Extra Payment.” You can then adjust the principal and ending balance calculations to account for any additional payments you make. This allows you to experiment with different scenarios and see how quickly you can pay off your loan. The SEQUENCE function will generate a dynamic list of periods based on the entered number of months.

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. On a side note, if you find yourself wanting an even more integrated solution, consider checking out Bricks. Bricks integrates spreadsheets, docs, and presentations into one seamless tool, with AI at its core.

This will save you a lot of time designing, building, and adding formulas to your amortization schedules. An Excel amortization schedule template — what a mouthful — is a pre-structured document with fillable fields that helps you fill out a loan amortization schedule. A template simplifies what can often be a complex process with many difficult formulas. Now that you’ve set up your basic sheet, let’s calculate the monthly payment using Excel’s PMT function.

Calculate interest (IPMT formula)

You can leverage Excel’s AutoFill feature to enter a series of numbers faster. If you are looking to create a reusable amortization schedule with a variable number of periods, you will have to take a more comprehensive approach described below. The payment amount is calculated with the PMT(rate, nper, pv, fv, type) function. If you haven’t yet borrowed money, you can get a lower payment by borrowing less.

1. For your convenience, we publish local Los Angeles mortgage rates below to help you see currently available rates.
2. We can use IPMT function to calculate the interest portion in our schedule.
3. If you aim to create a reusable amortization schedule, enter the maximum possible number of payment periods (0 to 360 in this example).
4. Depending on your needs, you may want a few additional informational columns, such as the payment amount, cumulative interest, and cumulative principal.
5. Each column will use a different formula to calculate the appropriate amounts as divided over the number of repayment periods.
6. This article is a step-by-step guide to setting up loan calculations.

First, you need to set up the parameters for calculating loan schedule.

Whether you’re an individual or a business, mastering the art of creating an amortization schedule can be a valuable asset. AI tools can significantly streamline the process of setting up an amortization schedule. These tools can suggest formulas, identify errors, and even automate repetitive tasks. Note that we’re dividing the interest rate by the number of payments per year and multiplying the loan term by the number of payments per year to get the total number of repayment schedule in excel payments. The minus sign before the loan amount indicates that this is an outgoing payment.