Apply NPV Formula anteryx

[daviddiaz159]

I have a dataset where I need to apply the following formula in Excel:

=NPV(H18, O18:$O$136)

=NPV(H20, O20:$O$136)

=NPV(H21, O21:$O$136), etc.

I want to implement this calculation in Alteryx, but I'm unsure how to make it correctly reference the range. Could you guide me on the best approach to replicate this functionality in Alteryx?

Thanks in advance for your help!

[apathetichell]

hey --- so this should be straight forward --- but i'd recommend diving into the underlying math of NPV. NPV is a cash flow discounted by a periodic rate ^ period number. To do this in Alteryx you need to 1) use recordID (or tiletool for multiple NPV in the same dataset) to create the period 2) create the period rate in a column. 3) create the divisor as (1/pow(1+[periodrate]),[rowID]) --- or whatever 4) multiple your cash flow by 3.

now use summarie tool or running total to get your NPV.

[daviddiaz159]

Good morning 

Thank you very much for your help. If you could assist me with an example, it would be wonderful to have a guide for my process.

[RolandSchubert]

Hi @daviddiaz159 ,

maybe this can be helpful:

Spoiler

You need the list of values (in the workflow provided in the lower text input tool) and the interest rate (the upper text input tool). Both are handed over to the macro.

Within the macro, I add RecordIds (=> Row Numbers) and combine ech RecordId with each RecordId (the Append Fields tool). Then I filter out all previous rows to create a list of all following values for each record. 

The Summarize Tool provides a NPV calculation, it's grouped by RecordId leading to one NPV for each row. Sorted and done.

Hope this is helpful - let me know if you have any additional questions.

Best,

Roland

[apathetichell]

@daviddiaz159 --- no Altteryx license === no samples. Sorry. Having said that --- um. just follow my instructions. The key of which is probably 'review NPV math' --- most of the time people have an issue implementing it's the math --- like not recognizing what the periodic rate should be.

[daviddiaz159]

Thank you very much for your help all