Hello everyone-
I've been working on building an iterative workflow to calculate Present Values at future points in time for a given set of cashflows but am not able to figure out macros just yet.
I attached a simple example of what I'm trying to accomplish and make more efficient. Any help working through this would be much appreciated.
Solved! Go to Solution.
Hi @msnyder,
See attached. To make this work I did the following:
Let me know if this works or if you have any issues.
Wow @Luke_C . You figured it out 😅 i got bit confused when i saw the inital workflow. Kudos to you 🙂👍
Hi @msnyder
Here is a tutorial video on iterative macro as a refresher https://community.alteryx.com/t5/Videos/Build-Your-First-Iterative-Macro/td-p/55565
Hope this helps 🙂
Hi @msnyder ,
In addition to @Luke_C 's iterative approach, I used a simple batch macro to achieve the same. The importance of this was to ensure it was dynamic.
I started by generating the Qtr dates from min to max using the generate rows tool, I then fed this into a batch macro as the control parameter along with the full dataset.
The macro then essentially carries out what you're doing on each branch before unioning and outputting:
Hope this helps,
M.
An alternative to @Luke_C - here's a batch macro instead of an iterative macro. This version generates period based upon floor division by 3 months and then feeds that into a filter and summarizes.
There's an optional text input to change the discount rate for the NPV.
FYI - You are receiving cash flows monthly but discounting quarterly which can throw off your numbers. Attached is a running total NPV analysis with monthly computations. I left in the periodic calculator and the numbers are slightly different because of the monthly vs quarterly discounting.
Thank you all for the great responses to my workflow!
@mceleavey 's approach fits my need the best in keeping it simple since I don't need to iterate the whole way through all of the dates.
@Luke_C this is a great example of an iterative workflow that I will use as a reference for other projects I work on.
@apathetichell I've utilized your NPV input into my workflow as well, great work!