Matrix Multiplication in Excel — Markov Chains

This post is part of the “Matrix Multiplication in Excel” series. It’s composed of a math introduction, a silly interlude and an interactive tutorial (you are here). By the end of the series, you’ll learn how to perform Markov Chain calculations, which are used in some damage calculations.

Now that we know some of the basics, I’m going to introduce a toy problem where one would use matrix multiplication, step through how to calculate it in Excel and then give an example used forensic economics.

Click here to download Matrix Multiplication.xlsx.

Scenario 1: Ping Pong Problem

Suppose two ping pong players (labelled left and right) are tied near the end of a game. In order to win, one of them has to have a two point lead. Suppose that, on any given volley, the left player has a 55% chance of winning.

The game can be thought of as having five states:

  1. (2,0) – The left player wins.
  2. (1,0) – The left player is winning by one.
  3. (0,0) – The game is tied.
  4. (0,1) – The right player is winning by one.
  5. (0,2) – The right player wins.

Graphically, the transitions between these states can be seen as follows.


The left player increases his score with the probability p(LW), in this case 55%. How do we calculate the probability that the left player wins? Continue reading →

Matrix Multiplication in Excel — What is the Matrix?

This post is part of the “Matrix Multiplication in Excel” series. It’s composed of a math introduction, a silly interlude (you are here) and an interactive tutorial. By the end of the series, you’ll learn how to perform Markov Chain calculations, which are used in some damage calculations.

‪‎Trigger Warnings‬: Authorial Self-Insertion, In Medias Res, Movie References, Fake Trigger Warnings


Just a few days ago, I had been lying on my back, surrounded by doctors, atrophied and struggling to gasp for breath. This man wanted billions of humans to experience the same process. A note of discord rang through my heart as I tried to imagine the whole of humanity simultaneously squinting as they adjusted to their new eyes.

Morpheus misinterpreted my discomfort and didn’t miss a beat. Continue reading →

Matrix Multiplication in Excel — Math Introduction

This post is part of the “Matrix Multiplication in Excel” series. It’s composed of a math introduction (you are here), a silly interlude and an interactive tutorial. By the end of the series, you’ll learn how to perform Markov Chain calculations, which are used in some damage calculations.

A matrix (plural matrices) is a rectangular array of numbers, symbols or expressions arranged in m rows and n columns. For matrix A below, the element at row i and column j is indexed as a_{i,j}.

mmFig1 Continue reading →

Forecasting Ebola Deaths Using Time Series Regressions

Johnny Voltz, an old college friend of mine who was once voted most-likely to have a superhero named after him, send in a great question about the recent, tragic Ebola outbreak in West Africa:

I know very little about math, and even less about medicine. From the data I have, it would suggest that the Ebola virus is growing at a logarithmic pace. Would it be fair to predict continued growth at this rate considering lack of medical care in Africa? Would 100,000 be an exaggerated number for March 2015? What are your thoughts?

He then linked to a chart which showed the logarithm of Ebola Deaths and an Excel fit.


This is a classic time series problem, and I’d like to use it to illustrate the process, merits and accuracy of fitting time trends through regressions. As a final step, we’ll produce an estimate of cumulative Ebola deaths in March 2015. But first, let’s talk about regressions in general. Continue reading →

Superbowl Special: Kelly Criterion

WrG88EmMerry Superbowl Sunday, and a happy offseason.

Like most finance types, I have a life-long fascination with risk and uncertainty. If I had my druthers, I’d move to Vegas and start a sports gambling show co-hosted with Jennifer Lawrence. It’d be called “Jenny and the Bets.” If that’s not possible, I’d get Kelly Clarkson to join me in the “Kelly Clarkson Criterion.”

Suppose you woke up in Vegas today and wanted to bet the Broncos would cover the 2 point spread. Assume you estimate a 55% chance of this happening. How much of your bankroll (or, analogously, your portfolio) should you wager on the bet? The kelly criterion answers questions like these. To help you celebrate this American holiday, I’ve created an excel workbook for this calculation.

The Kelly Rule has three inputs: your bankroll, the payoff odds and your estimated probability of winning. In this case, I’ve assumed a bankroll of $5,000 and that the bet odds are -110. In the gambling world, odds of -110 means that you have to risk $110 for the chance to win $100.

In this example, the Kelly Rule states that you should risk 5.50% ($275) of your portfolio on this one bet.

Kelly 1

Personally, I’m often surprised by the aggressiveness of the Kelly Criterion. If your probability of winning is actually 55%, then you should optimally wager $275. Any less and you leave winnings on the table. Any more and you will be too decimated by the losses.

Here’s another “plus money” example.

