DEV Community

Cover image for How to calculate accuracy ratio in Excel using only a formula
Wynn Tee for Quantitative Risk Solutions PLT

Posted on • Originally published at addin.qrstoolbox.com

How to calculate accuracy ratio in Excel using only a formula

Risk practitioners often use accuracy ratio (AR) to measure the discriminatory power of binary classification models, such as models of credit default and insurance fraud. The closer AR is to 1, the higher the discriminatory power of the model.

Definition

Cumulative Accuracy Profile

The above diagram shows the cumulative accuracy profiles of a realistic model, a random model, and a perfect model.

As the proportion of observations increases, a perfect model would correctly classify all events before all non-events, but a random model would indiscriminately classify events and non-events together. A realistic model would be somewhere in between.

AR is the ratio of the area between the cumulative accuracy profiles of the realistic and random models (B) to the area between the cumulative accuracy profiles of the perfect and random models (A+B).

Unfortunately, Excel does not come with a native function for calculating AR. It is possible to calculate AR in Excel manually, but the process involves auxiliary rows and columns with complicated formulas that have to be adjusted as observations are added or removed. The problem is exacerbated with the standard error of AR.

QRS.AR

Fortunately, QRS Toolbox for Excel includes the QRS.AR function for calculating AR and the standard error of AR. It is applicable to both grouped and upgrouped data.

To try QRS.AR yourself, add QRS Toolbox to your instance of Excel and start your free trial of QRS.AR. Then, open the example workbook.

Ungrouped data

The workbook contains 2 worksheets. In the UNGROUPED worksheet:

  • Cells A2–A2001 contain credit scores for 2000 borrowers. The scores range between 0 for least creditworthy and 100 for most creditworthy.
  • Cells B2–B2001 contain ones if credit default occurred and zeros otherwise.

Grouped data

In the GROUPED worksheet, cells A2–E8 contain data from the previous worksheet grouped into 7 score ranges, each with an alphabetical rating grade.

Ungrouped data example

=QRS.AR(A2:A2001, B2:B2001, -1)
Enter fullscreen mode Exit fullscreen mode

Formula

To calculate AR of the ungrouped data, open the UNGROUPED worksheet and enter the formula =QRS.AR(A2:A2001, B2:B2001, -1) in cell D1. The result is 0.794, which is generally considered to be a large AR.

Note that the third argument is -1 instead of +1, because the scores are defined such that events of default increase as scores decrease. If the third argument is omitted or set at +1, the result would be negative-valued.

Standard error

=QRS.AR(A2:A2001, B2:B2001, -1, "SE", TRUE)
Enter fullscreen mode Exit fullscreen mode

Formula

To include standard error of AR in the result, add "SE", TRUE to the formula. The result now contains 2 rows, with the standard error in the second row.

Labels and transpose

=QRS.AR(A2:A2001, B2:B2001, -1, "SE", TRUE, "LABELS", TRUE)
Enter fullscreen mode Exit fullscreen mode

Formula

To add labels to the result, add "LABELS", TRUE to the formula. To swap the rows and columns of the result, add "TRANSPOSE", TRUE to the formula.

Grouped data example

=QRS.AR(B2:B8, D2:E8, -1)
Enter fullscreen mode Exit fullscreen mode

Formula

To calculate AR of the grouped data, switch to the GROUPED worksheet and enter the formula =QRS.AR(B2:B8, D2:E8, -1) in cell F9. The result is 0.797.

Note that the second argument to the formula is now an array with two columns instead of one. The first column contains the number of borrowers who did not default. The second column contains the number of borrowers who defaulted.

The SE, LABELS, and TRANSPOSE options can be used as in the ungrouped data example.

Final remarks

If you would like to use QRS.AR beyond your free trial period, you may purchase the right to use it indefinitely for as little as USD 9.00. If you find QRS.AR useful, please share this page with other potential users.

Discussion (0)