<

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

Posted on
6,808 Points
810 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
Comment
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

In the blog post, we are going to discuss step by step procedures to set up an archive and deletion policy for Office 365 mailboxes.
Now Export Exchange 2010/07/03 Mailbox to Office 365 directly with Systools Exchange Recovery Tool. Repair Corrupt or Damaged Exchange Mailbox and move Database to O365 account to Mails, Contacts, Calendar, Tasks and Journal details.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month