# What's wrong with this asset growth simulation?

Sorry if this is too basic, but I have this spreadsheet that simulates asset growth of a portfolio under a given return and risk using MPT.

Here is a plot of probability distribution of asset growth derived from it (return = 0.05, risk = 0.2). It shows that the variance becomes larger as you hold the asset longer.

This however, goes against the conventional wisdom that variance becomes smaller as you hold the portfolio longer. The simulation also shows that with return=0.05, risk=0.2, the most likely scenario (Mode) is that your asset will be at 100% on first year, 98% on fifth year and 95% on tenth year; i.e. the growth is negative. This also seems wrong.

However, I can't see what's wrong with the method. Here is what it's doing:

1) Derive mean asset growth as follows:
μ = LN(m)-LN((s/m)^2+1)/2
where m is the expected return and s is the expected risk of the portfolio

2) Derive standard deviation of asset growth as follows:
σ = SQRT(LN((s/m)^2+1))

3) Derive asset growth distribution as follows:
y = 1/x*NORMDIST(LN(x),μ*n,σ * SQRT(n),FALSE)
where y is frequency, x is asset growth, n is year


Now when you plot (x,y) for year n, you get the aforementioned chart.

My questions are,

a) Is the method correct according to MPT?
b) Why does it differ from how actual asset growth behaves?

• You are welcome! As I added in my answer, an important point is that the lognormal distribution is not symmetrical. Mode is $\exp(m - s^2/2)$. What are your sources for “time diversification”? Oct 15, 2013 at 5:19