Hi, How do you calculate the standard deviation for a particular mutual fund and for the BSE/Sensex index?

Skip to content# Standard deviation of Mutual Funds and the BSE/Sensex index?

##
2 Replies to “Standard deviation of Mutual Funds and the BSE/Sensex index?”

### Leave a Reply

Professional Answers on Investments

Insurance, Mutual Funds, Sensex, Nifty, Financial Planning

Hi, How do you calculate the standard deviation for a particular mutual fund and for the BSE/Sensex index?

A better question might be “Why do you care?”…

The SD or %RSD of a stock or a fund price tells you absolutely zero about what it might do tomorrow, it’s like studying past “winning lottery numbers” and expecting to glean some valuable insight from that data!

(Worse yet, if you calculate the standard deviation of a fund that has grown consistently at 10% for 20 years, it will look AWFUL compared with one that has stayed the same price every day over the same period!)

Who gave you the idea that it mattered? Don’t lend them any money!

The standard deviation for a stock, mutual fund, or index is calculated the same way. You simply need a long list of closing prices.

Once you have your list in Excel, find the “price relatives” of each. For example, if day 1 = $100 and day 2 = $101, the price relative is $101/$100 = 1.01. In other words, the “return” on your money in one day was 1% since you could have purchased for $100 and sold for $101.

Then copy/paste that formula all the way down.

Next, take the natural logs of each price relative. You can do this in Excel by typing “=ln( )” where the number in parenthesis is the cell number in Excel. Using the above example, if the price relative is 1.01, the natural log is .00995. That just means if you take the constant e (roughly 2.7183) and raise it to .00995, you’ll get 1.01.

Once you have a list of the natural logs of price relatives, use “stdev ( )” in Excel to calculate the standard deviation. In the case, the number in parenthesis will be the entire array in Excel.

Finally, you just need to annualize the figure. Do this by dividing by the square root of time. For example, if you have six months worth of price relatives, you will divide by square root of (1/2), since 6 months is 1/2 of a year.

That answer is the annualized volatility! It’s a lot of steps by quite easy in Excel. By the way, you can download price history for free at yahoo.com.

Bill Johnson

Options A to Z.com