Living with uncertainty

Financial Modeling in Google Sheets

Erin Buchanan

Professor

Defining uncertainty

  • Stocks: share in a company

  • Modeling stock prices can be difficult because they are volatile and can change rapidly

  • Volatility is a measure of the variability in a stock across time

Financial Modeling in Google Sheets

Model Apple's volatility

A model of Apple's volatility

Financial Modeling in Google Sheets

Relative price and daily return

Relative price and daily return

  • Relative price = First Day / Previous Day

  • Natural log function: = ln(CELL)

Financial Modeling in Google Sheets

No #DIV/0!

Error divide by zero

Financial Modeling in Google Sheets

Days of data

Count function

  • =count(CELL REFERENCES)
Financial Modeling in Google Sheets

Descriptive statistics for Apple

Mean and standard deviation

  • Mean: =average(CELL REFERENCES)

  • Standard deviation: =stdev(CELL REFERENCES)

Financial Modeling in Google Sheets

Annualize volatility

Volatility score

  • Volatility: =Daily Standard Deviation / sqrt(Time)
Financial Modeling in Google Sheets

Apple's volatility uncovered

Volatility final

Financial Modeling in Google Sheets

Let's practice!

Financial Modeling in Google Sheets

Preparing Video For Download...