Probability Audit (free Excel tool)

Contents

  • The motivation
  • The model
  • The mathematics
  • Link to the tool

Motivation

  • I am a credit risk manager. At the start of the year, I have assessed the probabilities of my debtors defaulting in the course of the year (which I am rather naively assuming to be independent). At the end of the year, I have a list of those who have defaulted and those who have not and I would like to evaluate how good my probability assessments were, on average.
  • I am a portfolio manager in an oil and gas exploration company. At the start of the year, my subsurface teams have assessed the probabilities that the exploration wells I drill during the year will discover oil. At the end of the year, I have a list of those that discovered oil and those that did not and I would like to evaluate how good my probability assessments were, on average.
  • I am testing myself at assessing probabilities, so I have taken a calibration test where I have answered a list of questions and provided a probability that my answer to that question is correct. I would like to see whether, on average, I over- or under-estimate probability

The model

We have a sequence of probabilities, each of which describes our belief in the likelihood that a given event will take place (default, discovery, etc.). The events are binary, so they either happen or they don’t.

  1. You can hit it with a Monte Carlo sledge hammer
  2. You can calculate the probabilities directly
  3. You can use the Central limit theorem and approximate the distribution with a Normal distribution.

Direct calculation

We call the outcomes that we count successes . This makes sense for oil discoveries, but is frankly a little odd for credit defaults. Nonetheless.

Central limit theorem

If we think of each event as a random variable that takes a value 1 or 0 then the count is just the number we get adding all these random variables together.

The math

The tool

You can find a copy of the spreadsheet tool here.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Graeme Keith

Graeme Keith

805 Followers

Mathematical modelling for business and the business of mathematical modelling. See stochastic.dk/articles for a categorized list of all my articles on medium.