
1. Re: Debt Balance Calculation
Shinichiro Murakami Jan 16, 2016 4:04 PM (in response to Ray Yu)Ray,
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 = (10.0458)*C2+(5000000*0.0458)+MAX(0,MIN(E2,(10.0458)*C2(0.0458*5000000)))
I converted this formula to Tableau table calc.
[Fiinal_Calc_begin_Balance]
if index()=1 then 78400000 else (10.0458)*previous_value(1)+(5000000*0.0458)+MAX(0,MIN(lookup(ATTR([Debt Repayment 1]),1),(10.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,
Shin

Debt_Balance.twbx 28.0 KB

debt balance sample_rep.xlsx 13.0 KB


2. Re: Debt Balance Calculation
Ray Yu Jan 18, 2016 2:32 PM (in response to Shinichiro Murakami)Hi Shin,
Thanks a lot for your help.
It solved my problem perfectly and was also a great learning experience for me.
Thanks,
Ray

3. Re: Debt Balance Calculation
Shinichiro Murakami Jan 18, 2016 2:37 PM (in response to Ray Yu)Ray,
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?
Thanks,
Shin