XNPV Aggregate vs Individual

[charles_o]

Hi guys-

I have data that looks like this

In the XNPV Column, I am using this formula with 10% (.1) as the discount rate

Column = xnpv('Table 1', 'Table 1'[Profit], 'Table 1'[Dates], .1)

My problem:  The XNPV is shown as a sum of the XNPVs of all the projects. I want it to be for the individual project, so specific to that individual row.

Any suggestions?

Thanks!

[MFelix]

Hi @charles_o,

Try to createthe following measure:

XNPV = CALCULATE( SUM(Table[Profit]) * (0,1))

Since this is a measure it's based on context so on the project level it will give you the the discount value.

Regards,

MFelix

[charles_o]

The discounted valued needs to take into account the date- else I would just multiply Profit*(1-.1)

The npv formula as written out on paper for example would be like this:

Profit/((1+.1)^((Date- todays_date)/365))

so for example if I plan to make $100 a year from today:

$100/((1+.1)^((2/13/2019-2/13/2018)/365)

$100/((1+.1)^(365/365)

$100/(1.1) = $90

But if it is half a year from today it would be this

$100/(1.1)^(182.5/365)

$100/(1.0488)= $95.346

[charles_o · Accepted solution]

Alright guys- figured out a way that worked for me. Had to split numerator and denominator to make it work.

Made two columns one like this

XNPV DENOMINATOR = power(1.1,('Table'[Dates]-TODAY())/365)

= (1+i)^((Future Date-today)/365)

XNPV = CALCULATE(sum('Table'[Margin]))/'Table'[XNPV Denominator]

Not the fastest work around but works and got me what I needed.

Hope somebody else comes across this while browsing Google or something. This is pretty straightforward and considering we are discussing BI software, surprised that this does not have more optionality.