The Best Guide to Make An Amortization Table In Excel

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.

set 1

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.

pmt1

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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.

IPMT

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.

ppmt

Free Course: Introduction to MS Excel

Master the Fundamentals of MS ExcelEnroll Now
Free Course: Introduction to MS Excel

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.

balance-amortization

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 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! 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.