One of the prop bets today is picking the Superbowl MVP. If Seahawks Quarterback Russel Wilson is selected, sportsbooks will pay bettors at +300. This means that they will win $300 for every $100 they risked. If you think there’s a 35% chance of Wilson winning the trophy, you should actually bet 13.33% of your portfolio on that outcome.

Kelly 2

A bet of $666 on a Seahawk. As a Niner, that feels appropriate.

Kelly Criterion.xlsx

Grand Theft Autocorrelation

Michael-Counting-Money-16_RGBIn my last post, I discussed the concept of correlation. In my free time since then, I’ve been playing a lot of Grand Theft Auto V. It’s time to merge these noble pursuits.

As you may know, GTA V includes an online stock market that allows players to invest their ill-gotten gains in fictitious companies. Naturally, a Reddit user has created an updating database of Stock Market prices. I play on a 360, so I’ve analyzed the Xbox prices. I’ve developed a strategy that will earn money in the long-run, but first let’s do some learning.

As we previously discussed, correlation is a measure of how well the highs of one series line up with the highs of another series. Autocorrelation is a measure of how well highs of a series line up with the highs of the previous observation of the same series. Continue reading →

What is Correlation?

Besides not being causation, many pedantically smart laymen don’t know what correlation is. I’m here to fix that with a mathematical, an intuitive explanation and a brief philosophical comment.

Intuitive Explanation

Correlation is a quantitative measure of how well the highs line up with the highs and the lows line up with the lows between two arrays. Correlation will always be between -1 and 1, inclusively. A correlation of 1 indicates a linear, positive relationship between two variables. A correlation of zero indicates no correlation. A correlation of negative one indicates a perfect, negative relationship.

Mathematical Definition

This value can be computed in Excel through the CORREL() function, but stepping into the formula helps enhance the understanding. Feel free to skim over this part to the applications and philosophy sections. Mathematically, correlation can be computed as follows: Continue reading →

Dice Rolls

In a Straussian attempt to prove personality is genetic, my little brother texted me the following math question:

So imagine we have a die of n sides. We roll the die until it rolls a 1, the number of times it is rolled is the output. But, after each roll, we give the die one less face. What does the distribution of outcomes look like?

When I come across problems like this, I like to answer the question intuitively before solving for the actual answer. My brother and I both guessed what the distribution looked like. We both thought there’d be a very low chance of n or 1 being returned, and a higher chance of a number in the middle being returned. I thought the mode of the distribution would be lower than him.

Perhaps because probability wasn’t beneficial in the anscestral environment, we were both wrong. The distribution is actually perfectly uniform. Elementary math will show that the probability of rolling the first few numbers is exactly the same:

\frac{1}{n} , \frac{n-1}{n} \frac{1}{n-1} , \frac{n-1}{n} \frac{n-2}{n-1} \frac{1}{n-2}

I tested this empirically and produced the following histogram.


Here’s the R Code that generated that:

##Run Parameters
sides <- 100
runs <- 100000

Simulation <-,nrow=sides,ncol=runs))
for (n in 1:sides){
Simulation[n,] <- floor(runif(runs,1,sides-n+2))

Simulation <- Simulation==1

Outcomes <- vector(mode="integer",length=runs)

for (n in 1:runs){
Outcomes[n] <- which.max(Simulation[,n])


Monday Math Problem #1: Expected Births

My colleague Max Lummis became a father over the weekend, so in order to wrap up the recent series of posts on expected value, here’s a birth-themed, three-part math problem for your Monday afternoon.

I’ll give a hat-tip to the person who introduced me to the problem after posting the solution in a few days. Feel free to reference Wolfram and the rules of expectations.

Suppose there’s parents that can have infinite children who really want a boy. They will have kids until they have a boy. The probability of any given child being a boy is 50 percent.

  1. What is the expected number of boys?
  2. What is the expected number of girls?
  3. What is the expected percentage of the children that will be girls?

The Rules of Expectations

As promised, here are some rules for working with expected values, first in words and then in math. All of these can be verified in excel. I’ll also include a list of things that are not rules for working with expectations. These are all especially relevant in valuation if you have multiple uncertain parameters you are combining through probabilities. These also come into play while making total rows at the bottom of complex schedules. Sometimes they won’t foot, and this post will help you understand why.

The expected value of a constant is a constant.

E[c] = c

The expected value of two random values is equal to the sum of the expected values.

E[X + Y] = E[X] + E[Y]

The expected value of a random variable times a constant is equal to the constant times the expected value of the random variable.

E[cX] = cE[X]

Please note that this doesn’t hold if c is not a constant.

E[XY] \neq E[X]E[Y]

Remember these rules, as you will be tested on them.