This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper - 1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer" (term)


Thanks.

Try the number of periods for nper such as 1, 2, 12, 365

--
Don Guillett
SalesAid Software

"Karl" wrote in message
...
This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper - 1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer" (term)


Thanks.

Try the number of periods for nper such as 1, 2, 12, 365

Sorry, I don't understand your reply. The question is, what's the algebraic
equation to solve for NPer?



"Don Guillett" wrote in message
...
Try the number of periods for nper such as 1, 2, 12, 365

--
Don Guillett
SalesAid Software

"Karl" wrote in message
...
This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper - 1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer" (term)


Thanks.

There is not a closed form solution for nper. It has to be solved
numerically. Don Guillett suggested trial and error, but there are more
efficient numeric approaches, I imagine that Excel's NPER() function
uses Newton's method.

Jerry

Karl wrote:

This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper - 1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer" (term)


Thanks.

Hi,

Thanks for the reply, but I don't think what you say is accurate. Here's a
formula for solving for Nper

NPER = log( 1 - ( PV ) * RATE / PMT ) / log( v )

where V = 1 / ( 1 + RATE )


There's just one problem (for me) with the above. Notice it does not take
into account FV. So, assuming a PV of 10,000, monthly deposits of $1,000,
how many periods will it take to reach say $20,000 (FV)?

I think, someone should be able to rewrite the formula documented in the
help for the PV() function (included below) so that one can solve for Nper.

It has to be solved numerically.

Does this mean by a process of iteration? If so, I think you are thinking of
solving for the rate.





"Jerry W. Lewis" wrote in message
...
There is not a closed form solution for nper. It has to be solved
numerically. Don Guillett suggested trial and error, but there are more
efficient numeric approaches, I imagine that Excel's NPER() function
uses Newton's method.

Jerry

Karl wrote:

This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper - 1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer" (term)


Thanks.

