Building Probability Models in Excel Part 7: Modeling a Correlated Two-Fund Investment

Posted on
7,243 Points
Last Modified:
Experience Level: Beginner
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.

Video Steps

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 ToolsSimToolsSimulation Table

Author:Toby Reaper
1 Comment


by:Richard Shaw
In the SIM table (A25: A524) -- what is the meaning and use of the column A data?
This article series will show you how to utilise the Next Generation Cryptography (CNG) API from Microsoft for modern hashing and encrypting/decrypting in VBA. In this part: Compatible with other environments.
I am not a database expert nor am I conversant with database design, but I have had a couple of run-ins with databases that are either badly designed or use a platform that doesn’t meet the needs of the end-user.  I would like to think this is a "le…