I have been given the mean (~11%) and standard deviation (~20%) for annualized ROR for stocks over most of the twentieth century.
I would like to generate an artificial sequence of annualized ROR in Excel. Do I need to use the LOGNORM.INV function, or the NORM.INV function?
As in:
ROR(any given year) = LOGNORM.INV(RAND(),x,sigma)
or
ROR(any give year) = NORM.INV(RAND(),sigma,sigma)
That depends on what you wish to simulate. If you believe that returns have been lognormally distributed use the lognormal; if you believe normally distributed, use normal.
Having said that lognormal is probably better. For one thing, it’s possible with the normal distribution to generate a return of less than -100%. It would be rare as -100% is more than 5 standard deviations below the mean of 11%, but it could happen. You cold of course model this as a complete collapse of the stock market (-100%) rather than ending with negative dollars, but you’d want to take a bit of care.
Note that in Excel LOGNORM.INV(p,m,s) assumes that m is the expected value of log(x) and s is the standard deviation of log(x). The latter is not too important as log(1+r) ~ 1 + r - 0.5 r^2 + … so when r is small (like a rate of return) var[r] ~ var[log®]. However if X is lognormal with expected growth rate of m and var[log(x)] = v, then log(X) is normal with an expected change of m - 0.5v. So if 11% is the expected rate of return the expected change you need to use in the lognormal inverse is 0.11 - 0.5*0.2^2 = 0.09
Rather than Excel. Have you tried th calculator? http://www.firecalc.com
I wouldn’t worry about the details when your starting assumptions are unrealistically high IMO. Much of the 11% return seen in the 20th century was probably a fluke. Also remember that dividend rates were much higher back then than they are today, the inflation rate was higher than today, and the dividend growth rate has dropped considerably.
If you decide to use historical data, a better estimate for the expected real return of equities (sampling 23 countries over the past 116 years) is closer to 5%:
This assume what he want to model is the real return. Another way to do this is to model the equity return above the interest rate (that’s often called the excess return). If we had 11% nominal return with an average nominal interest rate of 6% (a guess), then right now we’d expect a nominal return 5% above the current interest rate.
I’ve left out a lot of information about what I’m trying to do, which is this:
I have an initial nest egg, from which a specific, non-constant series of annual payments is to be made. With the nest egg invested in a preselected allocation of stocks/bonds (and rebalanced annually so as to maintain that allocation), I want to estimate the probability of going bankrupt before all the payments are made. This requires repeated simulations in Excel using random annualized RORs generated from a representative probability distribution function (which sure seems like it ought to be a lognormal distribution, since each annualized ROR is the product of numerous daily RORs). The % of simulations that result in bankruptcy represents the probability I’m trying to estimate.
So a simple average ROR of 5% is not enough information; I need the variability as well, and (the whole point of my OP) I need to understand how to generate plausibly distributed RORs for my simulation.
FWIW, I agree with you that 11% is unlikely going forward; my simulation will be tried for a variety of general market forecasts.
Thanks for the help, but something’s still not working right. If I fill a group of thousands of cells with:
=LOGNORM.INV(RAND(), 0.11-0.5*.2^2, 0.2)
the geometric mean of all of these cells is about 1.094. OTOH, if I fill a group of cells with:
=LOGNORM.INV(RAND(), 0.11, 0.2)
the geometric mean is right around 1.116.
Where am I going wrong? Shouldn’t I be getting a geometric mean of 1.11?