For this example, the first payment was made on January 1st, 2018, and the last payment will be made on December 1, 2020. Because some of the formulas cross reference each other (not circular reference!), they may display wrong results in the process. So, please do not start troubleshooting until you enter the very last formula in your amortization table.
How to Schedule Your Loan Repayments With Excel Formulas
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. Start by entering the loan amount, annual interest rate, term in years, and first payment date. Enter the values in the designated spot at the top of the sheet including loan amount, annual interest rate, loan period in years, and start date of the loan. 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.
- You can try different loan numbers in the green cells, to see how they affect the monthly loan payment amount.
- It is important to note that interest costs are typically highest at the beginning of the loan tenure, particularly for long-term loans.
- For example, with a loan amount of $5000, over 36 months, at an annual interest rate of 5%, the monthly payment is calculated to be $149.85.
- In column E, the formula shows how much of the monthly payment went toward paying off the principal amount.
- Subsequently, the interest rate charged by the lending bank or institution must be entered.
If the loan principal balance does in fact reach zero, the borrower met its mandatory debt obligations on time and managed to not default, i.e. did not miss an interest or principal payment. The Loan Amortization Schedule outlines the interest expense obligation and principal payments owed on a loan, such as a mortgage, including the outstanding balance of the financing. Amortization is the process of spreading out a loan into a series of fixed payments over time. Please note that the principal only includes the part of the scheduled payment (not the extra payment!) that goes toward the loan principal. 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.
Payment Amount Calculation
This loan amortization calculator Excel template can be used for a home mortgage loan—one of the most common types of amortizing loans. Use this template to calculate the balances paid and owed, as well as the distribution of payments across the interest and principal. This will help you determine how many payments remain until you officially own your home. You can also see how much you will save by making extra mortgage payments.
Step 3: Calculate Total Payments (PMT Formulae)
It assumes that the discount rate and number of years refer to the same period. Enter the loan amount, interest rate, and loan term into separate cells. You’ll want to keep your amortization calculations separate from other financial information to prevent any confusion. Since the interest has been calculated in column D, it’s easy to find the principal amount – just subtract the interest amount from the payment amount. If the ExtraPayment amount (named cell C6) is less than the difference between the remaining balance and this period’s principal (G9-E10), return ExtraPayment; otherwise use the difference.
In that case, the rate per period is simply the nominal annual interest rate divided by the number of periods per year. When the compound period and payment period are different (as in Canadian mortgages), a more general formula is needed repayment schedule in excel (see my amortization calculation article). This is a commercial use license of our Interest-Only Loan spreadsheet.
Step 3: Use the PMT function
It can be used to track missed payments, late payments, early payments, fees, and escrow. The formula uses a combination of principal under a period ahead of the cell containing the principal borrowed. The table below shows that at the end of 120 periods, our loan is repaid. 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. You can build a table in Excel that will tell you the interest rate, the loan calculation for the duration of the loan, the decomposition of the loan, the amortization, and the monthly payment.
With our inputs converted into the right units, we’re now ready to build our mortgage amortization table in Excel. 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). All Spreadsheet123 trade marks and copyright notices were moved outside the printing areas. So why not take the time to dive into Excel, play around with the functions, and see how they can work for you? The more familiar you become with these calculations, the more empowered you’ll feel about managing your debt and planning for a financially healthy future.