The Excel ISPMT function calculates the interest paid during a given period of an investment where principal payments are equal. The given period is specified as a zero-based number instead of a 1-based number.
Get interest paid for specific period
Interest amount in given period
=ISPMT (rate, per, nper, pv)
rate - Interest rate.
per - Period (starts with zero, not 1).
nper - Number of periods.
pv - Present value.
The ISPMT function calculates the amount of interest in given period of an investment where principal payments are equal. The given period is specified as a zero-based number instead of a 1-based number. For example, to calculate the interest amount in payments for a loan where the amount is $10,000, the interest rate is 10%, and there are 5 periods in which the principal payment is constant (even), you can use:
=ISPMT(10%,0,5,-10000)// interest in period 1=ISPMT(10%,1,5,-10000)// interest in period 2=ISPMT(10%,2,5,-10000)// interest in period 3=ISPMT(10%,3,5,-10000)// interest in period 4=ISPMT(10%,4,5,-10000)// interest in period 5
In the example shown, the formula in H11, copied down, is:
The Excel IPMT function can be used to calculate the interest portion of a given loan payment in a given payment period. For example, you can use IPMT to get the interest amount of a payment for the first period, the last period, or any period in...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.