Forum Discussion
Helper IIHow to calculate IRR using DAX
Hi All,
XIRR=XIRR(Data,Data[Cash Flow],Data[DATE]) working fine we have function getting same as mentioned below.
I tried as explained in that video getting 0% but in excel 0.45%
IRR =
XIRR (
ADDCOLUMNS (
'Data',
"Date365", CALCULATE (
COUNTROWS ( 'Data' ),
'Data'[DATE] < EARLIER ( Data[DATE]),
ALL ( 'Data' )
)
* 365
+ CALCULATE ( MIN ( Data[DATE] ), ALL ( 'Data') )
),
Data[Cash Flow],
[Date365]
) )
| SPONSOR | DATE | Year | Month | Cash Flow | IRR | XIRR | |
| Sponsor1 | 1/1/2000 0:00 | 0 | 0 | -509060.5 | 0.45% | 0.80% | |
| Sponsor1 | 1/1/2023 0:00 | 1 | 1 | 9789.92 | |||
| Sponsor1 | 2/1/2023 0:00 | 1 | 2 | 9573.44 | |||
| Sponsor1 | 3/1/2023 0:00 | 1 | 3 | 9361.79 | |||
| Sponsor1 | 4/1/2023 0:00 | 1 | 4 | 9154.88 | |||
| Sponsor1 | 5/1/2023 0:00 | 1 | 5 | 8952.62 | |||
| Sponsor1 | 6/1/2023 0:00 | 1 | 6 | 8754.88 | |||
| Sponsor1 | 7/1/2023 0:00 | 1 | 7 | 8561.59 | |||
| Sponsor1 | 8/1/2023 0:00 | 1 | 8 | 8372.61 | |||
| Sponsor1 | 9/1/2023 0:00 | 1 | 9 | 8187.85 | |||
| Sponsor1 | 10/1/2023 0:00 | 1 | 10 | 8007.23 | |||
| Sponsor1 | 11/1/2023 0:00 | 1 | 11 | 7830.64 | |||
| Sponsor1 | 12/1/2023 0:00 | 1 | 12 | 7658.02 | |||
| Sponsor1 | 1/1/2024 0:00 | 2 | 1 | 7489.25 | |||
| Sponsor1 | 2/1/2024 0:00 | 2 | 2 | 7391.31 | |||
| Sponsor1 | 3/1/2024 0:00 | 2 | 3 | 7294.71 | |||
| Sponsor1 | 4/1/2024 0:00 | 2 | 4 | 7199.45 | |||
| Sponsor1 | 5/1/2024 0:00 | 2 | 5 | 7105.49 | |||
| Sponsor1 | 6/1/2024 0:00 | 2 | 6 | 7012.8 | |||
| Sponsor1 | 7/1/2024 0:00 | 2 | 7 | 6921.4 | |||
| Sponsor1 | 8/1/2024 0:00 | 2 | 8 | 6831.23 | |||
| Sponsor1 | 9/1/2024 0:00 | 2 | 9 | 6742.31 | |||
| Sponsor1 | 10/1/2024 0:00 | 2 | 10 | 6654.57 | |||
| Sponsor1 | 11/1/2024 0:00 | 2 | 11 | 6568.05 | |||
| Sponsor1 | 12/1/2024 0:00 | 2 | 12 | 6482.69 | |||
| Sponsor1 | 1/1/2025 0:00 | 3 | 1 | 6398.52 | |||
| Sponsor1 | 2/1/2025 0:00 | 3 | 2 | 6348.35 | |||
| Sponsor1 | 3/1/2025 0:00 | 3 | 3 | 6298.69 | |||
| Sponsor1 | 4/1/2025 0:00 | 3 | 4 | 6249.54 | |||
| Sponsor1 | 5/1/2025 0:00 | 3 | 5 | 6200.88 | |||
| Sponsor1 | 6/1/2025 0:00 | 3 | 6 | 6152.69 | |||
| Sponsor1 | 7/1/2025 0:00 | 3 | 7 | 6104.98 | |||
| Sponsor1 | 8/1/2025 0:00 | 3 | 8 | 6057.76 | |||
| Sponsor1 | 9/1/2025 0:00 | 3 | 9 | 6010.98 | |||
| Sponsor1 | 10/1/2025 0:00 | 3 | 10 | 5964.67 | |||
| Sponsor1 | 11/1/2025 0:00 | 3 | 11 | 5918.82 | |||
| Sponsor1 | 12/1/2025 0:00 | 3 | 12 | 5873.42 | |||
| Sponsor1 | 1/1/2026 0:00 | 4 | 1 | 5828.43 | |||
| Sponsor1 | 2/1/2026 0:00 | 4 | 2 | 5778.2 | |||
| Sponsor1 | 3/1/2026 0:00 | 4 | 3 | 5728.42 | |||
| Sponsor1 | 4/1/2026 0:00 | 4 | 4 | 5679.05 | |||
| Sponsor1 | 5/1/2026 0:00 | 4 | 5 | 5630.16 | |||
| Sponsor1 | 6/1/2026 0:00 | 4 | 6 | 5581.65 | |||
| Sponsor1 | 7/1/2026 0:00 | 4 | 7 | 5533.6 | |||
| Sponsor1 | 8/1/2026 0:00 | 4 | 8 | 5485.98 | |||
| Sponsor1 | 9/1/2026 0:00 | 4 | 9 | 5438.77 | |||
| Sponsor1 | 10/1/2026 0:00 | 4 | 10 | 5391.97 | |||
| Sponsor1 | 11/1/2026 0:00 | 4 | 11 | 5345.59 | |||
| Sponsor1 | 12/1/2026 0:00 | 4 | 12 | 5299.6 | |||
| Sponsor1 | 1/1/2027 0:00 | 5 | 1 | 5254.03 | |||
| Sponsor1 | 2/1/2027 0:00 | 5 | 2 | 5219.4 | |||
| Sponsor1 | 3/1/2027 0:00 | 5 | 3 | 5184.97 | |||
| Sponsor1 | 4/1/2027 0:00 | 5 | 4 | 5150.8 | |||
| Sponsor1 | 5/1/2027 0:00 | 5 | 5 | 5116.88 | |||
| Sponsor1 | 6/1/2027 0:00 | 5 | 6 | 5083.17 | |||
| Sponsor1 | 7/1/2027 0:00 | 5 | 7 | 5049.7 | |||
| Sponsor1 | 8/1/2027 0:00 | 5 | 8 | 5016.46 | |||
| Sponsor1 | 9/1/2027 0:00 | 5 | 9 | 4983.44 | |||
| Sponsor1 | 10/1/2027 0:00 | 5 | 10 | 4950.64 | |||
| Sponsor1 | 11/1/2027 0:00 | 5 | 11 | 4918.09 | |||
| Sponsor1 | 12/1/2027 0:00 | 5 | 12 | 4885.75 | |||
| Sponsor1 | 1/1/2028 0:00 | 6 | 1 | 4853.62 | |||
| Sponsor1 | 2/1/2028 0:00 | 6 | 2 | 4821.73 | |||
| Sponsor1 | 3/1/2028 0:00 | 6 | 3 | 4790.04 | |||
| Sponsor1 | 4/1/2028 0:00 | 6 | 4 | 4758.59 | |||
| Sponsor1 | 5/1/2028 0:00 | 6 | 5 | 4727.35 | |||
| Sponsor1 | 6/1/2028 0:00 | 6 | 6 | 4696.34 | |||
| Sponsor1 | 7/1/2028 0:00 | 6 | 7 | 4665.53 | |||
| Sponsor1 | 8/1/2028 0:00 | 6 | 8 | 4634.91 | |||
| Sponsor1 | 9/1/2028 0:00 | 6 | 9 | 4604.51 | |||
| Sponsor1 | 10/1/2028 0:00 | 6 | 10 | 4574.33 | |||
| Sponsor1 | 11/1/2028 0:00 | 6 | 11 | 4544.35 | |||
| Sponsor1 | 12/1/2028 0:00 | 6 | 12 | 4514.58 | |||
| Sponsor1 | 1/1/2029 0:00 | 7 | 1 | 4485 | |||
| Sponsor1 | 2/1/2029 0:00 | 7 | 2 | 4456.19 | |||
| Sponsor1 | 3/1/2029 0:00 | 7 | 3 | 4427.57 | |||
| Sponsor1 | 4/1/2029 0:00 | 7 | 4 | 4399.16 | |||
| Sponsor1 | 5/1/2029 0:00 | 7 | 5 | 4370.92 | |||
| Sponsor1 | 6/1/2029 0:00 | 7 | 6 | 4342.89 | |||
| Sponsor1 | 7/1/2029 0:00 | 7 | 7 | 4315.02 | |||
| Sponsor1 | 8/1/2029 0:00 | 7 | 8 | 4287.34 | |||
| Sponsor1 | 9/1/2029 0:00 | 7 | 9 | 4259.85 | |||
| Sponsor1 | 10/1/2029 0:00 | 7 | 10 | 4232.53 | |||
| Sponsor1 | 11/1/2029 0:00 | 7 | 11 | 4205.39 | |||
| Sponsor1 | 12/1/2029 0:00 | 7 | 12 | 4178.45 | |||
| Sponsor1 | 1/1/2030 0:00 | 8 | 1 | 4151.68 | |||
| Sponsor1 | 2/1/2030 0:00 | 8 | 2 | 4125.11 | |||
| Sponsor1 | 3/1/2030 0:00 | 8 | 3 | 4098.69 | |||
| Sponsor1 | 4/1/2030 0:00 | 8 | 4 | 4072.44 | |||
| Sponsor1 | 5/1/2030 0:00 | 8 | 5 | 4046.41 | |||
| Sponsor1 | 6/1/2030 0:00 | 8 | 6 | 4020.5 | |||
| Sponsor1 | 7/1/2030 0:00 | 8 | 7 | 3994.78 | |||
| Sponsor1 | 8/1/2030 0:00 | 8 | 8 | 3969.25 | |||
| Sponsor1 | 9/1/2030 0:00 | 8 | 9 | 3943.87 | |||
| Sponsor1 | 10/1/2030 0:00 | 8 | 10 | 3918.65 | |||
| Sponsor1 | 11/1/2030 0:00 | 8 | 11 | 3893.6 | |||
| Sponsor1 | 12/1/2030 0:00 | 8 | 12 | 3868.74 | |||
| Sponsor1 | 1/1/2031 0:00 | 9 | 1 | 3844.02 | |||
| Sponsor1 | 2/1/2031 0:00 | 9 | 2 | 3819.47 | |||
| Sponsor1 | 3/1/2031 0:00 | 9 | 3 | 3795.1 | |||
| Sponsor1 | 4/1/2031 0:00 | 9 | 4 | 3770.86 | |||
| Sponsor1 | 5/1/2031 0:00 | 9 | 5 | 3746.79 | |||
| Sponsor1 | 6/1/2031 0:00 | 9 | 6 | 3722.89 | |||
| Sponsor1 | 7/1/2031 0:00 | 9 | 7 | 3699.16 | |||
| Sponsor1 | 8/1/2031 0:00 | 9 | 8 | 3675.55 | |||
| Sponsor1 | 9/1/2031 0:00 | 9 | 9 | 3652.13 | |||
| Sponsor1 | 10/1/2031 0:00 | 9 | 10 | 3628.84 | |||
| Sponsor1 | 11/1/2031 0:00 | 9 | 11 | 3605.72 | |||
| Sponsor1 | 12/1/2031 0:00 | 9 | 12 | 3582.75 | |||
| Sponsor1 | 1/1/2032 0:00 | 10 | 1 | 3559.94 | |||
| Sponsor1 | 2/1/2032 0:00 | 10 | 2 | 3537.27 | |||
| Sponsor1 | 3/1/2032 0:00 | 10 | 3 | 3514.73 | |||
| Sponsor1 | 4/1/2032 0:00 | 10 | 4 | 3492.36 | |||
| Sponsor1 | 5/1/2032 0:00 | 10 | 5 | 3470.12 | |||
| Sponsor1 | 6/1/2032 0:00 | 10 | 6 | 3448.06 | |||
| Sponsor1 | 7/1/2032 0:00 | 10 | 7 | 3426.12 | |||
| Sponsor1 | 8/1/2032 0:00 | 10 | 8 | 3404.32 | |||
| Sponsor1 | 9/1/2032 0:00 | 10 | 9 | 3382.67 | |||
| Sponsor1 | 10/1/2032 0:00 | 10 | 10 | 3361.18 | |||
| Sponsor1 | 11/1/2032 0:00 | 10 | 11 | 3339.83 | |||
| Sponsor1 | 12/1/2032 0:00 | 10 | 12 | 3318.6 |
I loaded your Data table exactly as in your post, and tried your IRR measure, and did get a result of 0.45%.
See attached PBIX.
To confirm, do you want to replicate the behaviour of the Excel IRR function, which takes an ordered list of cashflows and assumes they occur at 1-year intervals (in this case by ignoring the DATE column)?
You can also write this more simply as something like this:
IRR 2 = XIRR ( 'Data', Data[Cash Flow], RANK ( DENSE, ALL ( 'Data'[DATE] ) ) * 365 )The interval between the Date values is important, rather than their magnitude, so there is no need to add the minimum date.
Please post back if needed.
Regards
2 Replies
- OwenAuger
Super User
I loaded your Data table exactly as in your post, and tried your IRR measure, and did get a result of 0.45%.
See attached PBIX.
To confirm, do you want to replicate the behaviour of the Excel IRR function, which takes an ordered list of cashflows and assumes they occur at 1-year intervals (in this case by ignoring the DATE column)?
You can also write this more simply as something like this:
IRR 2 = XIRR ( 'Data', Data[Cash Flow], RANK ( DENSE, ALL ( 'Data'[DATE] ) ) * 365 )The interval between the Date values is important, rather than their magnitude, so there is no need to add the minimum date.
Please post back if needed.
Regards
sanjaymithranHi OwenAuger,
Thanks for reply,the below one is detail data for which i posted yesteday is summary,same formula apply here not getting correct result its show 0% instead of 0.45% we have country,sponsor and Launch date slicer
Not able to attach file will split send agail