Double Declining Balance (DDB function) in Excel

Question

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.

Excel DDB Formula.xlsx

danilang · Accepted Answer

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 Answer

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

Workflow

ponraj · Accepted Answer

Here is the sample workflow.  Hope this is helpful.