Results 1 to 3 of 3
Thread: NPV Question (Excel 2000)

20030218, 20:48 #1
 Join Date
 Mar 2001
 Posts
 309
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030219, 01:04 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: NPV Question (Excel 2000)
Easiest way is:
Tools  Goal Seek
Set cell: C53
To value: 0
By changing cell: D10
The value is about $165.70
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

20030219, 17:35 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: NPV Question (Excel 2000)
The alternative I alluded to in my first response is to solve it algebraically.
Your calculation for C53 is:
=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:
=((TargetSUM($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