<

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

x

Building Probability Models in Excel Part 6: Investment Modeling Using a Log-Normal Distritribution

Posted on
6,670 Points
670 Views
Last Modified:
Experience Level: Beginner
5:25
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculate the 5% Value at Risk of the investment from the results.

Video Steps

1. Modeling a Simple Investment: Type in the mean and standard deviation of possible returns

2. Label column A “Return” in cell A5

3. Enter =EXP(NORMINV(RAND(),$B$2,$B$3) into cell A6 and copy down to cell A15

4. Enter 1 into cell B5 to represent $1 invested

5. Enter =B5*A6 into cell B6 and copy down to cell B15

6. Enter =B15 into cell B20

7. Select A20:B519

8. Click Tools > SimTools > Simulation Table

9. Enter 1000 into cell B17 and label “Initial”

10. Enter =$B$17*B21 into cell C21 and copy down to cell C519

11. Enter =PERCENTILE(B20:B519,0.05) into cell B18 and label 5% VaR

0
Comment
Author:Toby Reaper
0 Comments

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

Know the 5 most critical benefits of Microsoft Office 365 Exchange online for business, enterprise, education, and students. Also, be aware of Office 365 Challenges and how to backup data safely.
In this write-up, we have come up with a two-step solution to migrate Office 365 back to Exchange 2019 / 2016 / 2013 / 2010. First move Office 365 to PST, next add PST to Exchange Server.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month