The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a Monte Carlo simulation using the simulated returns.
1. Modeling a Simple Investment: Type in the means and standard deviations of the return of both of the funds, along with the correlation coefficient
2. Select B7:C7 and type =CORAND($B$5), then press Command+Shift+Enter
3. Copy down to row 16
4. Label the returns of fund 1 and fund 2
5. Enter =EXP(NORMINV(B7,$B$2,$B$3)) into cell E7 and copy down to E16
6. Enter =EXP(NORMINV(C7,$C$2,$C$3)) into cell F7 and copy down to F16
7. Label the $1 investment into fund 1 and fund 2
8. Enter =H6*E7 into cell H7 and =I6*F7 into cell I7 then copy both down to row 16
9. Enter =H16 into cell B25 and =I16 into cell C25
10. Select A25:C524 then click ToolsSimToolsSimulation Table
Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.