IPMT

Returns the portion of the periodic payment which is interest for a fixed rate loan or annuity.

Syntax:

IPMT(rate, period, numperiods, presentvalue, futurevalue, type)

rate: the interest rate, per period.

period: the period of the payment whose interest portion is to be calculated, numbered from 1.

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

presentvalue: the initial sum borrowed or invested.

futurevalue: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0.

type: when payments are made (optional - defaults to 0):0 - at the end of each period.1 - at the start of each period (including a payment at the start of the term).

With a fixed rate loan, where you make a constant payment each period to pay off the loan over the term, some of each period payment is interest on the outstanding capital, and some is a repayment of capital. Over time (as you pay off capital), the interest becomes less and the capital repayment becomes more.

IPMT returns the interest in the payment of a specified period. PPMT returns the capital repaid in the payment of that period. Together they add up to the actual payment, given by PMT.

When payments are made at the end of each period, the interest arises during that period.When payments are made at the start of each period, the interest arises during the preceding period.

By convention, money that you receive is positive, and money you pay is negative. For a loan where you receive a lump sum at the start, presentvalue is positive. For an investment where you pay a lump sum at the start, presentvalue is negative.

Example:

IPMT(5.5%/12, 12, 12*2, 5000, 0, 0)

returns -12.72 in currency units. You take out a 2 year loan of 5000 currency units at a yearly interest rate of 5.5%, making monthly payments at the end of the month. In the 12th month you make your usual monthly repayment, of which 12.72 is interest.

Application:

An application of using the IPMT function is when you want to calculate the interest portion of a loan payment for a specific period. This is useful for understanding how much of your monthly payment is going towards interest versus the principal, especially in the early stages of a loan where interest is a larger component.

Let's consider a home mortgage loan.

Scenario:

Sarah took out a mortgage loan to buy a house. She wants to see how much interest she'll pay in the first year of her loan.

Loan Amount (PV): $300,000
Annual Interest Rate: 4.5%
Loan Term: 30 years
Payment Frequency: Monthly

To use the IPMT function, we need to convert the annual rate and term to monthly values.

Monthly Interest Rate (Rate): 4.5% / 12 = 0.00375
Total Number of Payments (Nper): 30 years * 12 months/year = 360

Sarah wants to calculate the interest paid in the first month (Period 1), and then in the 12th month (Period 12).

The IPMT function is structured as: IPMT(rate, per, nper, pv, [fv], [type])

rate: The interest rate per period.
per: The period for which you want to find the interest. This must be between 1 and nper.
nper: The total number of payment periods in the loan.
pv: The present value, or the total amount that a series of future payments is worth now; also known as the principal.
fv (optional): The future value, or a cash balance you want to attain after the last payment is made.If omitted, it's assumed to be 0.
type (optional): The number 0 or 1. 0 indicates payments are due at the end of the period (standard), while 1 indicates payments are due at the beginning. If omitted, it's assumed to be 0.

Calculation:

1. Interest for the first month (Period 1):

IPMT(0.00375, 1, 360, 300000)

Result: -$1,125.00

2. Interest for the twelfth month (Period 12):

IPMT(0.00375, 12, 360, 300000)

Result: -$1,108.39

The result is negative because it represents a cash outflow (payment).

Table of Results:

This table shows the interest portion of the payment for the first few months, demonstrating how the interest portion of the payment decreases over time as the principal balance is paid down.

	Payment Period	IPMT Formula	Interest Portion of Payment
	A	B	C
1	1	IPMT(4.5%/12, 1, 30*12, 300000)	-$1,125.00
2	2	IPMT(4.5%/12, 2, 30*12, 300000)	-$1,123.52
3	3	IPMT(4.5%/12, 3, 30*12, 300000)	-$1,122.03
4	12	IPMT(4.5%/12, 12, 30*12, 300000)	-$1,108.39
5	360	IPMT(4.5%/12, 360, 30*12, 300000)	-$5.68

As you can see from the table, the IPMT function helps Sarah understand that in the beginning of her loan, a significant portion of her payment goes toward interest. By the end of the loan (period 360), the interest portion of her payment is very small, as the principal balance has been almost entirely paid off.