Incorrect result using NPER worksheet function
 

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


"KG Old Wolf" <KG Old wrote in
message
...
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?

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

"Chip Pearson" wrote:

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


"KG Old Wolf" <KG Old wrote in
message
...
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?

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


"KG Old Wolf" wrote in message
...
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

"Chip Pearson" wrote:

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


"KG Old Wolf" <KG Old wrote in
message
...
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?

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

"sd" wrote:

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


"KG Old Wolf" wrote in message
...
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

"Chip Pearson" wrote:

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


"KG Old Wolf" <KG Old wrote in
message
...
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?

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

KG Old Wolf wrote:

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

"sd" wrote:


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


"KG Old Wolf" wrote in message
...

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

"Chip Pearson" wrote:


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


"KG Old Wolf" <KG Old wrote in
message
...

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?

Jerry W. Lewis wrote:

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


that should read months, not years; sorry

Jerry

Incorrect result using NPER worksheet function
 

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

"KG Old Wolf" wrote:

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

"Chip Pearson" wrote:

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


"KG Old Wolf" <KG Old wrote in
message
...
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?