An amortization schedule is a table format that lists periodic payments on a loan or mortgage over a period of time. It breaks down each payment into principal and interest and shows the remaining balance after each payment.
Setting up the Amortization Table
Let’s begin by defining the input cells where you will enter the known components for the loan.
- C2 - annual interest rate
- C3 - loan term in years
- C4 - number of payments per year
- C5 - loan amount
The next task is to create an amortization table with the labels (Payment Number, Payment, Principal, Interest, Balance) in A7:E7.
Calculate Total Payment Amount (PMT Formula)
In Excel, the PMT (rate, nper, pv, [fv], [type]) function is used to calculate the payment amount.
For consistency in payment frequencies, you should be consistent with the values supplied for the rate and nper arguments:
- Rate - It is the interest rate per period for the loan.
- Nper - It is the total number of payments for the loan.
- Pv - It is the present value: the total amount that a series of future payments is worth now.
Calculate Interest (IPMT Formula)
To find the interest for each periodic payment, we use the IPMT(rate, per, nper, pv, [fv], [type]) function.
- Per - the period for which you want to find the interest and must be in the range 1 to nper.
Find Principal (PPMT formula)
To calculate the principal of each periodic payment, use the PPMT formula. The arguments and syntax for the PPMT formula are identical to the IPMT formula.
Calculate the Remaining Balance
There are two different formulas to calculate the remaining balance. To find the balance after the first payment in cell E8, you will need to add up/combine the loan amount (C5) and the principal of the first period (D8).
Because a loan amount is a positive number and the principal is a negative number, the principal is subtracted from the loan amount.
For the second and all periods succeeding this, you can add up the previous balance and the first period's principal to get the required result.
Conclusion
In this article, we made the amortization loan schedule table and understood how the different functions work. The table is very useful in calculating the regular payments.
Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training
This Business Analytics certification course course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.
Do you have any questions for us? Feel free to mention them in this article’s comments section, and our experts will promptly answer them for you!