Using dynamic arrays for investment schedule

[niwaajjua]

I need help creating dynamic arrays for the gain, the opening and closing balance in an investment schedule. I get a circular reference since all these columns are dependent on each other.

 

[vletm]

niwaajjua​

You should save those C- & D-column values to Your file.
... other cannot use Your links.

 

[niwaajjua]

niwaajjua​

You should save those C- & D-column values to Your file.
... other cannot use Your links.

here's the updated files as requested

 

[vletm]

niwaajjua​

It wasn't any request.
Did You tested Your file after ... You did something?
After I saved those values ....
Could You use something like this?

 

[niwaajjua]

instead of a table, i want to use dynamic arrays

 

[vletm]

niwaajjua​

As written After I saved those values ...
You could check if Your Excel-version match ...

Cumulative Sum Formula using new Excel Dynamic Array Formulas

I'm using the new Dynamic (ie Spill) formulas in Excel. I want a dynamic array that is the accumulation or running total of another dynamic array. Let's assume I have the following dynamic data in...

stackoverflow.com

 

[AlanSidman]

Here is a model that I have been using for over 20 years in Excel that lets you track individual stock performance. Use or Not. I think it does a great job for me.

 

[Peter Bartholomew]

You have already used the SCAN function to accumulate the Total Investment. What is different about the Closing Balance is that it requires access to two lots of series data, namely the 'In/Out' series and the 'Return' series. The key to such problems is to scan over a key series (I have used the Date), from which corresponding terms of the other series may be looked up (INDEX or XLOOKUP).

=SCAN(0,date,
    LAMBDA(c∕d, period,
        LET(
            rate,    XLOOKUP(period, date, Return),
            change,  XLOOKUP(period, date, InOut),
            balance, (change + c∕d) * (1 + rate),
            balance
        )
    )
)

Once the Closing Balance has been accumulated, the Opening Balances and Gains (or losses) are calculated from it using straightforward dynamic array formulas

openingBalance
= LET(
    c∕d, VSTACK(0, DROP(closingBalance, -1)),
    c∕d + InOut)
  )
 
gainLoss
= closingBalance - openingBalance

There is another way of passing multiple series as a single array and that is to use an array of functions; each function (a thunk) returns the series data for a given period in known order. To calculate the function (tuning it back to an array of values for a single period) requires an empty parameter string. I show that in green, but it most likely ranks as an 'advanced' technique.

= LET(
    dataϑ,  BYROW(periodData, LAMBDA(x, LAMBDA(x))),
    return, SCAN( 0, dataϑ,
        LAMBDA(c∕d, perioddataϑ,
            LET(
                rate,    INDEX(perioddataϑ(), 1),
                change,  INDEX(perioddataϑ(), 2),
                balance, (change + c∕d) * (1 + rate),
                balance
            )
        )
    ),
    return
  )