Reversing the order of an array within SUMPRODUCT

[Etoilebrilliant]

Okay, the good news is what I've done works perfectly. The bad news is I don't know what I've done or, more specifically, I can't explain it to a third party.

A brief description:

Row 2 contains the months from inception.
Row 3 shows the recovery vector for any cycle of new defaulting assets. This must add up to 100%. In this example: for every cycle of defaults: 25% of the notional will be recovered after 1 month, 22.5% of the notional will be recovered after 2 months, 20% of the notional will be recovered after 3 months, so on and so forth.
Rows 6 and 10 are simply opening and closing balances and are not, as such, important.
Row 7 shows the default cycle: i.e. 6,000,000 of assets suffered from default in month one, 7,200,000 of assets suffered from default in month two, 8,400,000 of assets suffered from default in month three.

Row 8 is where the action takes place. The purpose of the formula which works is to aggregate the notional recoveries based on (i) the notional balance of assets that have gone into default in previous periods (row 7) and (ii) the recovery vector (row 2).

Using D8 as an example this should show: (6,000,000 x 22.5%) plus (7,200,000 x 25%) or 3,500,000 (expressed as a negative for accounting purposes)


Excel 2012

	A	B	C	D	E	F	G	H	I
2	Period	1	2	3	4	5	6	7	8
3	Recovery Vector (Months after Default Start)	25.00%	22.50%	20.00%	17.50%	15.00%
4
5
6	Principal Default Opening Balance	0	6,000,000	11,700,000	16,950,000	12,030,000	7,650,000	3,810,000	1,260,000
7	New Assets Entering Default (Notional)	6,000,000	7,200,000	8,400,000
8	Notional Recoveries (this row contains the formula)		-1,500,000	-3,150,000	-4,920,000	-4,380,000	-3,840,000	-2,550,000	-1,260,000
9
10	Principal Default Closing Balance	6,000,000	11,700,000	16,950,000	12,030,000	7,650,000	3,810,000	1,260,000	-

<tbody>
</tbody>

Cell D8 contains the formula: =-SUMPRODUCT(OFFSET($B7,0,0,1,C2),N(OFFSET(C3,0,2-COLUMN($B3:C3),1,1)))

The first argument in SUMPRODUCT generates an array showing the monthly defaults since inception (no problem - this I understand)
The second argument in SUMPRODUCT generates and array in reverse order showing the recovery vector starting from the previous month until the month of inception.

It is the OFFSET function that leaves me scratching my head. (A) first the 3rd argument: conceptually I can't get my head around using a range within a column function - what does the return value tell me? (B) second the 5th argument showing the number of columns takes the static value of "1" whilst it should be dynamic.

Please could somebody explain to me in words of one syllable what I have done and why it works.

Many thnaks in advance

 

[Dave Patton]

Try using Excel's Formulas | Evaluate Formula and review the formula.

 

[Etoilebrilliant]

Try using Excel's Formulas | Evaluate Formula and review the formula.

Thanks. That helped a lot. it seemed to show that the 3rd argument of offset could contain an array (e.g. {-1, -2, -3}). I can see it in a different light.

 

[Dave Patton]

The third argument is an array {2, 3} 2-2 yields 0 2-3 yields -1
That part of the formula yields 0.225 and 0.25