In finance and banking, it’s important to measure and understand credit risk. This helps track performance, manage downside, and make better decisions. Fortunately, Excel is an excellent way to perform this type of study. One method is vintage analysis. Let’s learn how to do vintage analysis in Microsoft Excel.
How to Do Vintage Analysis in Excel
Vintage analysis, in essence, is a table-format risk matrix. It helps you determine how many clients or customers are past-due on balances for varying periods of time. This layout is easy to structure with Excel, and it can provide plenty of meaningful information at a glance.
To use vintage analysis, you’ll first need to construct the matrix. One great way is to list account opening dates in a single column, like column A. In the next column, list how many accounts were opened in each of the date ranges. For example, if 36 accounts opened in January, type 36 in cell B2 beside January.
In subsequent columns, list time frames since accounts were opened. For example, you can use 6 months, 12 months, 18 months, and so on.
Once you have the matrix built, you can begin building formulas. You’ll need to pull information from your records regarding how many accounts have past-due balances.
For example, considering the January example again, imagine that 2 accounts are past-due after 6 months. In cell C2, input the following formula:
=2/B2
On the Home tab, format the cell to Percent Style. Now, think of what this is telling you. After 6 months, 5.56% of accounts opened in January are past-due. From here, you can repeat the same vintage analysis steps, calculating the percentage of past-due accounts by referencing formulas back to the total number of accounts.
As you can see, it’s easy to perform vintage analysis in Microsoft Excel.
Calculate USL and LSL in Excel