In the previous post, we learnt about the PMT function, which calculates the payment for a loan based on constant payments and a constant interest rate.

For a loan, you may also want to calculate other figures, such as how much you will pay in interest during the life of the loan, or in each periodic payment, how much portion is of interest. Such metrics can be calculating using the following functions:

IPMT: This function calculates what portion of your period payment is going towards interest in a particular period.

PPMT: This function calculates what portion of your period payment is going towards principal in a particular period.

CUMIPMT: This function calculates the cumulative interest paid during a given period for a loan.

CUMPRINC: This function calculates the cumulative principal paid during a given period for a loan.

Let’s take an example to understand all these functions.

Assume that you have  $100,000 loan at a 6% interest rate to be paid in 10 years with equal annual installments.

Your yearly payment for this loan can be calculate using the PMT function

= PMT(rate,nper,pv,fv,type)

= PMT(6%,10,10000,0,0)

= -$13,586.80

This total payment is made up of both interest and principal payment.

Now, let’s answer a few questions?

How much of the third payment will be applied to interest?

= IPMT(rate,per,nper,pv,fv,type)

= IPMT(6%,3,10,10000,0,0)

= - $5,062.27

This means that in the 3rd year, $5,062.27 out of $13,586.80 will go towards interest payment.

How much of the third payment will be applied to principal?

= PPMT(rate,per,nper,pv,fv,type)

= PPMT(6%,3,10,10000,0,0)

= - $8,524.52

This means that in the 3rd year, $8,524.52 out of $13,586.80 will go towards principal payment.

What is the cumulative interest paid during the life of the loan?

= CUMIPMT(rate,nper,pv,start_period,end_period,type)

= CUMIPMT(6%,10,10000,1,10,0)

= - $35,867.96

This means that during the life of the loan (all 10 payments), you are paying $35,867.96 towards interest.

You can also use this formula to calculate the interest payment during any period. For example, from 3rd to 6th year, the total interest payment will be $17,055.67.

What is the cumulative principal paid during the life of the loan?

= CUMPRINC(rate,nper,pv,start_period,end_period,type)

= CUMPRINC(6%,10,10000,1,10,0)

= - $100,000

This means that during the life of the loan (all 10 payments), you are paying $35,867.96 towards principal, which is the same amount hat you borrowed. Again, you can also use this formula to calculate the principal payment during any period. For example, from 3rd to 6th year, the total principal payment will be $37,291.52.

You can download the PMT Functions.xls which contains these example calculations.