Summary XNPV formula

[cireost]

Hi - I am trying to calculate XNPV using the formula function of Summary tool and trying to figure out how to dynamically change the Finance Rate? The input source will have different dates, cash flows, and rate each time. 

Alternatively, I could use a formula tool and use the formula: FinanceXNPV(Rate, Value1, Date1, Value2, Date2): Net Present Value of an investment with dates

However, I am unsure of how to make this all dynamic with the formula tool.

See workflow attached for example.

Thanks!

[SamDesk]

Hello @cireost,

When we look at the formula for XNPV:

Spoiler

We can see that it is a Sum of a rather simple formula. The only difficulty here in making this dynamic is giving the start date to every row but we can accomplish this easily with the Multi-Row Tool. Once we have done this we can create the de-aggregated formula for XNPV. Finally, we Sum these values to give the final XNPV.

Hope this helps!

Sam :)

[danilang · Accepted solution]

hi @cireost 

Similar to @SamDesk but the rate is added separately and the start date is calculated as opposed to assuming the dates are in order

Dan

[SamDesk]

Nice @danilang, so much simpler to calculate Min Date using summarise and append!

Sam :)