Application of PMT, IPMT & PPMT Formula in Excel

These 3 formulas in Excel are going to be useful when you are creating a loan amortization schedule or payment waterfall or just analyzing loan or investment scenarios.

There are 3 different formulas that we are going to combine into our analysis here in Excel.

Formula Review:

• PMT Formula: Calculates the payment for a loan based on constant payments and a constant interest rate, which is the blended amount of the payment.
• IPMT Formula: The IPMT formula returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate when it is applied.
• PPMT Formula: The PPMT function calculates the principal payment of a given period for an investment or loan based on periodic, constant payments and a constant interest rate.

Example:

On January 1, 2020, Flexco purchased equipment through a loan for $1,000,000 which is payable under a few options. The loan options have interest rates which vary between 5% and 10%. The loan applicant has decided that they would like to pursue the loan arrangement under option A and asks for the amortization table.

Solution:

Using the PMT, IPMT and PPMT formula in excel, we can create the amortization table below for loan option A. PPMT calculates the principal, IPMT calculates the interest, and PMT calculates the blended payment.

* Note this table is only a sample of an example with a few data entries, and the formulas are based on the whole population of raw data which are not reflected in the example.

Summary

These 3 formulas come in handy when you are trying to create a loan amortization schedule or an investment schedule and you want to calculate the amount of the principal or interest associated with it.

This works in a situation where there are constant payments and in this case, it was $16,038 because that is the amount that is required in order to pay off the $1 million loan over the 72 months based on the interest rate of 5% per year.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!