An Easy IRR Google Sheets Guide for 2024 [Examples]

It’s hard to talk about investments and returns without visiting the topic of Internal Rate of Return, or IRR. Manually computing the IRR for a series of cash flows involves trial and error. You have to make multiple guesses till you reach an optimum result. This can be both complicated and time-consuming.

However, it’s quite quick and easy to compute the IRR in Google Sheets. In this IRR Google Sheets tutorial we will show you how to use the IRR function to compute the Internal Rate of Return for a given series of cash flows.

What is IRR in Google Sheets?

As mentioned before, IRR is an abbreviation for Internal Rate of Return. It gives the rate of return on a series of investments.This means that if you have a series of cash flows relating to a given project and you want to decide if the investment is going to be profitable then IRR can be a helpful indicator.

In this way the IRR function helps in financial analysis problems, giving us insights into investment plans, so we can quickly and easily choose the best course of action.

Syntax for the IRR Function

IRR calculation in Google Sheets uses the following syntax:

=IRR (cashflow_values, [rate_guess])

Here,

• cashflow_values is an array or reference to a range of cells containing cash flow corresponding to the investment
• rate_guess is an estimate for expected IRR. It is a value that you guess is close to the actual IRR. This parameter is optional.

Note: The default value for the rate_guess parameter is 0.1 or 10%.

What the IRR function will do in this case is use an iterative method to compute the optimum IRR value, starting with the guess value. It will keep iterating and making new guesses, all the while adjusting the IRR value till it gets an accurate result within the range of 0.00001%.

Finally, the function will return the Internal Rate of Returns corresponding to the given cash flow amounts (both negative and positive). The IRR returned will be a percentage value.

The IRR function iterates up to a maximum of 20 times. If at the end of 20 tries, the results don’t converge, the IRR function returns a #NUM! Error.

In this case, you can go ahead and try again with a different guess value in the second parameter of the formula.

Note: If the total income generated is less than the total expenditure, the IRR equation will return a negative value.

How to Calculate IRR in Google Sheets

Calculation of IRR involves computing the interest rate corresponding to a zero Net Present Value (NPV). So to find out the IRR for a given investment, you need to guess different rates until you reach one that gives you a zero Net Present Value.

Tip: We have a full guide on Net Present Value too.

This trial and error requires a lot of time and patience, which can prove to be quite inefficient when done by a human.

However, with the IRR formula in Google Sheets, these computations take place within seconds.

A Real-World Example for Using IRR in Google Sheets

Let us see how to use the IRR function to compute the Internal Rate of Return for the following series of cash flows:

Notice that the dates for individual transactions are periodic.

Moreover, we can see that the cash flow for the first month is a negative number. Negative values represent money going out, or payments made, while positive values represent money coming in, or income generated.

Note: If your cash flow values are formatted in the ‘Financial’ or ‘Accounting’ format, then money going out is represented in brackets, rather than as a negative number.

Google Sheets Internal Rate of Return – Basic Example

In the above example the IRR can be calculated using the following formula:

=IRR(B2:B6)

Here’s the result we get:

Points to Remember When Using IRR Formula

The IRR function can be really helpful as it takes away the hassle of guessing and adjusting values. However, there are a few points that you need to remember when applying the IRR function in Google Sheets:

• Make sure that your cash flow transactions include at least one negative and one positive value. Otherwise the function will return a #NUM! Error.
• The IRR function assumes that the cash flow values are sequential and periodic in nature.
• Any text or logical value in the IRR function inputs are ignored by the function.

Visit this link to learn how to fix parse errors in Google Sheets.

When Should You Not Use IRR in Google Sheets?

There are two main reasons why an IRR formula may not be meaningful. 

The first is when your business has more expenses than income which results in a negative cash flow. This will result in a negative IRR figure which serves no purpose.

The other time IRR calculations are not useful is when there are several different investments of different maturity. In this case, the cash is not invested at an identical rate which is the opposite of what an IRR calculation assumes.

Similar Functions to Use Instead

Here are some other functions that can also calculate IRR if you need them to:

• XIRR
• NPV
• SUMXIRR
• SUMNPV

IRR Formula Google Sheets FAQ

How Do I Calculate IRR in Google Sheets?

Here is how to make a simple IRR calculator in Google Sheets

1. Enter the cash flow data into a sheet
2. In an appropriate empty cell, type =IRR(
3. Click and drag over the cells with the highlighted data
4. Press Enter

How Do You Calculate IRR Manually?

To work out IRR manually you must first work out the NPV, or use estimations. Here’s a full guide to calculating IRR and NPV manually.

Are NPV and IRR the Same?

No, the IRR is the interest rate at which the Net Present Value will reach $0

Conclusion

In this tutorial, we covered the IRR Google Sheets function, what it does, and how it can be used to compute the Internal Rate of Return for a project or investment, based on a series of cash flows. We hope you found our explanations useful and easy to follow.