PV*(1+Rate)^Nper + (Pmt*(1+Rate*Type)*(1+Rate)^Nper/Rate =
Pmt*(1+Rate*Type)/rate - FV

(1+Rate)^Nper (PV + Pmt(1+Rate*Type))/Rate =
Pmt*(1+Rate*Type)/Rate - FV

(1+Rate)^Nper = (Pmt*(1+Rate*Type) -FV*Rate) / (PV +
Pmt*(1+Rate*Type))


Nper = ( log(Pmt*(1+Rate*Type) -FV*Rate) - log(PV +
Pmt*(1+Rate*Type) ) / ln(1+Rate)


Hope I didn't misplace some of the brackets and
misinterpreted your formula.
pac




Karl wrote:
This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper -
1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer"
(term)


Thanks.

You could simulate that using Goal Seek in the Tools menu.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Jerry W. Lewis" wrote in message
...
There is not a closed form solution for nper. It has to be solved
numerically. Don Guillett suggested trial and error, but there are more
efficient numeric approaches, I imagine that Excel's NPER() function uses
Newton's method.

Jerry

Karl wrote:

This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper - 1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer" (term)


Thanks.

Sorry, I was thinking of rate, for which my statement is true. All
others do have a closed form solution. See general solution by packat

Jerry

Karl wrote:

Hi,

Thanks for the reply, but I don't think what you say is accurate. Here's a
formula for solving for Nper

NPER = log( 1 - ( PV ) * RATE / PMT ) / log( v )

where V = 1 / ( 1 + RATE )


There's just one problem (for me) with the above. Notice it does not take
into account FV. So, assuming a PV of 10,000, monthly deposits of $1,000,
how many periods will it take to reach say $20,000 (FV)?

I think, someone should be able to rewrite the formula documented in the
help for the PV() function (included below) so that one can solve for Nper.


It has to be solved numerically.


Does this mean by a process of iteration? If so, I think you are thinking of
solving for the rate.





"Jerry W. Lewis" wrote in message
...

There is not a closed form solution for nper. It has to be solved
numerically. Don Guillett suggested trial and error, but there are more
efficient numeric approaches, I imagine that Excel's NPER() function
uses Newton's method.

Jerry

Karl wrote:


This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper - 1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer" (term)


Thanks.

Thanks Pac,

However, plugging in values, I can't get your equation to match the results
from Excel.

I'm using these values:

periodic rate= 0.008333333 ( which is 10% / 12 )
pmt= -100
pv= 100000
fv= -109,214.75
type= 0

When you plug the above into Excel's NPer() function, you get 12.

If I understand your equation, when I plug the same values in, the result I
get is -251.96580....

I did notice that the initial parenthesis is unmatched.

Do you see anything that you might want to change?

Thanks.










"packat" wrote in message
news:O%sAd.5721$Y57.2296@trnddc08...
PV*(1+Rate)^Nper + (Pmt*(1+Rate*Type)*(1+Rate)^Nper/Rate =
Pmt*(1+Rate*Type)/rate - FV

(1+Rate)^Nper (PV + Pmt(1+Rate*Type))/Rate =
Pmt*(1+Rate*Type)/Rate - FV

(1+Rate)^Nper = (Pmt*(1+Rate*Type) -FV*Rate) / (PV +
Pmt*(1+Rate*Type))


Nper = ( log(Pmt*(1+Rate*Type) -FV*Rate) - log(PV +
Pmt*(1+Rate*Type) ) / ln(1+Rate)


Hope I didn't misplace some of the brackets and
misinterpreted your formula.
pac




Karl wrote:
This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper -
1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for "NPer"
(term)


Thanks.

Karl,
Could you repost the original equation with matching
parentheses?
Thanks,
pac


Karl wrote:
Thanks Pac,

However, plugging in values, I can't get your equation to
match the
results from Excel.

I'm using these values:

periodic rate= 0.008333333 ( which is 10% / 12 )
pmt= -100
pv= 100000
fv= -109,214.75
type= 0

When you plug the above into Excel's NPer() function, you
get 12.

If I understand your equation, when I plug the same
values in, the
result I get is -251.96580....

I did notice that the initial parenthesis is unmatched.

Do you see anything that you might want to change?

Thanks.










"packat" wrote in message
news:O%sAd.5721$Y57.2296@trnddc08...
PV*(1+Rate)^Nper + (Pmt*(1+Rate*Type)*(1+Rate)^Nper/Rate
=
Pmt*(1+Rate*Type)/rate - FV

(1+Rate)^Nper (PV + Pmt(1+Rate*Type))/Rate =
Pmt*(1+Rate*Type)/Rate - FV

(1+Rate)^Nper = (Pmt*(1+Rate*Type) -FV*Rate) / (PV +
Pmt*(1+Rate*Type))


Nper = ( log(Pmt*(1+Rate*Type) -FV*Rate) - log(PV +
Pmt*(1+Rate*Type) ) / ln(1+Rate)


Hope I didn't misplace some of the brackets and
misinterpreted your formula.
pac




Karl wrote:
This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper -
1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for
"NPer"
(term)


Thanks.

Could you repost the original equation with matching parentheses?

PV * ( 1 + Rate )^Nper + ( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 +
Rate )^Nper - 1 ) / Rate ) + FV = 0

If I plug

periodic rate= 0.008333333
pmt= -100
pv= 100000
fv= -109,214.75
type= 0

Into the above equation, the result is 0.

I started checking each of the steps you provided. Notice that the right
side of the equation does not change between step 1 and step 2. Maybe
therein lies the problem?

PV*(1+Rate)^Nper + (Pmt*(1+Rate*Type)*(1+Rate)^Nper/Rate =
Pmt*(1+Rate*Type)/rate - FV

(1+Rate)^Nper (PV + Pmt(1+Rate*Type))/Rate =
Pmt*(1+Rate*Type)/Rate - FV


Thanks for sticking with me on this.

Karl







"packat" wrote in message
news:fZBAd.19755$_62.11167@trnddc01...
Karl,
Could you repost the original equation with matching
parentheses?
Thanks,
pac


Karl wrote:
Thanks Pac,

However, plugging in values, I can't get your equation to
match the
results from Excel.

I'm using these values:

periodic rate= 0.008333333 ( which is 10% / 12 )
pmt= -100
pv= 100000
fv= -109,214.75
type= 0

When you plug the above into Excel's NPer() function, you
get 12.

If I understand your equation, when I plug the same
values in, the
result I get is -251.96580....

I did notice that the initial parenthesis is unmatched.

Do you see anything that you might want to change?

Thanks.










"packat" wrote in message
news:O%sAd.5721$Y57.2296@trnddc08...
PV*(1+Rate)^Nper + (Pmt*(1+Rate*Type)*(1+Rate)^Nper/Rate
=
Pmt*(1+Rate*Type)/rate - FV

(1+Rate)^Nper (PV + Pmt(1+Rate*Type))/Rate =
Pmt*(1+Rate*Type)/Rate - FV

(1+Rate)^Nper = (Pmt*(1+Rate*Type) -FV*Rate) / (PV +
Pmt*(1+Rate*Type))


Nper = ( log(Pmt*(1+Rate*Type) -FV*Rate) - log(PV +
Pmt*(1+Rate*Type) ) / ln(1+Rate)


Hope I didn't misplace some of the brackets and
misinterpreted your formula.
pac




Karl wrote:
This is the financial function in Excel's PV Help topic:


PV * ( 1 + Rate )^Nper +

( Pmt * ( 1 + ( Rate * Type ) ) * ( ( 1 + Rate )^Nper -
1 ) / Rate ) +

FV = 0



Can someone please rewrite this for me to solve for
"NPer"
(term)


Thanks.

Karl wrote...
....
However, plugging in values, I can't get your equation to match the
results
from Excel.

I'm using these values:

periodic rate= 0.008333333 ( which is 10% / 12 )
pmt= -100
pv= 100000
fv= -109,214.75
type= 0

When you plug the above into Excel's NPer() function, you get 12.

If I understand your equation, when I plug the same values in, the
result I
get is -251.96580....
....

Your original equation:

PV*(1+Rate)^Nper + Pmt*(1+(Rate*Type))*((1+Rate)^Nper-1)/Rate + FV = 0

rearranges as

FV = Pmt*(1+(Rate*Type))*(1-(1+Rate)^Nper)/Rate - PV*(1+Rate)^Nper

Since type = 0, this reduces to

FV = Pmt*(1-(1+Rate)^Nper)/Rate - PV*(1+Rate)^Nper
= Pmt/Rate - (Pmt/Rate + PV)*(1+Rate)^Nper

Pmt/Rate - FV = (Pmt/Rate + PV)*(1+Rate)^Nper

Log(Pmt/Rate - FV) = Log((Pmt/Rate + PV)*(1+Rate)^Nper)
= Log(Pmt/Rate + PV) + Nper * Log(1+Rate)

So

Nper = (Log(Pmt/Rate - FV) - Log(Pmt/Rate + PV)) / Log(1+Rate)
= Log((Pmt/Rate - FV) / (Pmt/Rate + PV)) / Log(1+Rate)

And the formula

=LOG((-100/(10%/12)+109214.75)/(-100/(10%/12)+100000))/LOG(1+10%/12)

does return 12.00000008, just like
NPER(10%/12,-100,100000,-109214.75,0).