Resetting Period Dynamic Array Formula

I am trying to forecast the impact of monthly maturing balances for 12 consecutive months. The impact in each month = monthly balance * 1/12 * 1%. The next month, this maturing balance impact = monthly balance * 2/12 * 1%, and so on until the final and twelfth month (where impact = monthly balance * 12/12 * 1%).

In each consecutive month, the balance multiplier should start again at 1/12 and work down to 12/12 (though balances in month 2 will begin at 1/12 and only work up to 11/12, as this forecast is limited to 12 months in total).

I appreciate any and all assistance guys!

Thank you, Matt

Here is the data table with my attempt at a formula

Here is the formula dragged right and down

The issue is that now the formula in X14 has a multiplier of 2/12 (instead of 1/12).

This would be the correct reference in the formula for X14, that has a multiplier of 1/12

Intention is to aggregate the balances in each consecutive forecast month in column AI, and then sum these monthly forecast balances into a grand total in AI25

I have attempted using less space to write a sumproduct formula to encompass all the cells but I can never seem to get it over the line.

Change the second $V13 in the formula for cell W13 to (1+$V13-W$10) so that the formula now reads:

=IF(W$10>$V13,0,W$11*(1+$V13-W$10)/12*1%)

and copy to range W13:AH24.

This will now calculate 1/12 of 1% of the monthly balance (in row 11) for an element on the "leading diagonal" of W13:AH24 with 2/12 in the cell below, 3/12 in the cell below that, etc.

Stripping the out the monthly balance, division by 12 and multiplication by 1% perhaps provides a clear view as to how this substitution works...

Presumably, what you are wanting to do with your use of SUMPRODUCT is to calculate the grand total in cell AI25. It is not clear (to me at least) why you would want to do this (other than as an intellectual exercise) but the values shown in the picture above do provide a clue as to how to go about this.

Your SUMPRODUCT should have the balances in W11:AH11 as one of its arguments, since each non-zero cell in W13:AH24 is obtained by multiplication using the balance in row 11 of its same column as one of the multiplicands.

Looking at the table above W11 should be multiplied 1+2+...+12 (and then divided by 12 and multiplied by 1%). Similarly, X11 should be multiplied 1+2+...+11 (and, again, then divided by 12 and multiplied by 1%).

Stripping out the "divided by 12 and multiplied by 1%" (as these can be done outside the SUMPRODUCT) then the things to concentrate on are the series of:
1+2+...+12
1+2+...+11
etc

As any school student of mathematics probably knows 1+2+...+n is equal to n*(n+1)/2. It can also be noted that the value of n as 12 for column W is provided by 13-W10, and similarly those for columns X, Y, etc by 13-X10, 13-Y10, etc.

Putting this all together then a suitable formula involving use of SUMPRODUCT for cell AI25 should be:

=SUMPRODUCT((13-W10:AH10)*(14-W10:AH10)/2,W11:AH11)/12*1%

Addition

This addition addresses whether it is possible to produce similar formulae for the row totals in the range AI13:AI24.

Again, looking at the picture above and taking spreadsheet row 18 as an example, the requirement is to multiply W11 by 6, X11 by 5, ... , AB11 by 1 and to sum the resulting products, which again suggests the use of SUMPRODUCT.

The required multipliers of 6, 5, 4, 3, 2 and 1 are obtained by subtracting the month numbers in W10:AB10 from 7, which is conveniently provided as 1+V18.

However, there is a complication. The length of the arrays forming the arguments of the SUMPRODUCT differs for each spreadsheet row. For spreadsheet row 18 the arrays should each contain 6 elements, but for row 17 it is 5 elements, whilst for row 19 it is 7 elements. Messy, but not impossible to deal with.

An alternative is to make each array 12 elements long but to make the multiplying integer 0 for the "unwanted" products. So, again in terms of row 18, cells AC11 to AH11 are each multiplied by 0. Now the twelve products involving W11 to AH11 can be summed to get the required total for cell AI18.

A suitable array expression for delivering the required 12 multipliers for row 18 is:
(1+V18-W$10:AH$10)*(V18>=W$10:AH$10)
and, as a result, the appropriate formula for cell AI18 is

=SUMPRODUCT((1+V18-W$10:AH$10)*(V18>=W$10:AH$10),W$11:AH$11)/12*1%

and this formula can be copied to the range AI13:AI24.

A challenge

The SUMPRODUCT's for both the grand total and the row totals utilise only the ranges W10:AH10 (containing the values 1-12 as a row), W11:AH11 (containing the 12 monthly balances) and V13:V24 (again containing the values 1-12, but this time as a column).

It is fairly straightforward to redesign the calculations to occupy just 3 columns - the first containing 1-12, the second containing the monthly balances and the third containing the subtotals currently in AI13:AI24. The challenge is not to do this but to justify doing this in terms of transparency and explaining how the calculations work.