[et_pb_section fb_built=”1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_row _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The XIRR function<\/a> in Excel calculates the internal rate of return, or IRR, of a series of cash flows. The function is designed specifically to handle irregular cash flows that don\u2019t follow a consistent time period.<\/p>\n This makes the XIRR function very useful in financial calculations!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=XIRR(values, dates, [guess])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The function works by iteration. IRR is found by finding the rate that will make the net present value (NPV) equal zero.<\/p>\n The function guesses a number, runs the calculation, and then refines its guess. It does this until it finds the correct IRR.<\/p>\n The guess<\/b> parameter helps the function by giving it a starting estimate. If no guess is specified, it starts at 10% and performs guess-and-check iterations from there.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The XIRR function has some complicated math behind it, but it\u2019s extremely simple to use.<\/p>\n You basically list out all the dates in one column, and all the cash flows in another. Then use the function to find the IRR (you typically won\u2019t even need to input a guess).<\/p>\n In this example, I have a project that will require a $600 outlay at the beginning, then $400 on two separate dates. Because these cash flows don\u2019t follow a consistent time period (in which case you could use the IRR function), it\u2019s a perfect candidate for the XIRR function!<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/1.-XIRR-example-1.png” alt=”XIRR function Excel example 1″ title_text=”1. XIRR example 1″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I use the XIRR function here by typing =XIRR(B2:B4,A2:A4)<\/b>. The function tells me that the internal rate of return for this project is 19.88%.<\/p>\n To ensure you have a good understanding of how to use XIRR, I\u2019ll show one more example. This time, the project involves a $15,000 outlay at the start, then income over the next few years at the specified dates.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/2.-XIRR-example-2.png” alt=”XIRR function Excel example 2″ title_text=”2. XIRR example 2″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When I type =XIRR(B2:B6,A2:A6)<\/b> the function shows that the IRR for this project is 9.41%.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n That’s how to use the XIRR<\/a> function in Excel!<\/p>\nSyntax<\/h3>\n
\n
How to use the XIRR Excel function<\/h3>\n
Tips when using XIRR in Excel<\/h4>\n
\n
Final thoughts<\/h3>\n