1 of 1 people found this helpful
Took time, but finally...
Actually you excel sheet calculation is based on six columns, but 4 out of them are all based on independent parameter of "Debt Beginning Balance" and "Debt Repayment 1". Plus "Debt Beginning Balance"'s independent value is only first row.
Then, excel file's formula is something like this.
C3 = (1-0.0458)*C2+(5000000*-0.0458)+MAX(0,MIN(-E2,-(1-0.0458)*C2-(0.0458*5000000)))
I converted this formula to Tableau table calc.
if index()=1 then -78400000 else (1-0.0458)*previous_value(-1)+(5000000*-0.0458)+MAX(0,MIN(-lookup(ATTR([Debt Repayment 1]),-1),-(1-0.0458)*previous_value(-1)-(0.0458*5000000))) END
Begin Balance is last year's End Balance, and I calculated Begin Balance according to previous Begin Balance, then End is Calculated as "Next Begin Balance".
With that, year 2023's End balance is missing. I don't know I can fix without extending one period.
Anyway, attached 9.0 Tableau and Excel.
Thanks a lot for your help.
It solved my problem perfectly and was also a great learning experience for me.
This was tough one....
I also leaned many things through this.
But for this specific needs, Excel is much easier......
Of course there are specific strength both for Table and Excel or any other tools..
BTW, could you put "Correct Answer" to this question, for other people can see it's answered?