• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Vintage Analysis in Excel

October 11, 2021 by Andrew Childress
How to use vintage analysis in Microsoft Excel

In finance and banking, it’s important to measure and understand credit risk. Let’s learn how to do vintage analysis in Microsoft Excel.

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.

Vintage analysis Excel

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

How to use vintage analysis in Microsoft Excel

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.

How to do vintage analysis in Excel

As you can see, it’s easy to perform vintage analysis in Microsoft Excel.

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Find USL and LSL in ExcelCalculate USL and LSL in Excel
Next Post:How to Calculate Durbin-Watson Statistic in ExcelDurbin watson statistic Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top