Learn the Use of the IRR Function in Google Sheets

How to easily calculate the internal rate of return of an investment. The answer is using spreadsheets like Google Sheets or Excel. We can use the IRR function in Google Sheets or Excel to calculate the internal rate of return of periodic cash flows.

Here I am going to use Google Sheets to explain the IRR financial function. The usage is ‘almost’ the same in Excel too.

At one point I found one difference in the usage of this function in these applications. I’ll touch that at the end part but don’t expect a detailed comparison.

Tip: When you calculate the net present value (NPV) of periodic future cash flows from a project, if the discount rate (it’s one of the parameters in the NPV calculation) is replaced by IRR, the NPV would become zero.

Calculating the Internal rate of return is important as we can use it to estimate the profitability of potential investments.

Before calculating IRR, make sure that the cash flows are periodic like monthly or annually. The cashflow amount can vary.

IRR Function Syntax, Arguments, and Example in Google Sheets

Syntax

IRR(cashflow_amounts, [rate_guess])

Arguments

cashflow_amounts – An array or range (cell reference) containing the income/payments in connection with the investment. The range must contain at least one -ve and one +ve cash flow to calculate the IRR.

rate_guess – It’s an estimate (guess) for what the internal rate of return will be. In most cases, you can exclude this parameter. By default, Google Sheets will take 10% (0.1) as the rate_guess to calculate the IRR.

IRR Function Example in Google Sheets

For example, project-I requires an initial investment (expense from the owner’s perspective) to the tune of -$7000 (cell B2).

From this project, we are expecting cash flows (income from the owner’s perspective) of $2500 (cell B3), $4500 (cell B4), $1000 (cell B5), and $3800 (cell B6) at the end of the first, second, third and fourth periods respectively.

Let’s see how to calculate the internal rate of return of these cash flows using the IRR function in Google Sheets.

periodcashflow_amountsdescription
0-7000Initial cost of the project
12500net income for the 1st year
24500net income for the 2nd year
31000net income for the 3rd year
43800net income for the 4th year

IRR Formula:

=irr(B2:B6)

Result: 24.59%

That means the internal rate of return (IRR) after 4 years is 24.59%. To calculate the IRR after 1 year, we can use the below IRR formula in Google Sheets.

=irr(B2:B3)

The formula would return -64.29%. But in Excel, from my test, you should feed the rate_guess argument otherwise the formula would return #NUM! error.

Excel Formula:

=IRR(B2:B3,-30%)

The above Excel IRR formula would also return -64.29%.

This is not an IRR function in Google Sheets vs IRR function in Excel comparison. The above difference I had noticed in my test, so pointed out here.

IRR as Discount in NPV

We can calculate the net present value (NPV) of the above same cash flows. For example consider the ‘discount’ is 20%.

Syntax: NPV(discount, cashflow1, [cashflow2, …])

The net present value after the 4th year will be $619.00.

=npv(20%,B3:B6)+B2

If you replace the discount 20% with IRR in NPV, the net present value after the 4th year will be $0. Here is that formula.

=npv(irr(B2:B6),B3:B6)+B2
NPV and IRR functions in Google Sheets

That’s all about the IRR function in Google Sheets. Thanks for the stay, enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.