Free Excel templates

A free population stability index (PSI) calculator in Excel

A scorecard scored on 8,400 applications, ten bands fixed on the development sample, the PSI term by term, and the applicants the previous policy declined: ten sheets, no macros.

The file measures how far the population scored by a credit model has moved since the model was built. Scores are cut into ten bands of 600 applications each on the development sample; the out-of-time sample is then counted into the same bands. The population stability index comes to 0.091, under the usual 0.10 line, and a third of it comes from the top band, which empties. The second half of the file shows why that is not the whole story: the 3,230 applicants the previous policy declined had a bad rate of 12.8%, 2.4 times that of the accepted ones.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Population_Stability_Index_Calculator.xlsx (429 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from Machine Learning for Finance. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Ten sheets. Blue figures on a yellow ground are inputs; every count is a formula over the data.

Read me and Inputs
What the workbook measures, the points scale and the coefficients.
Bins, Card and Score
The scorecard that produces the scores, applied to every application.
Bands
The ten score bands, cut on the development sample.
Stability
Development against out-of-time shares in each band, each PSI term and the total, with its reading.
Through the door
The previous policy's rules, accepted against declined, and their bad rates.
Data and Checks
The 8,400 applications, and the book's figures against the formulas.

What the worked case returns

MeasureValue
Development and out-of-time cases6,000 and 2,400
Score bands10 of about 600
Population stability index0.091
Largest single term (band 10)0.029
Accepted by the previous policy5,170 (bad rate 5.2%)
Declined by the previous policy3,230 (bad rate 12.8%)
Declined against accepted bad rate2.44x

The bands must stay where the development sample put them. Recutting them on the new population makes every band hold 10% again and the index falls to zero, which hides exactly the shift it is meant to measure.

How to use it on your own model

  1. Replace the Data sheet with your development and recent applications and their scores.
  2. Keep the band boundaries from development on Bands.
  3. Read the PSI total and the band that contributes most on Stability.
  4. Check what the previous policy declined before trusting a model built on accepted cases only.

Questions people ask about it

Is this template really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference: counts by band and one log term per band.

How is the population stability index calculated?

For each band, the difference between the new share and the development share, times the natural log of their ratio, summed over the bands. Below 0.10 is usually read as no material shift, 0.10 to 0.25 as a shift worth investigating.

Can I use it on my own model?

Yes, by replacing the data. The lender and the applications are synthetic.

The rest of the files

This template is one of the four companion workbooks for Machine Learning for Finance: the others build the scorecard, measure discrimination against calibration, and price the cut-off. Free, like this one.

Open the companion files →

Other free templates

This template comes from Machine Learning for Finance. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.