Loan Amortization Schedule with a Variable Interest Rate in Excel Free Download

The table below shows that at the end of 120 periods, our loan is repaid. The minus sign in front of PMT is necessary as the formula returns a negative number. The first three arguments are the rate of the loan, the length of the loan (number of periods), and the principal borrowed.

The “Interest” column

Use the IPMT function to calculate the interest part of the payment. With Zebra BI’s Debt Schedule Excel Template, users can gain a clear financialoverview, streamline debt management, and make informed strategic decisions forlong-term financial stability. In this section, the user must input the Principal amount they wish to borrow or are planning to borrow. Subsequently, the interest rate charged by the lending bank or institution must be entered. An amortizing loan is just a fancy way to define a loan that is paid back in installments throughout the entire term of the loan. In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Input essential fields such as the lender’s name, borrower’s name, loan type, and contact information. This ensures clarity and proper documentation for future reference. Loan Repayment schedule is included in the Financial Reporting module of the CFA Level 1 Curriculum. The Data Input Section consists of two columns with predefined formulas. The user is required to enter the necessary data in the column on the left side.

What Does The Amortization Schedule Template Contain?

We can use IPMT function to calculate the interest portion in our schedule. As we need to calculate this value for all the periods, we can use the SPILL RANGE in C10# as the payment_number. We can use PMT function to calculate the equated payment or installment amounts. The result is shown in the screenshot above, “Loan Decomposition” over the period analyzed, which is “one;” that is, the first period or the first month. We pay $1,161.88 broken down into $856.20 principal and $305.68 interest.

The interest portion of the payment is recalculated only at the start of each year. The way to simulate this using our Amortization Schedule is by setting both the compound period and the payment frequency to annual. Usually, the interest rate that you enter into an amortization calculator is the nominal annual rate. However, when creating an amortization schedule, it is the interest rate per period that you use in the calculations, labeled rate per period in the above spreadsheet. The following table shows locally available mortgage rates which you can use to help calculate your monthly home loan payments.

Make a loan summary

Creating an amortization schedule in Excel is easy and can help you plan your loan payments. It will show you how much you will pay in interest and principal over the life of your loan. In this blog post, we will show you how to create an amortization schedule in Microsoft Excel. The “payment type” option lets you choose whether payments are made at the beginning of the period or end of the period. If you choose the “beginning of period” option, no interest is paid in the first payment, and the Payment amount will be slightly different.

It can be used to track missed payments, late payments, early payments, fees, and repayment schedule in excel escrow. Because Excel’s built-in functions do not provide for additional payments, we will have to do all the math on our own. To view the summary information about your loan at a glance, add a couple more formulas at the top of your amortization schedule. 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. All the arguments are the same as in the PMT formula, except the per argument that specifies the payment period.

In this blog post, we will guide you on how to create a simple amortization schedule in Microsoft 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.

Amortization Schedule Table:

The interest is calculated for each period—for example, the monthly repayments over 10 years will give us 120 periods. We have seen how to set up the calculation of a monthly payment for a mortgage. But we may want to set a maximum monthly payment that we can afford that also displays the number of years over which we would have to repay the loan. For that reason, we would like to know the corresponding annual interest rate. Payments per year – defaults to 12 to calculate the monthly loan payment which amortizes over the specified period of years. If you would like to pay twice monthly enter 24, or if you would like to pay biweekly enter 26.

• 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.
• But we may want to set a maximum monthly payment that we can afford that also displays the number of years over which we would have to repay the loan.
• Lenders, too, use amortization tables to present structured loan options to potential borrowers.
• The amortization schedule is handy for fixed-rate loans, where the payment remains constant, but the distribution between principal and interest shifts over time.

You will find your revised Amortization table and two summary tables to compare the amortization with and without moratorium periods. You will get a summary of the Amortization table with and without moratorium periods. If the user plans to make any additional optional payments alongside their regular installments, they can enter those in the designated Optional Payments cell. As payment schedules are typically monthly, the number of payments per year is designated as 12. It is important to note that interest costs are typically highest at the beginning of the loan tenure, particularly for long-term loans.

Enter the length of the loan in months in a cell in your spreadsheet. For example, if the loan term is 360 months (30 years), enter “360” in a cell. You will get your amortization schedule and two summary tables to compare loan amortization with and without the moratorium.

The last three arguments are optional, and the residual value defaults to zero; the term argument for managing the maturity in advance (for one) or at the end (for zero) is also optional. Finally, the estimate argument is optional but can give an initial estimate of the rate. In this template, you will find your revised amortization table with different moratorium and grace periods. You will get the revised amortization schedule and two summary tables to compare loan amortization with and without moratorium periods.

• It helps them visualize their home equity growth and assess refinancing opportunities for better loan terms.
• If you choose the “beginning of period” option, no interest is paid in the first payment, and the Payment amount will be slightly different.
• She holds a Bachelor of Science in Finance degree from Bridgewater State University and helps develop content strategies.
• We can use PMT function to calculate the equated payment or installment amounts.
• Banks, credit unions, and other lenders use amortization schedules to structure loan repayment plans for borrowers.

We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000. A Debt Schedule Excel Template is a structured spreadsheet designed to helpbusinesses and financial professionals track and manage outstanding debt obligationsefficiently. It provides a comprehensive view of all debts, including loan details,interest rates, repayment schedules, and outstanding balances. Banks, credit unions, and other lenders use amortization schedules to structure loan repayment plans for borrowers. These help financial institutions track outstanding balances, interest accruals, and payment progress, ensuring compliance with lending agreements.

Multiple Loan Amortization Schedule Excel Template Tips

By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above. We can use below SCAN function to get the balance at the end of each payment in our amortization table. For this we can use the PMT, IPMT, PPMT functions along with SEQUENCE dynamic array function. To create a loan schedule, we will use the different formulas discussed above and expand them over the number of periods. We find the arguments, rate, length, principal, and term (which are mandatory) that we already saw in the first part with the formula PMT. But here, we need the “start_date” and “end_date” arguments also.

It shows how much of each payment goes towards the principal and how much goes towards the interest, as well as how much is left in the balance after each payment. Errors in your formulas can cause your calculations to be incorrect, leading to an inaccurate amortization schedule. Double-check your formulas and make sure that you are using the appropriate functions and cell references. 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.