topic Re: Calculate the discounted payback in DAX Commands and Tips

Calculate the discounted payback

Roberto_Ribeiro — Mon, 31 Jul 2023 20:47:49 GMT

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

Re: Calculate the discounted payback

OwenAuger — Mon, 31 Jul 2023 23:39:45 GMT

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

Re: Calculate the discounted payback

Roberto_Ribei1 — Fri, 25 Aug 2023 13:17:04 GMT

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,

Re: Calculate the discounted payback

OwenAuger — Mon, 28 Aug 2023 11:31:46 GMT

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