The Best Guide to Make An Amortization Table In Excel

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.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore 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.

Become an AI-powered Business Analyst

With Purdue University's Business Analysis ProgramExplore Now

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.

Become an AI-powered Business Analyst

With Purdue University's Business Analysis ProgramExplore Now

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!