Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Roberto_Ribeiro
New Member

Calculate the discounted payback

 

Hello!
Please, I would like help to create a measure in dax to calculate the discounted payback indicator. I have the following data:
CAPEX$ 1.000.000
DISCOUNT RATE15%
  
PERIODCASH FLOW
1$ 200.000
2$ 203.000
3$ 189.000
4$ 220.000
5$ 198.000
6$ 200.004
7$ 203.006
8$ 210.555
9$ 200.876
10$ 220.000

 

3 REPLIES 3
OwenAuger
Super User
Super User

@Roberto_Ribei1  could you clarify what you mean by "broken payback", with an example?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

Hi @Roberto_Ribeiro 

I'm assuming you want to compute the Discounted Payback Period.

 

First, I suggest you set up your cash flow table with the capex as a negative cash flow in Period 0.

OwenAuger_0-1690846396046.png

In my example PBIX attached:

  • I have a measure that returns the discount rate. I have used a parameter with measure [Discount Rate Value]
  • I have not worried about a period or date dimension table, though it would be a good idea to create one.

The measures I created are:

 

Cash Flow Amount = 
SUM ( Cashflow[CASH FLOW] )
NPV to Date = 
VAR DiscountRate =
    [Discount Rate Value]
VAR CurrentPeriod =
    MAX ( Cashflow[PERIOD] )
-- XNPV treats 365 days one period for the purposes of calculation
VAR DaysPerPeriod =
    365
VAR PeriodToDate =
    FILTER ( ALL ( Cashflow[PERIOD] ), Cashflow[PERIOD] <= CurrentPeriod )
VAR NPV =
    XNPV (
        PeriodToDate,
        [Cash Flow Amount],
        Cashflow[PERIOD] * DaysPerPeriod,
        DiscountRate
    )
RETURN
    NPV
Discounted Payback Period = 
VAR DiscountRate =
    [Discount Rate Value]
VAR PaybackPeriod =
    MINX (
        VALUES ( Cashflow[PERIOD] ),
        VAR CurrentPeriod =
            Cashflow[PERIOD]
        VAR PeriodToDate =
            FILTER ( ALL ( Cashflow[PERIOD] ), Cashflow[PERIOD] <= CurrentPeriod )
        VAR NPV =
            [NPV to Date]
        RETURN
            -- If NPV to date >= 0, return the period, otherwise blank (which is ignored by MINX)
            IF ( NPV >= 0, Cashflow[PERIOD] )
    )
RETURN
    PaybackPeriod

 

Results look like this:

OwenAuger_1-1690846642762.png

Does something like this work for you?

 

There may be adjustments needed depending how you want the calculation to work if your dataset includes multiple investments.

 

Regards

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

 

Dear Owen Auger,

Thank you very much for the help. And I still have a challenge, which is to calculate the "broken" payback. That is, the result must be 9, something.

Sincerely,

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors