The IRR formula is usually used in a financial modeling or financial investment context. For example, you can forecast and evaluate your business performance by calculating the return rate based on your initial investment and previous net income. On the other hand, the IRR formula can also help you choose what to invest in; a higher IRR rate equals a better investment opportunity.<\/p>\n
In this article, we\u2019ll explore what the IRR formula is, how it works, and how you can employ it in your own cash flow data.<\/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
What is the IRR formula?<\/h2>\n
The IRR formula stands for \u2018Internal Rate of Return\u2019. This formula calculates the interest rate received for an investment consisting of both outgoing payments and income payments that occur at regular periods. It doesn\u2019t consider the external factors, such as inflation or the cost of capital. Hence the term \u2018internal\u2019.<\/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
Therefore, you can use the IRR formula with the following:<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- Periodic payments:<\/b> regular payments that come in, such as monthly or annually.<\/li>\n
- Uneven cash flow:<\/b> you don\u2019t have to have even values at every interval for the IRR formula to work<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h3>\n
The IRR formula uses the following syntax to calculate your return:<\/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”]=IRR(cashflow_values, [rate_guess])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- Cashflow_values<\/b> = the range of cells that contain the outgoing and incoming payments relating to your return of investment<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
\u25cb<\/span> The range must contain at least one positive value (income) and one negative value (payment).
\u25cb<\/span> Make sure the values are in the correct order of cash flow
\u25cb<\/span> Your range must include numbers only. Any other values, such as text or empty cells, will be ignored.<\/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
- \n
- [rate_guess]<\/b> = optional. Estimate of the internal rate of return.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
\u25cb<\/span> For the majority of instances, you don\u2019t need to provide a guess value for the IRR calculation. If so, the default value will be 0.1 (10%).
\u25cb<\/span> You may receive a #NUM!<\/b> error value if the [rate_guess]<\/b> doesn\u2019t work. There are 2 reasons this may occur:<\/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
- \n
- \n
- \n
- You have not entered at least one negative and one positive value.<\/li>\n
- The formula couldn\u2019t find an accurate enough result. IRR cycles through the calculation until the result is accurate within 0.00001%. If the formula cannot find a result that works after 20 tries, the #NUM!<\/b> value will show.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
\u25cb<\/span> If the result isn\u2019t close to what you expected, try another [rate_guess]<\/b> value for a different result.<\/p>\n
[\/et_pb_text][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”255434″ theme_builder_area=”post_content”]<\/p>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\nOne tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n
Find out how<\/a><\/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
How to use the IRR formula in Google Sheets<\/h3>\n
Now that we understand what the IRR formula is and how it works, let\u2019s explore how to use it in real-life situations.<\/p>\n
For our example, let\u2019s take a look at the following cash flow data in Google Sheets.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IRR.png” alt=”IRR formula 1″ title_text=”IRR” align=”center” _builder_version=”4.16″ _module_preset=”default” 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
The spreadsheet above shows the cash flow over the past 5 years after starting a business. The first shows the initial investment, followed by overall income received at the end of each year.<\/p>\n
Let\u2019s say we want to calculate the internal rate of return of our business investment across all five years, without a rate guess. We\u2019d input the following:<\/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”]=IRR(A2:A7)[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IRR-\u2013-1.png” alt=”IRR formula 2″ title_text=”IRR \u2013 1″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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
As you can see, our interest rate is 34%. We can check the accuracy of this value by substituting it in the NPV formula. The IRR and NPV (Net Present Value) are closely linked to one another, as the rate of return calculated by IRR is the interest rate corresponding to a 0 NPV. Check out our blog post to learn more about how to use the NPV formula in Google Sheets<\/a>.<\/p>\n
So, in order to check our IRR result, we can input the IRR value (located in cell D2) into the NPV formula on the same data and see if we get a final result of 0.<\/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”]=NPV(D2,A2:A7)[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IRR-\u2013-2.png” alt=”IRR formula 3″ title_text=”IRR \u2013 2″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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
As you can see here, our NPV value did indeed come back as 0, which means the IRR value is correct!<\/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
IRR formula<\/h3>\n
And there you have it! The IRR formula works in exactly the same way in Excel as it does in Google Sheets. Always remember that this formula works on cash flows with periodic payments; if you have a cash flow based on random payments, check out our article on how to use the XIRR formula for Google Sheets<\/a> instead.<\/p>\n
Alternatively, if you would like to learn more about the numerous functions in Google Sheets that can help streamline your calculation processes, discover our related blog posts below!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
If you\u2019re managing the returns of your periodic cash flows in Google Sheets, then using the IRR formula can not only help to streamline your calculations process but also guarantee accurate results. The IRR formula is usually used in a financial modeling or financial investment context. For example, you can forecast and evaluate your business […]<\/p>\n","protected":false},"author":41,"featured_media":37029,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Let's assume that we have a series of regularly spaced cash flows of an investment. And now we want to calculate the internal rate of return or annualized yield for that investment. Without having to do the complex math ourselves, that is. Thankfully, we have IRR<\/a> formula in Google Sheets - which means \"internal rate of return\". It doesn't consider the external factors, such as inflation or the cost of capital. Hence the term \u2018internal\u2019. The IRR<\/a> formula calculates the rate or the return for periodic cash flows, whereas the XIRR<\/a> formula does so for non-periodic cash flows.\n
Syntax<\/h3>\nIRR(cashflow_amounts, [rate_guess])<\/strong>\n
- \n \t
- cashflow_amounts<\/strong> - is a group or range or array of incomes or payments corresponding to the investment.<\/li>\n \t
- rate_guess<\/strong> - [OPTIONAL \u2013 0.1 by default] \u2013 is an estimated internal rate of return.<\/li>\n<\/ul>\nPlease note that the IRR formula in Google Sheets assumes that the cash flows happen in both directions. Therefore the cashflow_amounts<\/strong> must contain at least one negative and one positive cash flow to calculate the rate of return. A negative value indicates that the buyer is investing, while a positive value indicates that the buyer is yielding a return.\n
Usage: IRR formula in Google Sheets<\/h3>\nNow that we have established the purpose of IRR formula and its syntax, it's time to delve into actual examples. Because they help us internalize the concepts much better than theory. Please consider the screenshot below.\n\n
![\"IRR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/05\/IRR-formula-Illustration-Frame-1.png\")
\n\nAs demonstrated in the examples above, the cash flows values can either be numeric values. Or we can also use a reference to the range of cells that hold the cash flow values.\n\nIf we ever try the IRR formula in Google Sheets only with one directional cash flows, it returns a #NUM! error. That is because the IRR formula needs both positive and negative cash flows for it to be able to calculate the rate of return.\n\n![\"IRR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/05\/IRR-formula-Illustration-2.png\")
","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-8400","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/8400","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/41"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=8400"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/8400\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/37029"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=8400"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=8400"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=8400"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} - rate_guess<\/strong> - [OPTIONAL \u2013 0.1 by default] \u2013 is an estimated internal rate of return.<\/li>\n<\/ul>\nPlease note that the IRR formula in Google Sheets assumes that the cash flows happen in both directions. Therefore the cashflow_amounts<\/strong> must contain at least one negative and one positive cash flow to calculate the rate of return. A negative value indicates that the buyer is investing, while a positive value indicates that the buyer is yielding a return.\n
- cashflow_amounts<\/strong> - is a group or range or array of incomes or payments corresponding to the investment.<\/li>\n \t
- \n
- [rate_guess]<\/b> = optional. Estimate of the internal rate of return.<\/li>\n<\/ul>\n
- Uneven cash flow:<\/b> you don\u2019t have to have even values at every interval for the IRR formula to work<\/li>\n<\/ul>\n