<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Posted on
6,870 Points
870 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

Exploring SharePoint 2016

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.

Join & Write a Comment

This article covers the things you should know about Email backup in Office 365. Backing up emails is the most crucial part that generally neglected by users.
In this article, we have come up with a step by step procedure to migrate Office 365 data to Zoho Mail using SysTools Office 365 Backup and Zoho Mail Migration Wizard.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month