Macro Iteration Help

[msnyder]

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.

[Luke_C]

Hi @msnyder,

See attached. To make this work I did the following:

1. Before the macro, I used a formula tool to add EFF_DATE to the data, hardcoding 2021-01-01. You can change this at any point.
2. Within the macro itself, we perform the filter based on OUTDATE and EFF_DATE, summarize, and output that data.
3. At the same time, the unsummarized data goes to a formula tool that adds 3 months to the EFF_DATE, and processes the data again until no records remain. 

Let me know if this works or if you have any issues.

[atcodedog05]

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 🙂

[mceleavey · Accepted solution]

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.

[apathetichell]

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.

[msnyder]

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!