<

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

Posted on
7,266 Points
1,266 Views
Last Modified:
Published
Experience Level: Beginner
6:10
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

0
Author:Toby Reaper
1 Comment

Comment

by:Richard Shaw
In the SIM table (A25: A524) -- what is the meaning and use of the column A data?
0
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.
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: Hashing.