Using Dynamic Arrays to calculate a reducing balance amortisation schedule

[arnabkghosal]

Hi all,

I am new to the forum so apologies in advance if I do anything incorrectly. I have hit a brick wall on a schedule that I am trying to prepare using dynamic arrays and you are my last hope before I go old school.

The task:
I want to effectively build a reducing balance amortisation schedule using dynamic arrays. As an example the table should look like this:

No	Day	Days Left	bf	Additions	Amortisation	cf
1​	01/01/2021​	25	-	60,000.00	- 2,400.00	57,600.00
2​	02/01/2021​	24	57,600.00	-	- 2,400.00	55,200.00
3​	03/01/2021​	23	55,200.00	-	- 2,400.00	52,800.00

So essentially the amortisation is calculated as: (bf + Additions) / Days left

However, I cannot get this work using dynamic arrays as I keep getting a circular reference. Please see the attached file in the tab called "Dynamic Arrays" (The "Expected Result" tab shows what the result should be).

Is this something that is possible using Dynamic arrays or should I go back to doing it the old way.

Thanks

Arnab

 

[Excel Wizard]

Maybe try at K6

=IF(G6#>I3,0,-PROB(G6#,IF(B6:B95,C6:C95/(I3-G6#+1))/SUM(IF(B6:B95,C6:C95/(I3-G6#+1))),,G6#)*SUM(IF(B6:B95,C6:C95/(I3-G6#+1))))

 

[Peter Bartholomew]

Problems with accumulating arrays and aggregating 2D arrays have made the creation of fully dynamic models difficult. Lambda functions made it possible, although not something one could recommend. The helper functions that are just being rolled out now should provide a practical solution to the problem. These links may help
Ways of performing Accumulation with Dynamic Arrays - Microsoft Tech Community
Announcing LAMBDA Helper Functions (microsoft.com)

 

[arnabkghosal]

Maybe try at K6

=IF(G6#>I3,0,-PROB(G6#,IF(B6:B95,C6:C95/(I3-G6#+1))/SUM(IF(B6:B95,C6:C95/(I3-G6#+1))),,G6#)*SUM(IF(B6:B95,C6:C95/(I3-G6#+1))))

That's awesome, thank you so much for solving even though I do not understand it

The columns B6:B95 and C6:C95 won't exist in the final schedule so I have changed the formula to:

=IF(G6#>I3,0,-PROB(G6#,IF(J6#<>0,J6#/(I3-G6#+1))/SUM(IF(J6#<>0,J6#/(I3-G6#+1))
),,G6#)*SUM(IF(J6#<>0,J6#/(I3-G6#+1))))

I believe this achieves the same result.

Thanks once again

 

[arnabkghosal]

Problems with accumulating arrays and aggregating 2D arrays have made the creation of fully dynamic models difficult. Lambda functions made it possible, although not something one could recommend. The helper functions that are just being rolled out now should provide a practical solution to the problem. These links may help
Ways of performing Accumulation with Dynamic Arrays - Microsoft Tech Community
Announcing LAMBDA Helper Functions (microsoft.com)

Thanks for sharing these links

 

[Peter Bartholomew]

I have attached a file to show how balances can be created from cash flows by using SUMIFS. It is an approach that gets resource intensive as the array sizes grow but it works fine for smaller datasets. It is important to generate the flow variables without referencing the balance because then you will be back to circular referencing. Sorry that I have not placed the formulae in the correct context, but I am a mathematician/engineer, not a finance specialist.

 

[arnabkghosal]

I have attached a file to show how balances can be created from cash flows by using SUMIFS. It is an approach that gets resource intensive as the array sizes grow but it works fine for smaller datasets. It is important to generate the flow variables without referencing the balance because then you will be back to circular referencing. Sorry that I have not placed the formulae in the correct context, but I am a mathematician/engineer, not a finance specialist.

Thank you that's very helpful. Note your comment re dataset size

 

[Peter Bartholomew]

A preview of the future. I have just had some new functions enabled on my Insider beta copy of Excel.

= SCAN(0, No#,
      LAMBDA(acc,k,
         LET(
            inflow,  INDEX(Additions#, k),
            outflow, INDEX(payment#, k),
            acc+inflow-outflow)
      )
  )

SCAN runs through an array of calculations and collects the results. Your number column forms the input array;
LAMBDA contains an accumulator that allows each step of the calculation to access the result of its predecessor;
k is a name / dummy variable used to store each value from No in turn;
INDEX is used to lookup the required value from additions and payments;
these are added to the running balance (acc).
Note: the file will not run for quite a while yet!