Reversing the order of an array within SUMPRODUCT

Etoilebrilliant

New Member
Joined
Oct 10, 2017
Messages
5
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
ABCDEFGHI

<tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Period[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]8[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Recovery Vector (Months after Default Start)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]25.00%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]22.50%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]20.00%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]17.50%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]15.00%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]Principal Default Opening Balance[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"] 6,000,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"] 11,700,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"] 16,950,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"] 12,030,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"] 7,650,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"] 3,810,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"] 1,260,000[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] "] New Assets Entering Default (Notional)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 6,000,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 7,200,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 8,400,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] "] Notional Recoveries (this row contains the formula)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] -1,500,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] -3,150,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] -4,920,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] -4,380,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] -3,840,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] -2,550,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] -1,260,000[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] "] Principal Default Closing Balance[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 6,000,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 11,700,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 16,950,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 12,030,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 7,650,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 3,810,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] 1,260,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"] -[/TD]

</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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try using Excel's Formulas | Evaluate Formula and review the formula.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top