Free Excel templates

A free credit scorecard model in Excel, built from the counts up

Bins you can move, goods and bads counted from 8,400 applications, weight of evidence and information value for seven characteristics, the points scale from PDO and anchor odds, and the variable that leaks: eight sheets, no macros.

The file builds a small-business credit scorecard from 8,400 applications: 6,000 for development with 379 bads (6.3%), and 2,400 out of time with a bad rate twice as high. Seven characteristics are binned and counted; the strongest, the worst county court judgment, carries an information value of 0.247, and debt service cover 0.200. The scale puts 600 points at odds of 50 to 1 with 20 points to double the odds, so the base score is 565. Days past due at month three would score an information value of 2.86, eleven times the best real characteristic, and it is not on the card because nobody knows it on the day of the decision.

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.

Credit_Scorecard_Model.xlsx (288 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: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

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

Read me
What the workbook builds, why the logistic coefficients are inputs, and the experiments to run.
Inputs
The points scale (PDO, anchor score, anchor odds, factor, offset), the logistic coefficients, and the unit economics of one loan.
Bins
The bin boundaries for each characteristic, with a test cell that shows which bin a value falls in.
Card
Goods, bads, bad rate, shares, weight of evidence and information value for every bin of seven characteristics.
Points
The points grid: coefficient times weight of evidence times factor, with the sign that makes good bins score up.
Leakage
The same arithmetic on days past due at month three, and why a variable that strong does not belong on an application card.
Data
The 8,400 applications, one row each, with the outcome and the bin of every characteristic.
Checks
Every figure the book prints against the live formula: 35 of 35 pass.

What the worked case returns

MeasureValue
Development cases and bads6,000 and 379 (6.32%)
Out-of-time cases and bads2,400 and 303 (12.63%)
Points to double the odds20
Anchor600 points at 50:1
Factor and offset28.85 and 487.12
Base score565
Strongest IV: worst CCJ0.247
IV: debt service cover0.200
IV of the leaking field2.861 (11.6x)
Points, worst CCJ under 12 months-35.95

Weight of evidence is the log of the share of goods over the share of bads in a bin; information value sums the gap between those shares times the weight. Both are counts and logs, which is why the whole card can be built in a spreadsheet. The one thing a spreadsheet cannot do is fit the logistic regression, so the coefficients are inputs, and the file says so.

How to use it on your own portfolio

  1. Replace the Data sheet with your own applications: one row per case, the outcome and each characteristic.
  2. Move the bin boundaries on Bins until each bin has enough bads and the bad rate is monotone where it should be.
  3. Read information value on Card to keep or drop characteristics.
  4. Fit the logistic regression outside Excel and enter the coefficients on Inputs.
  5. Set your own PDO, anchor score and odds, and read the points grid.

Questions people ask about it

Is this credit scorecard model 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. Every count is a COUNTIFS over the data; the coefficients are typed because a logistic regression cannot be fitted inside a spreadsheet.

How are scorecard points calculated?

Points equal minus the coefficient times the weight of evidence times the factor, where the factor is PDO divided by the natural log of two. The offset sets the anchor: here 600 points at odds of 50 to 1.

Can I use it on real applications?

You can reuse the structure. The lender and the 8,400 applications are synthetic, and nothing here is credit or regulatory advice.

The rest of the files

This template is one of the four companion workbooks for Machine Learning for Finance: the others measure discrimination against calibration, population stability and the applicants the model never saw, 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.