Calculate the discounted payback

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.

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:

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

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