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.

Become an AI-powered Business Analyst

Purdue Post Graduate Program in Business AnalysisExplore Now
Become an AI-powered Business Analyst

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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

Become an AI-powered Business Analyst

Purdue Post Graduate Program in Business AnalysisExplore Now
Become an AI-powered Business Analyst

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

Purdue Post Graduate Program in Business AnalysisExplore Now
Become an AI-powered Business Analyst

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! 

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Post Graduate Program in Data Analytics

Cohort Starts: 11 Oct, 2024

8 months$ 3,500
Applied AI & Data Science

Cohort Starts: 15 Oct, 2024

14 weeks$ 2,624
Professional Certificate Program in Data Engineering

Cohort Starts: 21 Oct, 2024

32 weeks$ 3,850
Caltech Post Graduate Program in Data Science

Cohort Starts: 21 Oct, 2024

11 Months$ 4,500
Post Graduate Program in Data Science

Cohort Starts: 28 Oct, 2024

11 months$ 3,800
Data Analytics Bootcamp24 weeks$ 8,500
Data Scientist11 months$ 1,449
Data Analyst11 months$ 1,449