SheetFX
≡
<< Go back to all functions

XIRR

The XIRR function in Google Sheets calculates the internal rate of return of an investment based on a specified series of potentially irregularly spaced cash flows. This powerful function is essential for financial analysis and investment evaluations.

Function Syntax and Parameters

Syntax: XIRR(cashflow_amounts, cashflow_dates, [rate_guess])

Parameters:

  • cashflow_amounts: An array or range containing the cash flow amounts for the investment.
  • cashflow_dates: An array or range containing the corresponding dates of the cash flows.
  • [rate_guess]: [Optional] An initial guess for the rate of return. If omitted, the function uses 0.1 (10%) as the default rate guess.

Step-by-Step Tutorial

  1. Using XIRR with cash flow amounts and dates:

    • Example: If A2:A6 contains the cash flow amounts [1000, -100, -200, 300, 500], and B2:B6 contains the corresponding dates, XIRR(A2:A6, B2:B6) will calculate the internal rate of return.

  2. Using XIRR with a rate guess:

    • Example: If the rate guess is 0.05, use the syntax XIRR(A2:A6, B2:B6, 0.05).

  3. Using XIRR with irregularly spaced cash flows:

    • Example: If the cash flows occur on 01-Jan-2022, 15-Feb-2022, and 31-Dec-2022, use the syntax XIRR(A2:A4, B2:B4).

Use Cases and Scenarios

  1. Investment Analysis: Evaluate the profitability of an investment based on irregular cash flows.
  2. Loan Calculations: Determine the internal rate of return for loan repayment schedules.
  3. Portfolio Performance: Calculate returns on investment portfolios with variable cash flows.

Related Functions

  • IRR: Returns the internal rate of return for a series of cash flows that occur at regular intervals.
  • NPV: Calculates the net present value of an investment based on a series of future cash flows.

Related Articles