# Thread: NPV Question (Excel 2000)

1. ## NPV Question (Excel 2000)

The attached excel file is the actual one that I want to solve. I want to set C53 (NPV) = 0 and I am looking for the answer on D10. I remember there is a function in Excel that can solve such problems, but I just cannot remember it. Would you please tell me how?

Thanks

2. ## Re: NPV Question (Excel 2000)

Easiest way is:
Tools - Goal Seek
Set cell: C53
To value: 0
By changing cell: D10

You could also solve it yourself with some algebra, but that would take a while to figure out what all your calcs do and then solve for D10 in terms of everything else.

Steve

3. ## Re: NPV Question (Excel 2000)

The alternative I alluded to in my first response is to solve it algebraically.

=NPV(D8,F51:I51)+E51

This can be written out (algebraically) as:
Target=(+F51/(1+D8)+G51/(1+D8)^2+H51/(1+D8)^3+I51/(1+D8)^4)+E51

where Target is your "target value". Your question stated that you wanted it to be zero, but I made it a variable to allow setting other values if desired in the future. It also allows checking on results later.

Now you just need to rearrange the equation to put cell D10 in terms of all the other items.

The "simplest/base" cells I used were the cells at the top of the spreadsheet that contained numbers (ie NO formulas)

Cell E51 is equivalent to:
=SUM(\$H\$2:\$H\$4)

Cell F51(through some manipulation) is equivalent to:
=+\$D\$9*\$D\$10*(1-\$D\$7)-\$D\$9*\$D\$11*(1-\$D\$7)+\$D\$1*\$D\$7*C16

Though this is NOT the simplest form, it is a perfect form for further use since it is linear in the desired variable D10. For simplicity in the rearranging/grouping I converted it to:
=A*D10 + I
Where A is =+\$D\$9*(1-\$D\$7)
and I is =-\$D\$9*\$D\$11*(1-\$D\$7)+\$D\$1*\$D\$7*C16

Cell G51 is nearly identical to F51 and is equivalent to:
=A*D10 + J
Where J is =-\$D\$9*\$D\$11*(1-\$D\$7)+\$D\$1*\$D\$7*C17

[Yes, I could have made a couple of other variables and reduced the EXCEL "repeat calcs", but for manipulation of the algebra equations, less variables was the better way]

Cell H51 is also nearly identical to F51 and is equivalent to:
=A*D10 + K
Where K is =-\$D\$9*\$D\$11*(1-\$D\$7)+\$D\$1*\$D\$7*C18

Cell I51 is the most different. It adds some additional factors that none of the others had. It is equivalent to:
=A*D10 + L
Where L is =-\$D\$9*\$D\$10*(1-\$D\$7)-\$D\$9*\$D\$11*(1-\$D\$7)+\$D\$1*\$D\$7*C19+\$D\$4*(1-\$D\$7)+\$D\$2

Once you plug all these values into the original formula and rearrange, it can be shown that D10 is:
=((Target-SUM(\$H\$2:\$H\$4))*(1+\$D\$8)^4-(I*(1+\$D\$8)^3+J*(1+\$D\$8)^2+K*(1+\$D\$8)+L))/ A / ((1+\$D\$8)^3+(1+\$D\$8)^2+(1+\$D\$8)+1)

So if you create the range names for A, I, J, K, L and enter in the above formulas. Then you create a range named TARGET and enter in the value 0. Finally enter in the D10 formula from above into D10.

Now your C53 calc will show zero and the D10 will display \$166 (\$165.70 with the pennies).

Also if you change the value of TARGET, the value in D10 will change and your current calc of C53 will equal to the target value that you enter.

HTH,
Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•