simulate values for a given Mean and Standard deviation

[Myl]

I want to simulate values for a given mean and standard deviation. wat
is the procedure to do this? is there any free excels addins available
to do this.
For example, My portfolio return(mean) is 12% and Risk(SD) is 18% . Now
I want to simulate values for next 30-40 years Returns. How to do this?
is there any free add-ins that wud generate these yearly returns if we
provide the population mean and standard deviation.

 

[robert111]

Yes there is under tools, data analysis, random number generation


Lots of different distributions available

You might need to install data add ins

Also beware if you are generating hundreds of thousands of variables,
you get repetition.

 

[robert111]

further to my previous reply yes you can predict future numbers, but for
a 40 year future prediction you are likely to get a very uncertain
answer, eg if you make 12% on average now, but in the past it has
varied from -6% to +30%, then you COULD make -4%,3% and 0% in the next
3 years

Or you could make 9%, 21%, 24% in the next 3 years. Starting with
$1000, at the start of year 4 you could have $989 or $1635

I believe you will end up with an answer something like, for $1000
investment, in 40 years you will have between $15000 and $210,000, ie a
mean of $112,500

The nature of random (normal) variation means that you don't
NECESSARILY get one good year for every bad year you have....

 

[Mike Middleton]

Myl -

If you want random values from the normal distribution, using dynamic
worksheet functions, you could try

=NORMINV(RAND(),Mean,StDev)

The numerical accuracy of both NORMINV and RAND are much improved in Excel
2003 (and NORMINV is much slower).

- Mike
www.mikemiddleton.com