Forum Discussion
Helper IIHow to calculate IRR in Power Bi
Hi All,
We have XIRR function in Power BI, But need to calculate IRR, is ther any option calculating IRR?
Thanks,
Sanjay
5 Replies
- parry2k
Super User
sanjaymithran did you had a chance to look at this video https://www.youtube.com/watch?v=rHZ4hj5eh10
👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️
sanjaymithranThanks Parry, Yes seen that but not helped for my scenario
- parry2k
sanjaymithran I guess then you need to put together what your requirement is. Maybe put data in an excel sheet and explain the calculations you are looking for.
- sanjaymithran
Hi Parry,
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
HI,
I am also trying to find a way to calculate the IRR in power Bi, but as Sanjay already said it doenst work for me eighter.
Who can help?
