How to Use the XIRR Function in Google Sheets

Are you looking to calculate the internal rate of return for a schedule of cash flows in Google Sheets? Look no further! In this post, we will dive into the XIRR function and show you how to use it effectively.

Understanding the XIRR Function

The XIRR function is one of the three financial functions in Google Sheets used to calculate the internal rate of return. It is specifically designed for cash flows that are not periodic or evenly spaced. Keep in mind that the XIRR calculation excludes external factors such as inflation, cost of capital, and financial risks. Its focus is solely on the internal rate of return.

Syntax and Arguments of the XIRR Function

Let’s break down the syntax and arguments of the XIRR function.

Syntax:

XIRR(cashflow_amounts, cashflow_dates, [rate_guess])

In Excel, the syntax is slightly different, using values, dates, and [guess] instead.

Arguments:

• cashflow_amounts: This can be a range or array that contains a series of cash flows associated with the investment. It is important to note that this array must include at least one negative and one positive cash flow to calculate the rate of return.

• cashflow_dates: This is a range or array that corresponds to the dates of the cash flows. If any date precedes the starting date, the XIRR function will return an error value.

• rate_guess (optional): This is an estimate or guess that is close to the expected XIRR output. If omitted, Google Sheets will automatically use the default rate_guess of 0.1 (10%).

Example of Using the XIRR Function in Google Sheets

Let’s dive into an example to better understand how to use the XIRR function in Google Sheets.

Assume we have the following data range in column A to C:

Description     Cash Flow Amounts     Cash Flow Dates

To calculate the internal rate of return, we can use the XIRR function as follows:

=XIRR(B2:B7,C2:C7)

The expected output will be 0.10 (10%). If you want the output in percentage format, simply wrap the XIRR function with TO_PERCENT:

=TO_PERCENT(XIRR(B2:B7,C2:C7))

XIRR in XNPV

Now, let’s explore how we can calculate the net present value (XNPV) of an investment using the XIRR function in Google Sheets.

Assuming we want to calculate the XNPV of the above investment at a 10% discount, the formula would be:

=XNPV(XIRR(B2:B7,C2:C7),B2:B7,C2:C7)

The expected output will be 0.

That’s all there is to it! Now you can confidently use the XIRR function in Google Sheets to calculate the internal rate of return for your cash flows.

For more tips and tricks on Google Sheets, visit Crawlan.com and enhance your spreadsheet skills. Happy calculating!