Calculating NPER

When solving for NPER, I've had a couple of occasions where Excel
returns #NUM!

But if I changed the PMT amount (not changing the +/- sign, just the
amount), I found that the error only occurred at a certain value and
lower.
If the other variables are different, this threshold would be
different.
But above the threshold, N (or NPER in Excel) solves just fine.

I show two examples below. ***If you drop the value of
the PMT by just one, the #NUM! error is returned.***

Is there a fundamental that I didn't learn; or is there some other
idiosyncrasy?

Thanks
RICK

rate 7% 8%
pmt 12658 88578
pv -193484.50 -1269616.52
fv 0 0
BEG/END 1 1
solve nper 169 188

Hello:

The data needs to make economic sense. The 7% example works
just barely with payment you gave. Lower that payment by $1
and does not make economic sense. The 8% example makes no
economic sense with number you supplied.

Since we are talking about numbers raised to nper power the
solution is very sensitive to small changes.

The easiest way to understand the problem is to
look at your data. The payment of 12,658 just pays the interest
on the loan and makes a very small contribution to principal. As long
as that contribution is positive the loan can be repaid.

Per Bal BOP Pay i Prin Bal EOP
0 193484.50 12658.00 0.00 12658 180826.50
1 180826.50 12658.00 12657.86 0.14 180826.36
2 180826.36 12658.00 12657.84 0.16 180826.20
3 180826.20 12658.00 12657.83 0.17 180826.03
4 180826.03 12658.00 12657.82 0.18 180825.86
5 180825.86 12658.00 12657.81 0.19 180825.67

If you make that payment $1 smaller (in fact only 14 cents smaller) there
is nothing paid toward principal. So the loan can never be repaid,
as shown below:

Per Bal BOP Pay i Prin Bal EOP
0 193484.50 12657.00 0.00 12657 180827.50
1 180827.50 12657.00 12657.93 -0.93 180828.43
2 180828.43 12657.00 12657.99 -0.99 180829.41
3 180829.41 12657.00 12658.06 -1.06 180830.47
4 180830.47 12657.00 12658.13 -1.13 180831.61
5 180831.61 12657.00 12658.21 -1.21 180832.82

Thus NPER will find impossible in this case to find the number of periods.


Using the 8% interest rate the minimum payment is $94,045.67
and it will take 214 periods to pay it off. If you lower the payment
by $1 it can never be paid off.

So the $88,578 is not large enough. Even if you pay for an infinite length
of time. The #num is simply a way of telling that even with infinite
number of payments the load cannot be repaid. As a matter of fact
after making payments for 200 periods you will owe $330,723,238,673.17
assuming the lender was will to add the deficiency to the loan balance.


Pieter Vandenberg

rick wintomac <[email protected]> wrote:
: When solving for NPER, I've had a couple of occasions where Excel
: returns #NUM!

: But if I changed the PMT amount (not changing the +/- sign, just the
: amount), I found that the error only occurred at a certain value and
: lower.
: If the other variables are different, this threshold would be
: different.
: But above the threshold, N (or NPER in Excel) solves just fine.

: I show two examples below. ***If you drop the value of
: the PMT by just one, the #NUM! error is returned.***

: Is there a fundamental that I didn't learn; or is there some other
: idiosyncrasy?

: Thanks
: RICK

: rate 7% 8%
: pmt 12658 88578
: pv -193484.50 -1269616.52
: fv 0 0
: BEG/END 1 1
: solve nper 169 188

Thanks Pieter.

Actually the example was for a payment received from a presumed amount
saved at the begining of a distribution period.

The principle still applies - the payment is so low that the account
will never run out.

RICK