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

Posted on
6,765 Points
Last Modified:
Experience Level: Beginner
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

Author:Toby Reaper
Enroll in these five web development courses: RESTful Services: Data, JAX-RS and Jersey, RESTful Services: Fundamentals, Selenium Fundamentals, Bootstrap 3.1: Fundamentals, and Perl Fundamentals. Then learn to perform data analysis in Microsoft Exce…
Learn about Power Excel add-ons and how to use them. Power Pivot. Get and transform (Power Query).Power View.3D Map (Power Map).Extra Power BI.Power summary video (Spanish)

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month