Incorrect result using NPER worksheet function

[Guest]

I am using the NPER function to determine the number of periods required to
pay of a loan at constant rate and constant payment. The result is
consistently understated (periods are too few to pay off the loan)

Is there a known problem? Has anyone else used this function?

 

[Chip Pearson]

Post the formula you are working with, the values you are passing
to the formula, the result you get and the result you expect.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[Guest]

Hi Chip!

Rate = 5%, PV = $500,000, Payment = $42,803.74. Using CUMIPMT
=CUMIPMT(Rate/12,12,PV,1,12,0), I get the correct amount of interest paid for
the 12 month loan ($13,644.89).

However using the NPER =ABS(NPER(Rate/12,Pmt,PV)) I get a result of only
11.43 months (instead of 12). While close, the variance worsens with lonnger
terms. In fact, the only way I am able to get a correct NPER calculation
result is when the Rate = 0%!

I appreciate your help. My goal is to develop a model where I can include a
variable in my calculation that will show how much I can shorten a mortgage
by adding a constant payment over and above the contractual amount. That
incremental payment will be applied directly to reducing Principal.

Thanks,
Ken Gorman

 

[sd]

When I change the formula from PV to -PV it gives me the correct result (12
months)
My formula is
=NPER(B2/12,B6,-B3,,0)
where B3 house 500,000

 

[Guest]

Hi,

Making the Loan a negative amount resulted in the correct answer. However,
while correct, I don't understand the logic of it! No need to respond; I
think the NPER function is a bit funky. I can use it as you described.

Thanks,
Ken

 

[Jerry W. Lewis]

Excel's financial functions use sign to indicate direction of money flow.
=NPER(5%/12,42803.74,500000)
is for a problem where you borrow $500,000 initially, and then borrow an
additional $42,803.74 each month. At that rate, the break-even point is
nearly 12 years before the process starts (a mathematically correct, but
practically unreasonable result).
=NPER(5%/12,42803.74,-500000)
is for a problem where you loan $500,000 and receive $42,803.74 monthly
payments.
=NPER(5%/12,-42803.74,500000)
is for a problem where you borrow $500,000 and pay back $42,803.74 each
month.

Jerry

 

[Jerry W. Lewis]

... At that rate, the break-even point is
nearly 12 years before the process starts

that should read months, not years; sorry

Jerry

 

[rkaye]

can you tell me what NPER STAND FOR? Number of periods? or Number of Periods
for Every Relative value?