Multi Row Formula/3 Calculated fields dependent on each other

[MorneViljoen]

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.

[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 · Accepted solution]

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 

[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:

[jyurkovich]

it looks like Roland has it right on this one. 

[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.

[MorneViljoen]

@RolandSchubert  thank you this works for the bigger workflow as well.