Forum Discussion

charles_o's avatar
charles_o
Helper I
8 years ago
Solved

XNPV Aggregate vs Individual

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'[Dat...
Need Help
  • charles_o's avatar
    charles_o
    8 years ago

    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.

     

MFelix's avatar
MFelix
Super User
8 years ago

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's avatar
charles_o
Helper I
8 years ago

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's avatar
    charles_o
    Helper I
    8 years ago

    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.