<

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

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

Expert Comment

by:Richard Shaw
In the SIM table (A25: A524) -- what is the meaning and use of the column A data?
0

Featured Post

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

A quick guide on adding multiple SMTP proxy addresses to Users for Office 365
Today we are going to make Microsoft clients aware of tricks and tips used by Cybercriminals to bypass Microsoft Office 365 tenants. Read this post carefully to help you achieve an unbreakable security level in your Exchange Online accounts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month