For fixed-rate loans, this reduces the balance and the overall interest, and can help you pay off your loan early. But, the normal payment remains the same (except for the last payment required to bring the balance to zero – see below). The amortization schedule is a loan repayment schedule showing all values of payments, interest paid, principal paid, and remaining balance after each payment. Depending on loan amount and tenures, payment per installment is calculated, and a comparison of principal, interest, and remaining balance can be made. This spreadsheet-based calculator creates an amortization schedule for a fixed-rate loan, with optional extra payments.
To help the understanding and calculation of loan amortization, I have created a user-friendly Loan Amortization Template in Excel format. This template incorporates preset formulas, requiring users to input their specific loan amounts and dates. Subsequently, the template automatically calculates all relevant figures, streamlining the amortization process. Loan Amortization refers to the systematic repayment of a debt over an extended period through a series of scheduled installments. To amortize a loan effectively, the periodic payments must be substantial enough to cover not only the interest that has accrued but also a portion of the principal amount.
Write formulas to calculate the amortization schedule
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. Enter the following formulas in row 10 (Period 1), and then copy them down for all of the remaining periods. 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 repayment schedule in excel enclose the PMT formula within the IFERROR function. 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 a borrower can earn extra money, they might want to make some extra payments with their regular payments.
- We find the arguments, rate, length, principal, and term (which are mandatory) that we already saw in the first part with the formula PMT.
- By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above.
- Note that the corresponding data in the monthly payment must be given a negative sign.
Calculating the equal payments for each 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.
- Simply change the formula for your monthly payment calculation to reflect the new frequency.
- To amortize a loan effectively, the periodic payments must be substantial enough to cover not only the interest that has accrued but also a portion of the principal amount.
- 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.
- The schedule shows the remaining balance still owed after each payment is made, so you know how much you have left to pay.
This might be done by changing the Payment Amount or by changing the Interest Amount. Changing the Payment Amount makes more sense to me, and is the approach I use in my spreadsheets. Optional extra payment – if you want to add an extra amount to each monthly payment then add that amount here & your loan will amortize quicker. If you add an extra payment the calculator will show how many payments you saved off the original loan term and how many years that saved.
As indicated by the brackets, fv and type are optional arguments. In the first period column, enter „1“ as the first period and then drag the cell down. In our case, we need 120 periods since a 10-year loan payment multiplied by 12 months equals 120.
Configure Payment Terms
Regular updates help maintain financial transparency and support long-term planning. For example, if the interest rate is 5%, enter “0.05” in a cell. The payment frequency can be annual, semi-annual, quarterly, bi-monthly, monthly, bi-weekly, or weekly. 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.
Then you can experiment with other payment scenarios such as making an extra payment or a balloon payment. Make sure to read the related blog article to learn how to pay off your loan earlier and save on interest. We can use PMT & SEQUENCE functions to quickly and efficiently generate the full loan amortization table for any number of years. First, here’s how to calculate the monthly payment for a mortgage. Using the annual interest rate, the principal, and the duration, we can determine the amount to be repaid monthly. Download our free Excel weekly amortization schedule to generate your weekly loan amortization schedule and read the article to learn how to use this template.
Interest Rate, Compound Period, and Payment Period
Performing time series analysis consists in studying data points that are organized chronologically and equally spaced i… IF AND Excel template demonstrating the use of IF&AND functions in Excel for logical analysis and decision-making. I’m Bill Whitman, the founder of LearnExcel.io, where I combine my passion for education with my deep expertise in technology.
However, this approach makes the calculations simpler than prorating the interest. Please note that the principal only includes the part of the scheduled payment (not the extra payment!) that goes toward the loan principal. 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. Note that the corresponding data in the monthly payment must be given a negative sign. Loan start date – the date which loan repayments began, typically a month to the day after the loan was originated.
Aids in Budgeting and Financial Planning
As more payments go toward reducing the principal, equity increases. Borrowers aiming to tap into their home equity for refinancing, home improvement, or investment purposes can use the schedule to determine the optimal time to leverage their accumulated value. The video covers how to create a simple loan calculator (cols A and B in the screenshot above) and how to create a repayment schedule (cols E, F and G above). The other can be used in any version of Excel although if you have Microsoft 365 I recommend using the first method – it’s simpler and more flexible. An amortization schedule is a detailed table that outlines the payment schedule of a loan.
Track and Update Regularly
This comprehensive approach ensures transparency and enables borrowers to track their loan repayment progress effectively. With this template, it is really quite simple to handle arbitrary extra payments (prepayments or additional payments on the principal). You simply add the extra payment to the amount of principal that is paid that period.
How Do I Create a Loan Repayment Schedule in Excel?
To include extra payments in your schedule, create a new column labeled “Extra Payment” and deduct the extra payment from the balance each month. This will give you a more accurate representation of your payments and how they impact the life of your loan. A loan payment schedule usually shows all payments and interest rounded to the nearest cent. That is because the schedule is meant to show you the actual payments. Many loan and amortization calculators, especially those used for academic or illustrative purposes, do not do any rounding.