CODEWITHSUNDEEP
excelarray-formulasoffsetfinance
Axel Augustin
Axel Augustin

Reputation: 1

Simplify an Excel array function

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 :

  1. this is extremely manual and error prone (This is an extract, the formula would have to have 50+ terms)
  2. 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 :

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

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

Upvotes: 0

Views: 87

Answers (2)

P.b
P.b

Reputation: 11628

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.

Upvotes: 0

tejas e
tejas e

Reputation: 74

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

Upvotes: 0

Related Questions