Standard Deviation Q

M

mwvt9

New member
Joined
Apr 14, 2007
Messages
0
Reaction score
0
I have 10 years worth of data on a portfolio. I have calculated the mean and standard deviation of the portfolio. Assuming a normal distribution (which I know is a stretch), how would I calculate the range of possible returns (95%) after five year and 10 years?

For example:

If I know the mean is 10 and the SD is 15...I could say with ~95 stat reliability that returns would fall within 40 and -20 in any one year (assuming normal). I am trying to figure out what the probability is of cummulative returns after 5 years....

Appoximate Future Returns:

One year Five Year Ten Year
40 ? ?
10 10 10
-20 ? ?


I have done this before...I know I have to take the squre root of the number of years at some point. Any help in much appreciated.
 
Here's how you'd do that in excel.

in year zero (now), put your current portfolio value, put it in cel A1

in each of the following 5 years (and the following 5 cells, A2, A3, A4, etc.) put this formula: =(A1*(1+NORMINV(RAND(),mean,standard deviation))

The 5th cell will be the portfolio value at the end of 5 years. YOu can extend down that formula as far as you want for 10, 20, 30, 50 years, etc.

Everytime you hit F9 it will generate new random returns. Do it 10k times, take the average and you will get an unbelievable answer that is remarkably similar to your 10% annual return.
 
MFE Wrote:
-------------------------------------------------------
> did you test for serial correlation?


I tried that method and an error box popped up:
"Sorry, you smurfed a whalefish"
 
But a whalefish is actually a fish - the cetomimiformes. And they are relatively small at only 15 inches at most. Now, I'm not quite sure how you would smurf one since they live at the bottom of the ocean.

I suggest going with the random number generation ploy 10,000 times. That sounds like the only logical thing to do here. Or try some Monte Carlo simulations on that a$$!!
 
So I looked this up on Wiki and found the following - "Sexual dimorphism is (apparently) exceptionally strong: males may only grow to 3.5 centimetres while females may be ten times as large. This could be said typical of deep-sea fishes, where the males serve little use other than as suppliers of sperm." I showed it to my g/f who promptly called me a whalefish.
 
You must log in or register to reply here.
Back
Top