Double Declining Balance (DDB function) in Excel

[yikl]

Dear experts 

I try to mimic the double declining balance method (DDB Function) in Excel to Alteryx.  Initially think this is straight forward but do struggle at the end.  The depreciation value at a particular time is dependent on the the accumulated values since day 1.

I have attached an Excel on how this is calculated. Appreciate if someone can shed some lights. 

Many thanks.

[danilang · Accepted solution]

Hi @yikl 

Having the sum of the previous years included in the calculation makes this a prime candidate for an Iterative Macro.

The main program just sets up the inputs for the macro

The macro creates a new record creating a sum of the existing records and uses this in the calculation of the depreciation for the current year.  This record is added to existing records.  The Formula tool called Loop adds a boolean value all the records.  If the number of times we've gone through the loop is less than to the number years passed in as a param, it loops around again passing the existing records out the I output to go back to the input.  If not, the loop stops and the data is passed to the D output.  I used the number of years (# of Year, from your spreadsheet) to determine when to exit the loop.  I realize the depreciation can accrue indefinitely, since it never reaches zero, but I had the stop the loop somewhere.

The results, with no rounding, are as follows.

Dan

[PhilipMannering · Accepted solution]

Here is (I think) a simpler way to do it,

Workflow

[ponraj]

@yikl , It can be achieved using iterative macro.  Can you just explain on what basis it stopped at 8 year ?  I will be able to help you with the solution if you could explain it. 

[yikl]

Hi Ponraj. 

Thanks.  I just stop at year 8 for illustration purpose.  It should theoretically run all the way to end of year 10.

[ponraj · Accepted solution]

Here is the sample workflow.  Hope this is helpful. 

[yikl]

Thanks Dan, Philip and Ponraj

Your solutions are all very useful.  Appreciate your help.

Henry