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

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

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Join & Write a Comment

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
Do you know how to convert CSV to multiple VCF formats? Are you searching for a hassle-free solution to import .csv file contacts into .vcf files? Get an optimum solution here to export Excel or CSV files to vCard or VCF formats! Read more to know t…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month