Incorrect result using NPER worksheet function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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]...
 
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
 
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
 
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
 
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
 
can you tell me what NPER STAND FOR? Number of periods? or Number of Periods
for Every Relative value?
 
Back
Top