In last months Excel Financial Functions video tip, we spoke about the PMT Function and its use in calculating the repayment amount on a loan. In this tip we will go a bit more in-depth and introduce you to the IPMT Function (interest payment function).
The significant difference between the PMT Function and the IPMT function is that the IPMT function calculates the Interest repayment for a specific period on a constant loan repayment plan and that it is used after the PMT function which calculates the actual monthly repayment amount.
The video tutorial below will walk you through using the IPMT Function (a full transcript of the video is below):
Get your Free copy of our Financial Functions Quick Reference Guide: Click Here
IPMT Function Tip Video Transcript:
The IPMT Function returns the interest payment for a given period. For example, a specific month or specific week for a repayment investment plan based on periodic constant payments and a constant interest rate.
Say that you had a loan repayment plan over thirty-six months. The IPMT function would allow you to take one of those periods and work out exactly how much your payment goes to interest and how much of your payment goes to principle.
The IPMT Function requires:
- Rate: The interest rate per period,
- Per: the specific period out of a number of periods (which both must be referenced),
- Nper: the total number of payment periods,
- Pv: the present value (sometimes called the principle),
- [Fv]: or in the case of a savings or investment plan – the future value.
- [Type]: Type is also an option, allowing you to select whether a payment is made at the start or at the end of the month which is important for interest calculations.
=IPMT(rate, per, nper, pv, [fv], [type])
Here we have a PMT Function (a payment function) already set-up. The payment function looks at a loan amount of $100,000 to be paid over seven-years at a constant eight percent interest. Using the PMT Function we can work out that our monthly repayment is just over $1500.
Using this information we can then build our IPMT Function and see how much of that money goes to interest.
For this example I am specifically looking at period four. I can reference my interest rate from the PMT Function (cell B9). The number of periods is seven-years multiplied by twelve to get eighty-four (cell C9) and to get the present value of the loan, would be $100,000 (cell C3).
I can now calculate the interest portion of this specific payment for period four. I reference the rate (cell B18), specific period (cell C18), the overall number of periods (cell D18) and the present value (cell E18).
=IPMT (B18, C18, D18, E18)
You will notice that the syntax in cell C1 has future value [fv] and [type] in square brackets, this means that these are optional parameters and you don’t have to use them.
The IPMT Function has now calculated the proportional monthly repayment rate for period four.
Get your Excel Financial Functions Quick Reference Guide Now:
This Quick Reference Guide is a curation of the Top 15 most commonly used Excel Financial Functions.
