Circular reference in a financial model

[lynnsong986]

Hello, I need some help with a circular reference in TM1 (knowing it is not allowed). Attached is a simplified model where a circular reference exists in excel:

interest expense (to simply it I only calculate on short term debt) = STD * interest rate; interest is part of net income that goes into retained earnings on balance sheet
Short term debt - this is basically a plug, short term debt is not allowed to go negative (once it becomes negative cash will increase)

the issue here is that I need to circular reference STD in order to calculate interest expense. In the Tm1 model, this is done through rules (not TI), I can't make it to work (basically crushed the server because of the circular reference), can someone please help me with this?

thanks so much!

[ascheevel]

To be sure, you don't really have a TM1 problem, you have a math problem. Solve the circular ref issue in excel (without iterative calc turned on) and you would solve your problem implementing in TM1. I wasn't a math major, so the attached solution might not be the most elegant, but one way to solve these is to calc individual parts that are not part of the dependent calculation and reference those instead. I've done this in the attached in rows 19 through 22 in column D. Column B has your original formulas. By calcing retained earnings & net income before interest is applied, you can calc short-term debt before interest and then apply the interest and sum the result for your final short-term debt amount. I consolidated the formula in cell E23 if you don't like creating measures for the different formula steps.

[lynnsong986]

oh wow I never thought of circumventing formulas like that, you are a genius! thanks a ton!!

[ascheevel]

Cubewise had a good blog post on working through circular ref in TM1, provides good examples & visuals.


https://code.cubewise.com/blog/resolvin ... alculation