A simple stock model

Financial Modeling in Google Sheets

Erin Buchanan

Professor

Types of stock return

Beginning simulation

  • Expected return: $\mu$ - rate of return in the population
  • Expected return: k - rate of return based on a finite sample
Financial Modeling in Google Sheets

Realistic returns (k)

Beginning simulation

  • K $= \mu - \frac{Volatility^2} {2}$
Financial Modeling in Google Sheets

Simulate random normal data

Random normal data

  • normsinv() function: takes a probability and returns a z-score
  • rand() function: creates a random score between 0 and 1
Financial Modeling in Google Sheets

Day 1 estimates

Stock estimates day 1

Financial Modeling in Google Sheets

All other days are random

Stock price randomized

$ = Previous Day Stock \times e^{k \times \frac{1}{252} + Volatility \times rand \times \sqrt{\frac{1}{252}}}$

Financial Modeling in Google Sheets

Certainty is not random

Create random values

Financial Modeling in Google Sheets

Graph your simulation

Create simulation graph

Financial Modeling in Google Sheets

Explore the model

Example simulation

Second simulation

Financial Modeling in Google Sheets

Let's practice!

Financial Modeling in Google Sheets

Preparing Video For Download...