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'[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!

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.

     

3 Replies

  • 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.