Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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:
    =((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
  •