Macro Iteration Help

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.

PV Iteration Example.yxmd

Accepted answers

mceleavey

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,

mceleavey_solution.yxmd

msnyder.yxmc

mceleavey_solution.yxzp

All comments

Luke_C

Hi @msnyder,

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

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.
Within the macro itself, we perform the filter based on OUTDATE and EFF_DATE, summarize, and output that data.
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.

PV Iteration Example.yxzp

NPV.yxmc

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

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,

mceleavey_solution.yxmd

msnyder.yxmc

mceleavey_solution.yxzp

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.

property project.yxzp

the ususal pp.yxmd

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!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335