3 Replies Latest reply on Jan 18, 2016 2:37 PM by Shinichiro Murakami

# Debt Balance Calculation

Hi,

Thanks in advance for any ideas.

I am trying to use Tableau to create an Excel model for debt balance calculation.

The calculations are:

Ending Balance=Beginning Balance + Addition + Repayment

Beginning Balance = previous period's ending balance

Addition = Beginning Balance * 5% + a value

Repayment = Max(0, Min (beginning balance+addition, repayment 1 (a value)))

As you can see, there are circular references in these calculations, which make it very difficult to build.

I attached a sample and hope someone can help me solve this.

Thanks

Ray

• ###### 1. Re: Debt Balance Calculation

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 = (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.

[Fiinal_Calc_begin_Balance]

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,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Debt Balance Calculation

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

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