Depreciation Calculator - Iterative Macro

[m_tizzie]

I am trying to replicate a depreciation calculator that was previously done in Excel with formulas.  Essentially, I have a list of approximately 100+ assets and I want to calculate the Current Year Expense, YE NBV, and Straight Line Depreciation for each year of those assets' lives.  Due to half year double declining balance depreciation, the calculations are a bit more complicated and are dependent on the prior year being calculated before you can calculate the current year so multi-row formulas were not working for me.  I'm currently trying to create an iterative macro that will perform the calculations one row at a time but I'm having issues getting it to loop.  I am brand new to iterative macros and have been trying to research how to set them up but all the examples I'm seeing are different than what I need.  I would appreciate any help regarding how to correctly set up my iterative macro or ideas of other ways to go about this if I'm missing something. 

Thank you in advance for your help!  

Attached is...

- Input Excel file

Only one asset is currently shown for simplicity.  Year 1 calculations as well as the duplication of rows for the number of years of life were all added in as part of a workflow to set up the data in preparation for the iterative macro loop calculations to be done.

- Alteryx Macro workflow 

This workflow does not include all the necessary calculations yet.  I was just trying to get the loop to work correctly before adding in all the details.

- Screenshot of Depreciation Calculator in Excel

This is just to show what the expected values would be

[apathetichell]

@m_tizzie  --- follow my instructions. I have explained how to build it. @PangHC may have meant --- it is a manual in that you are using substantial adjustments to it (ie year 1/last year/overwriting with the year 7 values for years 7-11).

[apathetichell]

If there isn't a python module for this ---> I can throw something up on GH. I built it out and got:Year Depreciation Accumulated Book Value Months Remaining Straight Line Value Depr Factor
------------------------------------------------------------
1 $22600.00 $22600.00 $226000.00 126.00 $11300.00 0.090909
2 $41090.91 $63690.91 $184909.09 114.00 $21523.81 0.165289
3 $33619.83 $97310.74 $151289.26 102.00 $19464.11 0.135237
4 $27507.14 $124817.88 $123782.12 90.00 $17798.74 0.110648
5 $22505.84 $147323.72 $101276.28 78.00 $16504.28 0.09053
6 $18413.87 $165737.59 $82862.41 66.00 $15580.97 0.07407
7 $15065.89 $180803.48 $67796.52 54.00 $15065.89 0.060603
8 $15065.89 $195869.38 $52730.62 42.00 $15065.89 0.060603
9 $15065.89 $210935.27 $37664.73 30.00 $15065.89 0.060603
10 $15065.89 $226001.16 $22598.84 18.00 $15065.89 0.060603
11 $15065.89 $241067.05 $7532.95 6.00 $15065.89 0.060603
12 $7532.95 $248600.00 $-0.00 0.00 $7532.95 0.030301

@m_tizzie --- I'd suggest that your straight line depreciation for the last year is wrong. It should be set for a max of book value remaining.

[PangHC · Accepted solution]

@m_tizzie 
generally, you need to provide all related information for other to help, you can give example or remove all the sensitive data.

for you case, you need to put all information into the table to calculate.
For example, the [month in first year], [Method], it play key role for the calculation, unless it fixed for all time.

It really need to using macro because the CY Expenses, Depr, NBV, Remaining life is refer to each other.
Where it not working by multi-row macro.

I built 1 macro for it. please ask if you not understand how it work.

Spoiler

Workflow: 
1. Create columns for [CY expense] and etc to ease for macro
2. After macro, only then do the extra things that no necessary in macro.


Macro:
1. to output year 0, the filter is apply before process.
2. create formula to process the CY  expense, depr, NBV, remaining year etc
Note: by sorting of in formula, it kind of act like row-1. hence multi-row formula is not required

[m_tizzie]

@PangHC Thank you so much for the example workflow!  I was able to tweak the formulas on this and add my own additional data modifications so that it worked for the process I am trying to improve.