IPMT
The interest part of a loan's payment in a period.
Group: Financial. Compatible with DAX.
Syntax
IPMT ( <rate>, <per>, <nper>, <pv> [, <fv>] [, <type>] )| Parameter | Type | Description |
|---|---|---|
rate | Number | The interest rate per period. For monthly payments on a yearly rate, divide that rate by 12. |
per | Number | The period asked about, from 1 to nper. |
nper | Number | How many payment periods there are, all told. |
pv | Number | The amount at the start: what was borrowed, or what was put in. |
fv | Number optional | The balance wanted once the last payment is made; 0 when left out. |
type | Number optional | When the payments fall: 0 (when left out) at the end of each period, 1 at its start. |
Returns
Number. The interest part of that period's payment.
Remarks
- Every payment of a level loan is part interest, part repayment. The interest part is the rate times what is still owed, so it shrinks over the loan while
PPMTgrows. - Cash going out is negative, cash coming in positive. A loan you take is a positive pv, so the payments on it come out negative; a deposit you make is negative.
- With payments at the start of each period (type 1), the first payment carries no interest.
- A per below 1 or above nper is an error.
- Give rate and nper in the same period: 6 % a year paid monthly for 4 years is a rate of 0.06 / 12 and an nper of 48.
Examples
Interest in the first and the last month
The loan of 2,000,000 at 4.5 % over 10 years: 0.375 % of the whole amount in month 1, very little in month 120.
EVALUATE
ROW (
"Month 1", IPMT ( 0.045 / 12, 1, 120, 2000000 ),
"Month 120", IPMT ( 0.045 / 12, 120, 120, 2000000 )
)| Month 1 | Month 120 |
|---|---|
| -7500 | -77.4384125206154 |