Hi,
I apologize in advance for using HTML message. I want to be able to
show my sample spreadsheet and have the columns properly aligned.
|
B |
C |
D |
E |
F |
| 2 |
|
|
|
|
|
| 3 |
|
Average |
448,471
|
70,893,875 |
0.7% |
| 4 |
|
std dev |
1,110,330
|
923,382
|
1.6% |
| 5 |
|
Annual volatility |
25% |
21% |
26% |
| 6 |
|
|
|
|
|
| 7 |
Day |
Starting value |
P+L |
Ending value |
Return |
| 8 |
0 |
|
|
68,691,564 |
|
| 9 |
1 |
68,691,564 |
2,846,611 |
71,538,176
|
4.1% |
| 10 |
2 |
71,538,176 |
(36,906) |
71,501,270
|
-0.1% |
| 11 |
3 |
71,501,270
|
(517,171) |
70,984,098
|
-0.7% |
| 12 |
4 |
70,984,098
|
191,329 |
71,175,427
|
0.3% |
| 13 |
5 |
71,175,427
|
(191,224) |
70,984,203
|
-0.3% |
| 14 |
6 |
70,984,203
|
398,186 |
71,382,390
|
0.6% |
My objective is to be able to measure the annual volatility of a
portfolio using only a few days worth of data. As you can see from
above, the annual volatilities from using three different methods do not
agree. I want to better understand why they do not agree and which method
would be the best.
Here is what I have done.
P+L (Profit and Loss)
The average is simply the average of days 1-6.
Standard deviation is simply =STDEVP(D9:D14)...STDEVP for P+L for days
1-6
Annual volatility is =D4*SQRT(256)/E3.... which is standard deviation above
times sqrt of 256 (average trade days per year) divided by the average portfolio
value to arrive at a percentage figure.
Portfolio Ending Value
Average=AVERAGE(E8:E14)...straightforward...average of all ending
values
Std Dev=STDEVP(E8:E14)
Volatility=E4*SQRT(256)/E3...very similar as for P+L above
Return Method
Average=AVERAGE(F9:F14)....very similar to previous two methods
Std Dev=STDEVP(F9:F14)
Volatility=F4*SQRT(256)
I am not a stats expert, so I would appreciate your help in understanding
why do these methods not agree? Which is the best method to use and
why? Or is there a better method that I am not using?
Thank you very much for your help.
Regards,
Kevin
