Multi Row Formula/3 Calculated fields dependent on each other

I have a document where I have the opening balance, all of the repayments (as clearing in the doc), interest rate and days for interest.

I need to calculate the following:

*Opening balance for each period,

*Interest for each period,

*Closing balance for each period (will then be the opening balance for the next period).

When I use the multi row formula it does not update all of the fields as needed, or not in the order I want.

I have tried using various orders for the multi row, but have not been able to solve this.

In the excel there is the input and then the expected output.

Also see the workflow attached.

To Upload.xlsx

Workflow to upload.yxmd

Preparation

Accepted answers

RolandSchubert

Hi @MorneViljoen ,

I think, calculation has to start with closing balance. I've changed the references to the closing balance of previous period and included the interest calculation, now it seems to work. Hope this is helpful.

Best,

Rolland

Workflow to upload modified.yxmd

All comments

jyurkovich

If you change the formula in the first multi-row formula to this, it appears to work:

IIF ([lease liability]=0,IIF([Row-1:closing balance calculation]=0,[Row-1:lease liability]-[Row-1:clearing]+[Row-1:interest recalc.],[Row-1:closing balance calculation]), [lease liability])

RolandSchubert

Hi @MorneViljoen ,

Best,

Rolland

Workflow to upload modified.yxmd

MorneViljoen

It seems like this sort it out to an extend but not exactly, the opening balance on the one month does not agree to the closing balance for the prior month, this is out by the prior month interest, see print screen:

MorneViljoen

Yes @RolandSchubert , this seems to be the solution, let me run it with the full data set and other parts of the workflow then will update you guys.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703