solving for NPer w/o function (algebra question)

[Karl]

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.

 

[Don Guillett]

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

 

[Karl]

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?

 

[Jerry W. Lewis]

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]

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.

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

 

[packat]

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

 

[Niek Otten]

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Jerry W. Lewis]

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]

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]

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

 

[Karl]

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

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?
Pmt*(1+Rate*Type)/rate - FV
Pmt*(1+Rate*Type)/Rate - FV


Thanks for sticking with me on this.

Karl

 

[hrlngrv]

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).