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)

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?