ISPMT

Returns the interest paid in a period for a fixed rate loan.

Syntax:

ISPMT(rate, period, numperiods, principal)

rate: the interest rate per period.

period: the period for which interest is to be calculated.

numperiods: the total number of payment periods in the term.

principal: the initial sum borrowed.

ISPMT considers a fixed rate loan, where you repay the same amount of capital at the start of each period, and pay interest on the outstanding balance at the end of each period. The interest you pay will depend of the remaining outstanding balance each period, and will decline with time. ISPMT returns that interest.

The capital you repay each period is given by principal/numperiods.

This is not the same as IPMT, where the total sum you pay each period (interest plus capital) is constant.

Example:

ISPMT(5%, 1, 4, 12000)

returns -450. You take out a 4 year loan of 12000 currency units at a yearly interest rate of 5%, making yearly payments. On the day you take out the loan, you repay capital of 12000/4 = 3000, which leaves an outstanding capital balance of 12000 - 3000 = 9000. At the end of the 1st period interest will be due, that is 9000 * 5% = 450. The sign is negative, because you have to pay the interest.

ISPMT(5%, 2, 4, 12000)

returns -300. You take out a 4 year loan of 12000 currency units at a yearly interest rate of 5%, making yearly payments. At the start of the second period, you pay the 450 interest due as above, and you also repay a second 3000 of capital, leaving an outstanding capital balance of 9000 - 3000 = 6000. At the end of the 2nd period interest will be due, that is 6000 * 5% = 300. The sign is negative, because you have to pay the interest.

ISPMT(5%, 3, 4, 12000)

returns -150. You take out a 4 year loan of 12000 currency units at a yearly interest rate of 5%, making yearly payments. At the start of the third period, you pay the 300 interest due as above, and you also repay a third 3000 of capital, leaving an outstanding capital balance of 6000 - 3000 = 3000. At the end of the 3rd period interest will be due, that is 3000 * 5% = 150. The sign is negative, because you have to pay the interest.

ISPMT(5%, 4, 4, 12000)

returns 0. You take out a 4 year loan of 12000 currency units at a yearly interest rate of 5%, making yearly payments. At the start of the fourth period, you pay the 150 interest due as above, and you also repay a fourth 3000 of capital, leaving an outstanding capital balance of 3000 - 3000 = 0. There is thus no interest to pay during the fourth period.

Application:

Calculating Interest on a Car Loan

Let's imagine you've taken out a loan to buy a new car.

Loan Amount (PV): $30,000
Annual Interest Rate: 5%
Loan Term: 5 years (60 months)

We want to calculate the interest paid during the first, 10th, and 45th months.

First, we need to adjust the annual rate and the number of periods to be monthly.

Monthly Rate (Rate): 5% / 12 = 0.0041667
Total Periods (Nper): 5 years * 12 months/year = 60

Now we can use the ISPMT function to calculate the interest for specific periods.

	Period	Formula	Calculation	Interest Paid
	A	B	C	D
1	1st Month	ISPMT(5%/12, 1, 60, 30000)	ISPMT(0.0041667, 1, 60, 30000)	-122.91765
2	10th Month	ISPMT(5%/12, 10, 60, 30000)	ISPMT(0.0041667, 10, 60, 30000)	-104.1675
3	45th Month	ISPMT(5%/12, 45, 60, 30000)	ISPMT(0.0041667, 45, 60, 30000)	-31.25025

The results are negative because it represents an outflow of money (a payment).

Analysis of the results:

In the first month, a significant portion of the payment goes toward interest, as the principal balance is at its highest.
By the 10th month, the interest paid has decreased because the principal has been paid down, and the interest is calculated on a smaller remaining balance.
By the 45th month, the interest portion of the payment is much smaller. Most of the payment now goes toward the principal.

This example clearly shows how the ISPMT function can be used to track the interest portion of a loan payment over time, demonstrating the typical amortization schedule where interest payments decrease as the principal balance is reduced.