Forum Discussion

wjkolesar's avatar
wjkolesar
Icon for Advocate II rankAdvocate II
3 years ago
Solved

NPV Not Aggregating / Summing from Month to Year Correctly

Hi -  I am trying to calculate NPV allowing end users to select their Discount Rate and Date Range.   I can get the values correct at a monthly level when spanning multiple years (IE 2022 & 2023),...
Need Help
  • vanessafvg's avatar
    vanessafvg
    3 years ago

    thanks.

     

    so i think the problem is 2 fold.

     

    Firstly I do believe the number to be 734639.0506839028 is correct, not $727,405.  The issue with your measures (all the discount rate ones), all of them are set to currency with no decimal places.  Because you are using the power of the 1+ Discount rate which is a decimal you are using a lot of the accuracy because you are rounding.  So i set the discount rate to decimal places of 4, and when did that the sum of that table was correct in excel.   However your totals are stilll not working properly.

     

     

    second issue is that in order to get the correct total that is reflected when you sum it in excel, you need to iterate through each line to get your calculation  ie

     

    so I changed this calculation to this

     

    Billings - Discount Rate =
    SUMX (
    VALUES ( Billings[Date] ),
    DIVIDE (
    Billings[Billings - No Discount Rate],
    POWER (
    SELECTEDVALUE ( 'Rates Table'[1 + Discount Rate] ),
    [# of Days Divided by 365]
    )
    )
    )

     

    not quite sure what you were trying to achieve with the discount rate final but please check what i have done in discount rate.

     

    please see attached.

     

     

wjkolesar's avatar
wjkolesar
Icon for Advocate II rankAdvocate II
3 years ago

vanessafvg - sure, here is the sample data I am using, thanks!

 

DateBillings
11/30/2022 $               50,000.00
12/31/2022 $               75,000.00
1/31/2023 $               60,000.00
2/28/2023 $               50,000.00
3/31/2023 $               50,000.00
4/30/2023 $               75,000.00
5/31/2023 $               60,000.00
6/30/2023 $               65,000.00
7/31/2023 $               50,000.00
8/31/2023 $               75,000.00
9/30/2023 $               60,000.00
10/31/2023 $             100,000.00
11/30/2023 $               50,000.00
12/31/2023 $               60,000.00
vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
3 years ago

I will also need this

 

 SELECTEDVALUE ( 'Rates Table'[Value] ) )

this rates table; 

  • wjkolesar's avatar
    wjkolesar
    Icon for Advocate II rankAdvocate II
    3 years ago

    vanessafvg - sorry about that, this is just a table I created in PBI Desktop with the GenerateSeries function: 

     

    Rates Table = GENERATESERIES(0.0,.2025,0.0025)
     
    And then I added new column: 
    1 + Discount Rate = 1 + 'Rates Table'[Value]
    • vanessafvg's avatar
      vanessafvg
      Icon for Community Champion rankCommunity Champion
      3 years ago

      apologies but im actually struggling to recreate your scenario

       

      i am missing how you get 

       

      no of days

      no of days divided by 365 

      etc, in order to replicate the issue i need your whole file, can you provide it?