Google Sheets

IRR: Google Sheets Formulae Explained

How do you use IRR in Google Sheets?

The IRR function in Google Sheets calculates the internal rate of return for a series of cash flows. This function is useful for investment analysis, as it can help you determine whether a particular investment is worth making. To use the IRR function, you need to provide a series of cash flows in the form of a table. The function will then calculate the internal rate of return for that series of cash flows.

What is the syntax of IRR in Google Sheets?

The syntax of IRR in Google Sheets is very simple. You just need to enter the following formula in the cell where you want the result to appear: =IRR(values)

What is an example of how to use IRR in Google Sheets?

The Internal Rate of Return (IRR) is a financial metric used to determine the profitability of an investment. It calculates the rate of return that equates the net present value of cash flows from an investment to zero. This can be used to compare different investments or to determine whether a particular investment is worth pursuing.

To calculate the IRR of a series of cash flows in Google Sheets, you can use the IRR function. This function takes as input a range of cells that contain the cash flows for the investment and the dates on which they occur. It then calculates the IRR for that investment.

For example, imagine you are considering investing in a new business. You have estimated that the business will generate the following cash flows over the next five years:

Year 1: -$10,000 Year 2: $5,000 Year 3: $10,000 Year 4: $15,000 Year 5: $20,000

To calculate the IRR for this investment, you can enter the following formula in a cell in Google Sheets:

=IRR(B2:B6)

This will return the IRR for the investment, which in this case is approximately 15%.

When should you not use IRR in Google Sheets?

There are a few instances when you should not use IRR in Google Sheets. One instance is when you have negative cash flows, which is when cash flow expenses are more than cash flow income. This will result in a negative IRR, which is not meaningful. Additionally, you should not use IRR when you have multiple investments or cash flows with different maturities. This is because the IRR calculation assumes all cash flows are reinvested at the same rate, which may not be the case.

What are some similar formulae to IRR in Google Sheets?

There is no one-size-fits-all answer to this question, as the formulae you use to calculate internal rate of return (IRR) in Google Sheets will vary depending on the type of data you are working with. However, some common formulae that can be used to calculate IRR include the XIRR and NPV functions. Additionally, you can also use the SUMXIRR and SUMNPV functions to calculate the IRR and NPV for a series of investments or cash flows.

Move beyond 

Google Sheets

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.