Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I am looking for a formula in Excel to Calculate the standard deviation of NPV (Net Present Value)

    Your assistance will be most appreciated


    Regards

    Howard

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='HowardC' post='774703' date='11-May-2009 14:40']I am looking for a formula in Excel to Calculate the standard deviation of NPV (Net Present Value)

    Your assistance will be most appreciated


    Regards

    Howard[/quote]

    What are you trying to acheive? Standard Deviation looks to a sample and measures how widely values are dispersed from the average value.
    If you have values, I would think first get the NPV of each value using NPV function, then use STDEV or STDEVP on each NPV value to determine its deviation.
    In other words, have Excel first perform NPV computations, take those results as values for a Standard Deviation computation.

    Regards,

    Tom Duthie

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [quote name='duthiet' post='774705' date='11-May-2009 20:05']What are you trying to acheive? Standard Deviation looks to a sample and measures how widely values are dispersed from the average value.
    If you have values, I would think first get the NPV of each value using NPV function, then use STDEV or STDEVP on each NPV value to determine its deviation.
    In other words, have Excel first perform NPV computations, take those results as values for a Standard Deviation computation.

    Regards,

    Tom Duthie[/quote]


    Hi Tom

    I managed to sort out the formula-see attached sample

    Regards

    Howard
    Attached Files Attached Files

  4. #4
    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
    I think the problem is in your question about NPV. The question is not about calculation of a std dev for NPV but how to calculate a weighted standard deviation. The NPV aspect of the question is immaterial.

    Your formula can be simplified by using:
    =SQRT(SUMPRODUCT(B2:B6,(C2:C6-D9)^2))

    and not requiring each individual entry.

    Note: You could get rid of the intermediate calcs in D2: D6. You can calculated the weighted average directly in D9 as:
    =SUMPRODUCT(B2:B6,C2:C6)


    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
  •