ddGini is a function within ddGlobal and is use to calculate the Gini-coefficient, also known as the Gini-index or the Gini-Ratio. The calculation is generally use by statisticians to measure the inequality of income levels. Scorecard developers however abuse this as a measure to determine how strong their scorecards are at differentiating between a good and a bad account.
The way to use ddGini is as follows.
1. Create a frequency of your individual scores, or score-ranges that you would like to calculate a Gini-coefficient on. Column A in the following example.
2. Column B, insert the number of BAD accounts that you have in each of the scores / score ranges depicted in Column A
3. Colum C, contains the number of GOOD accounts that you have in each of the scores / score ranges depicted in Column A.
A | B | C | |
1 | Score | # Bad Accounts | # Good Accounts |
2 | 10 | 30 | 40 |
3 | 20 | 25 | 48 |
4 | 30 | 21 | 57 |
5 | 40 | 17 | 68 |
6 | 50 | 14 | 81 |
7 | 60 | 11 | 97 |
8 | 70 | 9 | 116 |
9 | 80 | 7 | 139 |
10 | 90 | 5 | 166 |
11 | 100 | 4 | 199 |
12 | 110 | 3 | 238 |
13 | 120 | 2 | 285 |
14 | 130 | 1 | 342 |
15 | 140 | 0 | 410 |
16 | 150 | 0 | 492 |
The formula to calculate the Gini-coefficient in the above case is:
=+ddgini(B2:B16,C2:C16)
If you have done everything correctly, you should obtain an answer of 0.815175323. Typically, Gini-coefficients for scorecards is expressed as a percentage, so just convert to a percentage and you have a scorecard with a 81% Gini.
The below code may be copied and use freely, but please make reference to either ddGlobal or to this page when you use it please.
Function DDGini(TheBadRange As Range, TheGoodRange As Range) As Double Dim i, j As Integer Dim TotG, TotB As Double Dim CumG, CumB As Double Dim Pct1G, Pct2G, Pct1B, Pct2B As Double Dim Gini As Double With TheGoodRange For i = 1 To .Rows.Count If IsNumeric(.Cells(i, 1).Value) Then TotG = TotG + .Cells(i, 1).Value Next i End With With TheBadRange For i = 1 To .Rows.Count If IsNumeric(.Cells(i, 1).Value) Then TotB = TotB + .Cells(i, 1).Value Next i End With CumG = TotG CumB = TotB For i = 2 To TheGoodRange.Rows.Count + 1 Pct1G = CumG / TotG Pct1B = CumB / TotB CumG = CumG - TheGoodRange.Cells(i - 1, 1).Value CumB = CumB - TheBadRange.Cells(i - 1, 1).Value Pct2G = CumG / TotG Pct2B = CumB / TotB Gini = Gini + ((Pct1G - Pct2G) * (Pct1G + Pct2G - Pct1B - Pct2B)) Pct1G = Pct2G Pct1B = Pct2B Next i DDGini = Gini End Function
This function has been tested to be accurate by various third parties but if you have any suggestionson how to improve it, or you found any errors in it, please contact me.