How to use XIRR Google Sheets formula?

The syntax for the XIRR formula is =XIRR(cashflow_amounts, cashflow_dates, [rate_guess]), it has 2 required parameters and 1 optional parameter.

XIRR

The XIRR function in Google Sheets calculates the internal rate of return (IRR) for a series of cash flows that occur at irregular intervals. It returns the annualized percentage rate earned by an investment, taking into account the dates on which each payment was made. The function is most commonly used in financial analysis to compare the returns on different investment opportunities.

How to use XIRR formula?
Examples of using XIRR formula
XIRR formula not working?
Similar formulas to XIRR

Usage

Use the XIRR formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:

=XIRR(cashflow_amounts, cashflow_dates, [rate_guess])

Parameters:

cashflow_amounts (required):
An array or range of cells that contain the cash flow amounts for the investment. Positive values represent cash inflows, and negative values represent cash outflows. The first value must be negative and represents the initial investment.
cashflow_dates (required):
An array or range of cells that contain the dates on which the cash flows occur. The first date must correspond to the initial investment and be expressed as a negative number representing the number of days between the investment date and the cash flow date.
rate_guess (optional):
An optional guess for the rate of return, expressed as a percentage. If omitted, Google Sheets uses a default value of 10%.

Examples

Here are a few example use cases that explain how to use the XIRR formula in Google Sheets.

Calculate IRR for a single investment

Suppose you invested $10,000 in a project and received cash inflows of $1,000, $2,000, and $3,000 in the first, second, and third years, respectively. You can use the XIRR function to calculate the annualized rate of return on this investment, taking into account the exact dates on which each payment was made.

Compare the returns on multiple investments

Suppose you are considering investing in two different projects, each of which has a different pattern of cash flows over time. You can use the XIRR function to calculate the IRR for each project and compare the returns to determine which investment opportunity is better.

Analyze the profitability of a real estate investment

Suppose you are considering purchasing a rental property and want to estimate the annualized rate of return you can expect based on the expected rental income and expenses. You can use the XIRR function to calculate the IRR for the cash flows associated with the property and determine whether the investment is likely to be profitable.

Common Mistakes

XIRR not working? Here are some common mistakes people make when using the XIRR Google Sheets Formula:

Incorrect cash flow amounts

One common mistake when using XIRR is inputting incorrect cash flow amounts. This can happen if the amounts are not properly aligned with the corresponding dates, or if there are missing or extra entries in the cash flow amounts. To correct this, make sure that the amounts are properly aligned with the dates and that there are no missing or extra entries.

Incorrect cash flow dates

Another common mistake when using XIRR is inputting incorrect cash flow dates. This can happen if the dates are not in the correct format or if there are missing or extra entries in the cash flow dates. To correct this, make sure that the dates are in the correct format and that there are no missing or extra entries.

Incorrect rate guess

A common mistake when using XIRR is inputting an incorrect rate guess. This can happen if the rate guess is too high or too low, which can cause the function to return an error or an incorrect result. To correct this, try adjusting the rate guess to a more appropriate value.

The following functions are similar to XIRR or are often used with it in a formula:

IRR
The IRR function is used to calculate the Internal Rate of Return for a series of cash flows. The IRR is the interest rate at which the Net Present Value of the cash flows equals zero. It is commonly used to evaluate potential investments or projects.
NPV
The NPV function calculates the net present value of a series of cash flows, discounted by a specified rate. It is commonly used to determine the present value of an investment, where the cash flows represent incoming and outgoing payments. The function takes a discount rate and one or more cash flow values as input.
XNPV
The XNPV function calculates the net present value of a series of cash flows that occur at irregular intervals. It discounts each cash flow by a discount rate that is supplied by the user. It is commonly used in financial analysis to determine the value of investments or to evaluate the profitability of projects.
PMT
The PMT formula in Google Sheets is a financial function that calculates the periodic payment required to fully pay off a loan or investment based on a constant interest rate and a fixed number of payments. It is commonly used to determine loan payments, mortgage payments, and annuity payments.
FV
The FV function calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.

Learn More

You can learn more about the XIRR Google Sheets function on Google Support.