cancel
Showing results 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.

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

3 REPLIES 3
Super User

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

Owen Auger
Blog
Super User

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

Owen Auger
Blog
New Member

```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,```

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors