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.
| period | cashflow_amounts | description |
| 0 | -7000 | Initial cost of the project |
| 1 | 2500 | net income for the 1st year |
| 2 | 4500 | net income for the 2nd year |
| 3 | 1000 | net income for the 3rd year |
| 4 | 3800 | net 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)+B2If 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
That’s all about the IRR function in Google Sheets. Thanks for the stay, enjoy!
