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

    Debt Balance Calculation

    Ray Yu

      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
          Shinichiro Murakami

          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
            Ray Yu

            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

              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