Results 1 to 4 of 4

20090511, 13:40 #1
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 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

20090511, 14:05 #2
 Join Date
 Nov 2002
 Location
 New York, New York, USA
 Posts
 279
 Thanks
 0
 Thanked 20 Times in 20 Posts
[quote name='HowardC' post='774703' date='11May2009 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

20090511, 14:23 #3
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 Posts
[quote name='duthiet' post='774705' date='11May2009 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 formulasee attached sample
Regards
Howard

20090512, 07:44 #4
 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:C6D9)^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