Simplify an Excel array function

Question

I'm creating a excel file where I don't want to use VBA which would model the drawdown of loans (drawn each quarter for varying amount), and the reimbursement schedule of these loans (which are all reimbursed according to a defined schedule (X% of the loan reinbursed in quarter 20 after drawdown, y% in quarter 21 after drawdown, etc.). I want to caluculate the amount reimbursed every quarter.

The difficulty in this is the fact that although each loan is reimbursed according to the same schedule, each loan is drawn intialy at a different date, which will offset the reimbursement schedule by X period.

The way I would typicaly do this is to create a "triangle" with one line for each loan each offset by one row. However given the large number of loans, this is not practical.

The only solution I have found right now is the array formula as follow:

=IFERROR((I7*Input!$AZ$90:$CE$90)+(J7*Input!$AY$90:$CE$90)+(K7*Input!$AX$90:$CE$90)+(L7*Input!$AW$90:$CE$90)+(M7*Input!$AV$90:$CE$90)+(N7*Input!$AU$90:$CE$90);0)

The two problems I am facing are :

this is extremely manual and error prone (This is an extract, the formula would have to have 50+ terms)
I have to include 50 blank rows in my "Input" excel sheet to allow for the offset to work

Do you have an idea how i could do it better ?

												Q1 N	Q2 N	Q3 N	Q4 N	Q1 N+1	Q2 N+1	Q3 N+1	Q4 N+1	Q1 N+2	Q2 N+2	Q3 N+2	Q4 N+2	Q1 N+3	Q2 N+3	Q3 N+3	Q4 N+3	Q1 N+4	Q2 N+4	Q3 N+4	Q4 N+4
Loans												10	20	50	50	20	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Reimbursment schedule		0%	0%	0%	0%	0%	0%	0%	5%	10%	30%	50%	5%	0%	0%	0%	0%	0%	0%	0%	0%


Reimbursement per period	Q1 N	Q2 N	Q3 N	Q4 N	Q1 N+1	Q2 N+1	Q3 N+1	Q4 N+1	Q1 N+2	Q2 N+2	Q3 N+2	Q4 N+2	Q1 N+3	Q2 N+3	Q3 N+3	Q4 N+3	Q1 N+4	Q2 N+4	Q3 N+4	Q4 N+4

Loan Q1 N												0	0	0	0	0	0	0	0.5	1	3	5	0.5	0	0	0	0	0	0	0	0
Loan Q2 N													0	0	0	0	0	0	0	1	2	6	10	1	0	0	0	0	0	0	0
Loan Q3 N														0	0	0	0	0	0	0	2.5	5	15	25	2.5	0	0	0	0	0	0
Loan Q4 N															0	0	0	0	0	0	0	2.5	5	15	25	2.5	0	0	0	0	0
Loan Q1 N+1																0	0	0	0	0	0	0	1	2	6	10	1	0	0	0	0
Loan Q2 N+1																	0	0	0	0	0	0	0	0.5	1	3	5	0.5	0	0	0
Total												0	0	0	0	0	0	0	0.5	2	7.5	18.5	31.5	43.5	34.5	15.5	6	0.5	0	0	0


Méthod 2												0	0	0	0	0	0	0	0.5	2	7.5	18.5	31.5	43.5	34.5	15.5	6	0.5	0	0	0

Ok I think I have found a solution, I would juste need help to write it as a formula. The trick is to :

reverse both the loans and the reimbursement schedule arrays
sumprod these two arrays (with $ on the reimbursment schedule array)
reverse the resulting array to obtain the result

I would like to pack all this in one formula, could you help ?

I believe that using VBA is the best option. The code would be incredibly simple and small to meet your needs. If you save the document in .xlsb format, it will not affect the final size at all and it would be highly portable (including the possibilities for actions/improvements). If you need the code, just let me know! — Rafa Mori
– Rafa Mori, Commented Jan 27 at 10:12
VBA comes in two ways: macros and UDFs (User-Defined Functions). The drawback of macros is that some event is needed to trigger them, while UDFs don't have that problem: you can use them as regular Excel functions. So why don't you want VBA? Don't you want macros for the aforementioned reason or don't you want VBA period? — Dominique
– Dominique, Commented Jan 27 at 10:30
Hi Dominique, on second though, it would be great if you could share code for a UDF if you have some time ? Thanks a lot ! — Axel Augustin
– Axel Augustin, Commented Jan 27 at 16:05
I think your table is a bit off. Also what is your goal. To have a row showing a total per quarter, or a 2D result for each quarter/loan? — P.b
– P.b, Commented Feb 28 at 20:48

Rich · Accepted Answer · 2025-01-28 14:29:28Z

0

=TRANSPOSE(SUMPRODUCT(TRANSPOSE(B2:B6), TRANSPOSE(COUNTIF(ROW(C2:C6), ROW(C2:C6)))))

edited Jan 28 at 14:29

Rich

6,58520 gold badges38 silver badges58 bronze badges

answered Jan 27 at 10:56

tejas e

743 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Axel Augustin Jan 27 at 11:38

i'm sorry this function does not seem to work for me

P.b · Accepted Answer · 2025-02-28 22:07:41Z

Not sure about how you wanted the output to look like, so I combined 2 functions, so you could easily drop one to have a different output:

=LET(Q,TOCOL(IFS(B2:U2,B1:U1),2),
     L,TOCOL(1/(1/B2:U2),2),
     R,B3:U3,
     M,MAKEARRAY(ROWS(L),COUNTA(R),
          LAMBDA(x,y,
                 LET(P,XMATCH(INDEX(Q,x),Q),
                     IF(y<P,
                        "",
                        INDEX(L,P)*INDEX(--R,1+y-P))))),
BYCOL(M,SUM))

This first declares names for ranges: Q for the quarters names where there's a loan L for the loans that have amounts, R for the reimbursements range.

Next MAKEARRAY is used to create an array the height of the count of loans and the width of reimbursements. We check the xth (makearray's row number's) loan amount and multiply it by the reimbursement percentage of the matching quarter. This is found by matching the xth (makearray row number's) Q against Q and count from there by using the sum of 1+y-P, which starts at 1 at the start quarter and adds 1 for every next column. The MAKEARRRAY is declared as M and is summed by row using BYROW(M,SUM). If you need the 2D array, drop this last addidltion and simply use M.

Collectives™ on Stack Overflow

Simplify an Excel array function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related