Calculate the discounted payback

[Roberto_Ribeiro]

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 RATE	15%
PERIOD	CASH 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

[OwenAuger]

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

[OwenAuger]

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]

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,