NPER
The number of periods a loan or an investment takes at a constant payment and rate.
Group: Financial. Compatible with DAX.
Syntax
NPER ( <rate>, <pmt>, <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. |
pmt | Number | The amount paid every period, the same each time. |
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. How many periods it takes; seldom a whole number.
Remarks
- 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.
- When the payment does not even cover the interest, no number of periods repays the loan, and the result is an error.
- With a rate of 0 it is −(pv + fv) / pmt.
Examples
How long a loan takes
2,000,000 at 4.5 % a year, repaid 25,000 a month, or 40,000 a month.
EVALUATE
ROW (
"At 25,000", NPER ( 0.045 / 12, -25000, 2000000 ),
"At 40,000", NPER ( 0.045 / 12, -40000, 2000000 )
)| At 25,000 | At 40,000 |
|---|---|
| 95.2915446032076 | 55.4742521906629 |