The IPMT function in Excel is used to calculate the interest portion of a loan given a specified payment period.
IPMT (rate, per, nper, pv, [fv], [type])
- rate – Interest rate per period.
- per – Payment period of interest.
- nper – Total number of payment periods.
- pv – Present value/total value of all payments now.
- fv – [optional] The cash balance desired after last payment is made. Defaults to 0.
- type – [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.
IPMT Function Example:
Using the above formula, we are going to calculate the interest by period on a 10,000 loan.
This can be achieved by using the below formula. You can also download the spreadsheet above to follow along.