Double Declining Balance (DDB function) in Excel
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Best Practices
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Ponraj.
Thanks. I just stop at year 8 for illustration purpose. It should theoretically run all the way to end of year 10.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Dan, Philip and Ponraj
Your solutions are all very useful. Appreciate your help.
Henry
