Building Probability Models in Excel Part 6: Investment Modeling Using a Log-Normal Distritribution
Posted on
6,838 Points
5:23
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 Comments
Suggested Videos
| Title | Views | Activity |
|---|---|---|
| Installing and Using Excel Add-Ins | 8 | |
| Using Excel's Lambda Function | 13 | |
| Using Python Inside Excel | 18 | |
| How to use the new SCAN function in Excel | 9 |
This article showcases how to fix basic cloud storage and sync errors in OneDrive for Business on primarily Windows OS workstations, but some of the tips and troubleshooting steps could be easily applied to OneDrive - Personal usage (non-work relate…
- Windows 10
- Storage
- Microsoft Office
- Microsoft 365
- OneDrive
- Windows OS, File Sharing Software, *Syncing
Excel formula name translator: Spanish - English and English - Spanish and work in both languages
