The Excel IPMT function is used to calculate the interest paid during a period of a loan or investment.
It could be used by an investment company to find out the interest earned on a loan given to a customer. The IPMT function could be used to decide what interest rate to apply to the loan, as changing the rate will change the potential interest earned, ensuring the loan is worth giving.
The IPMT function can also be used to find out the amount of interest earned during a specific period during the length of a loan or investment, such as the 3rd year.
The syntax for the IPMT function is:
=IPMT(rate, per, nper, pv, [fv], [type])
| Argument | Purpose |
|---|---|
| rate | The interest rate per period |
| per | The period for which you want to find the interest amount. It must be entered in the range 1 to nper |
| nper | The number of payment periods during the lifetime of the loan. If payments are monthly for 5 years, this would be entered 5*12 to calculate 60 payment periods |
| pv | The present value, or current amount of the loan or investment. |
| fv | The future value, or amount after the last payment is made.FV is optional and if omitted the value is assumed to be 0 i.e. the future value of a loan will be 0 |
| type | When the payments are due. It can be entered as 1 or 0 and is optional. If omitted the value is assumed to be 00 – Payments are made at the end of the period1 – Payments are made at the beginning of the period |
The examples below show the IPMT function being used to return the interest paid on a loan using different parameters.
| Function | Result |
|---|---|
| =IPMT(C4/12,3,B4*12,-A4) | £912Interest paid in the 3rd period of a 5 year loan |
| =PPMT(C4,5,B4,A4,0,1) | £2,406Interest paid in the last year of a 5 year loan, with a future value of 0 and payments made at the beginning of each year |
