Hope this is the correct place to post!
Semi-excel related re formulas, but also more general knowledge/math related - and hoping there are some resident math experts here! (seeing as everyone is an excel guru, i am sure most are!)
I am trying to work out the monthly standard deviation/volatility of a portfolio to compare it relatively against a benchmark i.e. to see how much more or less volatile the client portfolio is compared to the benchmark for their returns.
I did my calculations but am having a debate with a couple of people as to what figures are used to keep things consistent and in line with standard financial practise (e.g. fund manager on their portfolio returns etc)
So could I please ask for any expertise and feedback?
Please see spreadsheet - https://www.dropbox.com/s/a9bm8ahi4y5v748/SDV Test Sheet.xls
(hope link works!)
They are hypothetical numbers. I have done 4 calculations. My feeling is that I use either calculation 1 or 2 (and that 3 and 4 are incorrect).
I do have daily data if needed…
A couple secondary questions once I know which is the best way (on what numbers) to calculate the volatility:
1. Is it common practise to annualise the monthly standard deviation? From my experience it is… but others are saying just to show the straight figure?
2. Do/should you measure the volatility over all the months from the clients portfolio inception, or do it on just the latest e.g. 12 months, rolling, to keep it relevant?
2. When annualising (I admit my knowledge in this area is limited), say I have 13 months of data, is the formula STDEV([13 monthly values])*SQRT(12), or STDEV([13 monthly values])*SQRT(13) or other?
Thank you so much for any time and help,
Kindest Regards
KJ
Semi-excel related re formulas, but also more general knowledge/math related - and hoping there are some resident math experts here! (seeing as everyone is an excel guru, i am sure most are!)
I am trying to work out the monthly standard deviation/volatility of a portfolio to compare it relatively against a benchmark i.e. to see how much more or less volatile the client portfolio is compared to the benchmark for their returns.
I did my calculations but am having a debate with a couple of people as to what figures are used to keep things consistent and in line with standard financial practise (e.g. fund manager on their portfolio returns etc)
So could I please ask for any expertise and feedback?
Please see spreadsheet - https://www.dropbox.com/s/a9bm8ahi4y5v748/SDV Test Sheet.xls
(hope link works!)
They are hypothetical numbers. I have done 4 calculations. My feeling is that I use either calculation 1 or 2 (and that 3 and 4 are incorrect).
I do have daily data if needed…
A couple secondary questions once I know which is the best way (on what numbers) to calculate the volatility:
1. Is it common practise to annualise the monthly standard deviation? From my experience it is… but others are saying just to show the straight figure?
2. Do/should you measure the volatility over all the months from the clients portfolio inception, or do it on just the latest e.g. 12 months, rolling, to keep it relevant?
2. When annualising (I admit my knowledge in this area is limited), say I have 13 months of data, is the formula STDEV([13 monthly values])*SQRT(12), or STDEV([13 monthly values])*SQRT(13) or other?
Thank you so much for any time and help,
Kindest Regards
KJ
