A couple of years ago, I was approached by one of my former students on whether I knew of any Excel shortcuts for aggregating defaults arising from multiple originations cycles. This is how the student framed the problem:
Assume that we have a portfolio of €100m of assets that is originated in period one (cell B3). We wish to apply a cumulative default rate of 10% over the following 4 periods. Let’s assume 12% of the cumulative default rate in first period following origination, 24% in the second period, 36% in the third period and 28% in the final period (a default vector 12%/24%/36%/28%) – or in dollar terms, defaults of €1.2m, €2.4m, €3.6m and €2.8m spread between periods two to five (see row 8 below).
Now further assume that in period two we originate a second tranche of assets, say €80m (cell C4), and we wish to apply the same default vector (12%/24%/36%/28%). In other words, we wish to see defaults of €0.96m, €1.92m, €2.88m and €2.24m spread between periods three to six (see row 9 below) .
Table 1 - Longhand Solution to Default Vectoring for Multi-Period Origination
And to complicate the situation further, we not only wish to model similar default patterns for further originations cycles in period three (see row 10), but also do the same for periods four through, say, 36 – and to calculate the respective defaults for each tranche accordingly.
Question: is there any way of modelling this behaviour in a single row?
As you can see from Table 1 above, whilst the longhand solution works, it suffers from two problems. First, every time a new cohort or period of originations is added, the user must manually insert a new row for the associated defaults. Furthermore, we must manually change the formula for each new cycle of defaults. Secondly, if the user wishes to program other behaviour such as scheduled repayments or unscheduled prepayments, it will not just be a row that needs to added but a full ledger for each new cohort of originations.
The key to the solving this, is to devise shortcut that avoids opening a new row or ledger for each cohort of originations whilst at the same time maintaining the accuracy of the calculation. Moreover, if we can do this in a single row for unlimited origination cohorts, this will make considerable savings in memory and processing speed.
Differences Between Cumulative Default Rates and CDRs
Before we delve into the solution, it’s worth reminding oneself of the different methods of programming defaults. For those of you familiar with the rating agencies’ approach to structured finance modelling, will know that, almost without exception, the rating agencies use a cumulative default probability approach. In other words, the aggregate level of defaults displayed by a portfolio will be function of the original balance of the portfolio – regardless of its behavior.
For example, if the balance of the portfolio was €100 million at year zero and the cumulative default rate of 25% was applied, then the rating agencies will expect to witness a notional balance of €25 million of assets to suffer from default over the life of the portfolio regardless of prepayments, short-term delinquencies, dilutions, etc. For this reason, the default rate for any period is always multiplied by the year zero balance and not the opening balance for the period in question.
The cumulative default rate tells us everything about the aggregate balance of defaults over the lifetime of the portfolio but tells us nothing regarding the timing of defaults. For us to visualize the timing of defaults we need to introduce the concept of default vectoring – see below.
In contrast to the rating agencies, many investors, issuers, and investment banks prefer to use constant default rates or CDRs. Whereas cumulative default rates are applied against the original (or year zero) portfolio balance, CDRs are generally expressed as per annum amounts which are applied against the current outstanding balance – for instance, a CDR of 4% per annum could be re-expressed as a CDR of 0.34% per month. This would mean that every month 0.34% of the current (declining) opening balance of assets would be expected to suffer from default.
In summary: rating agencies use cumulative default rates, whilst issuers, investors and investment banks may choose to use CDRs.
Default Vectoring
As outlined above, a cumulative default rate only addresses the total value of assets expected to default but makes no mention of the timing of defaults – this feature is addressed by default vectoring. Most default vectors are defined by their pattern or timing or how the defaults are distributed over the life of the portfolio. For instance, in consumer asset classes it is not uncommon for a rating agency to specify up to three different default vectors that must be stress tested defined by their timing: front-loaded, evenly distributed and back-loaded. Compare this to CLOs, where Standard & Poor’s might ask for up to 20 different default vectors to be run defined by (a) a start date based on the number of years following closing and (ii) the default pattern (sawtooth versus evenly distributed, etc).
In summary: default vectors are only used with cumulative default rates and not CDRs. Moreover, the sum of the vectors must equal 100% - for instance, 25/25/25/25 or 40/30/15/10/5.
How Do Rating Agencies Account for Revolving Periods?
Before we get to the programming it is worth recalling why the problem of default vectoring doesn’t present itself when stressing revolving portfolios for the rating agencies – for instance, a deal that has a revolving period running for the first three years. Out of the majors, Fitch is the most detailed in its prescriptive, when they state that they model the deal from the end of the revolving period – i.e. at the end of year three in our example
Although Fitch are not explicit in their rationale, we can imply that they believe that during the revolving period, the portfolio will migrate to worst case – in other words: ‘good out – bad in’. the better loans will prepay and will replaced by less creditworthy assets. This means that those deals with longer revolving periods will, all other things being equal, require greater credit enhancement as this takes into account the replenishment capacity (or length of revolving period). Furthermore, the rating agencies will assume that the portfolio composition will tend towards the stressed (or worst) case composition allowed by the replenishment criteria [Note 1].
In summary: the rating agencies don’t require multi-period default vectoring for revolving periods as they will model the portfolio in run-off from the end of the revolving period assuming that, at this point, the portfolio has migrated to the stressed or worst case portfolio composition.
Shortcut Method for Displaying Aggregate Defaults for Multiple Periods of Origination
In this next section we going to examine how we can aggregate all the defaults for as many origination periods into one cell using a relatively simple formula.
Table 2 - Shortcut Method for Displaying Aggregate Defaults
We have moved the layout slightly from the previous worksheet to add maximum flexibility and to make it easier to understand.
As with the previous worksheet we are showing the periods across row 1. These could be months, quarters, years – you choose.
In row 3, we are showing each new originations for each period. For comparison purposes, we have used the same numbers from the previous example. There is no reason why you cannot continue to populate this row for the width of the worksheet if you have a large number of cohorts of new originations.
In row 5, we add our new row - the total new defaults for any period – regardless of which cohort they were derived from. This is the row where all the programming takes place. For instance, in cell E5 this will show €6.6m or the aggregate of:
a. The €100m of originations from period 1 suffering their 3rd cycle (36%) of the total defaults (€10m or 10% of €100m) or €3.6m, plus
b. The €80m of originations from period 2 suffering their 2nd cycle (24%) of the total defaults (€8m or 10% of €80m) or €1.92m, plus
c. The €90m of originations from period 3 suffering their 1st cycle (12%) of the total defaults (€9m or 10% of €90m) or €1.08m
In row 7, we show the assumed default vector assuming of four cycles (12%/24%/36%/28%). Again, you may elect to run much longer default vectors. This can be done simply by populating the remaining cells in row 7 to the right – remembering that the row total must always add up to 100%!
How the Formula is Constructed?
Let us have a look at the Excel formula in more detail and break it down into its component parts. We are using the SUMPRODUCT function to multiple two arrays - where the first argument contains an array with each period’s originations in time order and the second argument contains the default vector in reverse time order.
We will use cell E5 for the breakdown of the formula below:
=SUMPRODUCT(OFFSET($B$3,0,0,1,D1),N(OFFSET(D7,0,2-COLUMN($B7:D7),1,1))) *CumulativeDefaultRate
The first argument in SUMPRODUCT addresses the notional value of each period’s originations in time order
The second argument in SUMPRODUCT addresses the default vector taken from row 7 in reverse time order
The named range CumulativeDefaultRate refers to cell B9 containing 10%
To give you a better understanding, the same cell E5 is shown replacing part of the formula with values. This shows the aggregate or sum of (100 x 0.36), (80 x 0.24), and (90 x 0.12)
=SUMPRODUCT({100,80,90},{0.36,0.24,0.12})*0.1
How the OFFSET Function works?
The OFFSET function is probably the most useful function in the Excel toolbox and much maligned by auditors because of a ‘cloaking device’ that makes it invisible to the trace precedents/dependents features.
Most Excel users think of the OFFSET function as the older brother of the INDEX function. By using the first three arguments it can be a very flexible vector function to calculate from the contents of adjacent cells. However, by using the fourth and fifth arguments (both optional) the OFFSET function can be used to create virtual arrays [Note 2). The fourth argument defines the array’s height in rows (+ means row height down / - means row height up) and the fifth argument describes the array’s width (+ means column width to the right / - means column width to the left)
The First Argument in the SUMPRODUCT function – the New Originations
In this part of the formula we are going to have Excel produce a virtual array, one row in height and of variable column width (linked to the number of origination periods). The array will contain a list of each period’s new originations from period 1 to the previous month’s period. It is important to note that this will be in time order (i.e. €100m, €80m, €90m, …. etc).
OFFSET($B$3,0,0,1,D1)
The 1st argument shows point of origin (static address B3). 2nd and 3rd arguments show row and column adjustments – both zero
The 4th and 5th arguments show height (1 row) and width of virtual array. The width will increase by one column for each new cell in row 5
Again to give you a better understanding, the same cell E5 is shown replacing part of the formula with the array values.
{100,80,90}
The Second Argument in the SUMPRODUCT function – the Default Vector
The part of the formula requires more thought. My first (failed) attempt was based on a formula similar to first argument in SUMPRODUCT and looked something like this:
OFFSET($B$7,0,0,1,D1)
Whilst this part of the formula was generating the correct default vectors – they were being produced in reverse order! In other words, I was applying the last default vector to the first period’s originations.
Again, showing the results for this part of the (incorrect) formula you will see why the default vector didn’t work in its current form.
{0.12,0.24,0.36}
In the current construction, for the period three the €100m was multiplied against the 12% and the €90m against the 36% - in this particular case only the 2nd element was correct by dint of being the middle part of the array. Left uncorrected, the first period’s new originations would always be multiplied by the 12% default vector regardless of how many periods it had progressed from origination!
The key to the next stage was to reverse the order of the elements.
Reversing the Order of the Elements in the Second Argument.
To understanding how to reverse the order of the array, we need to know how the COLUMN function operates with a range of values. In general, most Excel users understand that the COLUMN function will return the column number (from 1 to 16,384) of the cell. For instance, COLUMN(D7) will return the value 4.
However, this is where Excel can offer a little magic. If the COLUMN function is used against a range of cells, it will return an array containing the column numbers for the range. Therefore, again using the extract from the formula in cell E5.
COLUMN($B7:D7) will return an array containing the values {2,3,4}
If we then subtract the array {2,3,4} from 2 (the column number containing the first column value of the default vector), this will generate a new array {0,-1,-2} which will form the 3rd argument in the OFFSET function - or the column adjustment to point of origin (D7) in the default vector.
The 4th and 5th arguments simply define each element in the array as one row in height and one column in width. This now produces an array (of variable width) containing the default vector in reverse time order.
OFFSET(D7,0,2-COLUMN($B7:D7),1,1)
The 1st argument shows point of origin (relative address D7). 2nd arguments show row adjustments of zero
The 3rd argument creates an array containing the column adjustment elements
Replacing this part of the formula with values – now you can see that the order has been reversed compared with the earlier incorrect version!
{0.36,0.24,0.12}
Lastly, in order for the above array to be recognised by the SUMPRODUCT function as a series of numbers (rather than text entries) we will use the text to number conversion function N() – without this function, you will get an error with SUMPRODUCT!
The final formula in cell E5 looks as follows:
=SUMPRODUCT(OFFSET($B$3,0,0,1,D1),N(OFFSET(D7,0,2-COLUMN($B7:D7),1,1)))*CumulativeDefaultRate
Or replacing the formula with values.
=SUMPRODUCT({100,80,90},{0.36,0.24,0.12})*0.1
Final Version – Cleaned Up
I have added a full version where I have removed all the comments and assumed 40 periods of originations and a default vector spread out over 13 periods to show how this could be used in practice. Moreover, as an additional check, I have added a Default Ledger tracking new defaults as well as write-offs (after 3 periods).
Table 3 – Final Version Cleaned Up
None of this is new or original. However, like most things in Excel the key is to success is adapting someone else’s formula for your own purposes. In my next post I will take this concept to the next level showing how to build a single ledger for prepayments, repayments and defaults – for single and multiple origination cycles.
Have fun, save some memory on your Excel workbooks and don’t forget to check out our securitisation courses.
Luke Mellor
Creative Capital Partners
+46 73 645 9936
Footnotes:
1. One exception to this rule relate to Moody’s approach to CLOs where ‘additional’ collateral is added to the portfolio. This could occur either through (a) the reinvestment of default proceeds or (b) through the overcollateralization of the portfolio during the reinvestment period by way of diverting the interest amounts to purchase additional collateral by way of a breach of the reinvestment OC test.
2. A little trick to reveal the values of the virtual array is to select the part of the formula you wish to show in the formula bar and press F9 - do remember use ESC when finishing to avoid retaining the values compared to the formula.