Assignment Task
Your assignment is to develop an Excel model which takes the parameters of a normal distribution of the logarithm of asset returns and turn it into the distribution of actual returns. From classroom slides:
Your model will use Excel’s built-in functions to produce the bronze bell curve. In class, we assumed that the bronze curve was standard normal, but in your model, the mean will reflect the average return (actually, 1 + ????), based on input into the model. Hence the probability density assigned to 1 + ???? will equal the density associated with 0 were the distribution standard normal.
Variance is a little tricky, but for this assignment, assume the following: Let’s say the inputs to your model are ???? = 10%, sd = 20%. As per above, the mean of your normal distribution will be the natural log of 1.10 ≈ 0.095. For this model, if the standard deviation is 20%, use 0.2 as the standard deviation of your normal distribution. (Again, IRL it’s tricky.)
Based on your inputs, you should be able to replicate the bronze curve. Then exponentiate the logs of your returns, and assign the corresponding probability density to form the gold curve.
It would be nice to show some diagnostics: What is the average expected return (which is NOT the anti-log of the average log-return!). Standard deviation?
Think about how you might interrogate the model. What if one were to ask what is the likelihood of losing money? What about a 95% confidence interval?
