NPV and IRR in a table calculated with measures

[Adrian_1987]

Hello everyone,
Through various measurements I have obtained the following calculated table, where:
Column 1= Year, with connection to my Calendar table.
Column 2=Plate_Num, with connection to my product dimension table
Column 3=Cashflow. It is a measure that is a sum of other measures from several tables.

I need to get the NPV and IRR for each enrolment I filter, as what I get is not correct. For example, for the 1154 KVM Plate_Num, the NPV should be 15.623,63 € (k=0.09) and the IRR 10,19% and I don't know how to get it in PowerBI.
I would appreciate any help.

I attach the data. Thank you very much in advance.

Year	Plate_Num	Cashflow
2019	1154 KVM	-15.305,65
2020	1154 KVM	7.125,49
2021	1154 KVM	6.081,49
2022	1154 KVM	3.735,69
2023	1154 KVM	1.528,99

[Syk]

How are you calculating what the NPV should be? 

[Syk]

I ask because it looks like you're maybe just using the NPV function in excel without adding back the first year? Your NPV @ 9% discount is 317.98

[Adrian_1987]

Hello,
Thank you very much for your prompt reply.

In fact, the first year should not be taken into account when calculating the NPV, as this is when the investment is made and this money ‘does not require updating’. So for our example, the NPV=317.98 € for me is correct.

For the IRR, all values are necessary.

Thank you very much for your help.

[Syk]

I'm a bit confused, is 317.98 correct for you? Because that takes into account your capital investment (a summed total of the discounted cash flow in my screenshot from before).

[Adrian_1987]

Hello,
Yes, that's correct for me. In fact, I have checked it on several websites and even with chatgpt and I always get that result, as none of them take into account the first year.

[Syk]

Just making sure because you mention in your post that the NPV should be at 15,623. What is the result you're getting in Power BI?

[Adrian_1987]

Hello,
Sorry, you're totaly right, I got confused with the signs when I copied them.
The value of 317.98 € is good for the NPV.

Later, in another measure, I will add it to the investment of the first year, which would be (-15,305.65+317.98= -14,987.67).

Right now, I am using this measure:
NPV = XNPV(ProductsExpanded,[Total Cashfow], ProductsExpanded[Year],0.09).

And the result for this example is 3.146,95 €, which is incorrect, and I don't know why. I suspect it is because the formula takes the whole table, and should not take the first value, but I don't know how to do it. Remember that I must be able to filter by "Plate_Num".

Thanks

[Adrian_1987 · Accepted solution]

Hi,

Finally I found a solution. The DAX code is:

NPV =
VAR DiscountRate = 0.09 // 9% discount rate
VAR MinimumYear =
MIN(ProductsExpanded[Year]) // Calculates the minimum year for the filtered enrolment only
RETURN
SUMX(
ProductsExpanded,
DIVIDE(
ProductsExpanded[Total Cashflow],
POWER(1 + DiscountRate, ProductsExpanded[Year] - MinimumYear)
)
)

And for the IRR, the code is:

IRR =
VAR Flows =
SUMMARIZE(
ProductsExpanded,
ProductsExpanded[Year],
‘Cashflow’, SUM(ProductsExpanded[Total Cashflow])
) // Creates a table with years and cash flows summed by year
VAR Dates =
ADDCOLUMNS(
Flows,
‘Date’, DATE(ProductsExpanded[Year], 1, 1) // Assigns a date to each flow (January 1st of the corresponding year)
)
RETURN
XIRR(Dates, [Cashflow], [Date]) // Calculates the IRR using the XIRR function

Thanks!!!!