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.
- The way to simulate this using our Amortization Schedule is by setting both the compound period and the payment frequency to annual.
- Start by entering your basic mortgage information, select the compound period and payment frequency from the drop-down lists, and see your balance for any year.
- Start by entering the loan amount, annual interest rate, term in years, and first payment date.
- The above adds the interest that will accumulate while you are not paying off your loan.
- Optionally enter extra payments into the schedule by date or use one of the mentioned tabs to manage your payments.
How to Schedule Your Loan Repayments With Excel Formulas
Due to the use of relative cell references, the formula adjusts correctly for each row. To calculate the remaining balance for each period, we’ll be using two different formulas. Please pay attention, that we use absolute cell references because this formula should copy to the below cells without any changes. With all the known components in place, let’s get to the most interesting part – loan amortization formulas.
When you create a PMT formula, such as PMT(rate, nper, pv, [fv], [type]), you need several data points. As indicated by the brackets, fv and type are optional arguments. The minus sign in front of PMT is necessary as the formula returns a negative number.
Bonus #1: Home Mortgage Calculator (Commercial Version)
In the early stages of the loan, you will mostly pay off interest, and in the later stages, you will pay off the principal loan. 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.
The 2 Best Amortization Schedule Excel Templates for 2024
The first is if your payment isn’t enough to cover the interest. The second is if you choose a compound period that is shorter than the payment period (for example, choosing a weekly compound period but making payments monthly). Creating repayment schedule in excel an amortization schedule can help you stay organized and keep track of your loan payments. It can also help you see how much of each payment goes towards interest versus principal, and how long it will take you to pay off your loan. Save your amortization schedule and update it regularly, especially when changes occur, such as interest rate changes or extra payments.
Write formulas to calculate the amortization schedule
Please download my loan amortization schedule template and use it to see the schedule for your data. Specifically for mortgage loans, take a look at this template from Vertex42. You have options for fixed or variable rate loans, can view your balance at the end of a specific year with interest and principal paid, and can enter tax deduction details. If you want an easy way to view the schedule for your loan, you can create an amortization table in Microsoft Excel. We’ll show you several templates that make creating this schedule easy so that you can track your loan.
Yes, Excel has built-in templates for creating an amortization schedule. However, creating your own schedule gives you more control over the table and allows you to add additional features, such as graphs or extra payment columns. Yes, you can create an amortization schedule for any loan payment frequency, such as monthly, bi-weekly, or weekly. Simply change the formula for your monthly payment calculation to reflect the new frequency. In the above example, we built a loan amortization schedule for the predefined number of payment periods.
All Spreadsheet123 trade marks and copyright notices were moved outside the printing areas. If you miss a payment on your loan, it’s important to contact your lender as soon as possible and make arrangements to catch up on your payments. This may involve paying a late fee or making a larger payment in the next period. In the “Balance” column, use the previous balance minus the principal paid to calculate the balance for each month. 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.
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. If you have payments as positive numbers, remove the minus sign from the above formulas. In the Period column, insert the maximum number of payments you are going to allow for any loan, say, from 1 to 360. You can leverage Excel’s AutoFill feature to enter a series of numbers faster. The above formula goes to E9, and then you copy it down the column.
Please note that the principal only includes the part of the scheduled payment (not the extra payment!) that goes toward the loan principal. If you aim to create a reusable amortization schedule, enter the maximum possible number of payment periods (0 to 360 in this example). 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.
Other Loan Calculators & Schedules
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. Microsoft Excel is a powerful tool that enables you to create financial sheets and calculate loan payments with ease. An amortization schedule is a table that shows the repayment schedule for a loan, including the total balance, monthly payment, interest, and principal.
Also, if you found this useful, please check out my balance sheet templates. Are you looking for a loan or figuring out the best way to pay down an existing one? Please tell us how you’re saving on your loans in the comments. As you can see in the GIF, the balance automatically updates for the rest of the loan period. Here, the Payment No for the formula is also referenced in the same cell and row.
This example teaches you how to create a loan amortization schedule in Excel. If a borrower can earn extra money, they might want to make some extra payments with their regular payments. Download our free Excel Monthly Amortization Schedule template to generate your monthly amortization schedule and read the article to learn how to use this article efficiently.