<

Go Premium for a chance to win a PS4. Enter to Win

x

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month