Summary XNPV formula

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!

XNPV Formula.yxzp

Expression

Accepted answers

danilang

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

XPNV.yxmd

All comments

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

Dynamic XNPV Formula.yxmd

danilang

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

XPNV.yxmd

SamDesk

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

Sam

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335