How to Use the XIRR Function in Excel
Overview of XIRR Function in Excel and Google Sheets
The XIRR function in Excel and Google Sheets is designed to compute the internal rate of return (IRR) for a sequence of cash flows that do not occur at regular intervals. This functionality proves invaluable for analyzing investments or projects characterized by irregular cash inflows and outflows.
Basic Syntax
The syntax for the XIRR function is as follows:
XIRR(values, dates, [guess])
- values: A range or array of numbers representing the cash flows. These include both incoming (positive values) and outgoing (negative values) amounts.
- dates: A range or array of dates corresponding to each cash flow in the ‘values’ argument. These dates must be formatted as actual date values.
- guess: (Optional) An initial guess at the internal rate of return. If omitted, Excel and Google Sheets default this value to 0.1 (or 10%).
Example Usage
Consider an example where an investment involves irregular cash flows. Below is a table depicting these cash flows and their respective dates:
Date | Cash Flow |
---|---|
01/01/2021 | -10000 |
05/30/2021 | 2500 |
09/15/2021 | 4000 |
12/20/2021 | 3000 |
To determine the internal rate of return for this investment using the XIRR function in Excel or Google Sheets, input the following formula:
=XIRR(B2:B5, A2:A5)
In this formula, B2:B5 refers to the cash flow values and A2:A5 to their corresponding dates. The function calculates the internal rate of return based on these cash flows.
Applications of XIRR Function
The XIRR function has various applications in the financial sector, including but not limited to:
- Assessing the returns on investment portfolios that involve irregular deposits and withdrawals.
- Computing the internal rate of return for projects with non-periodic cash flows.
- Estimating the required rate of return to achieve a specific financial goal given a series of cash flows.
The XIRR function allows for precise financial evaluation and decision-making by accurately calculating the rate of return for investments or projects with irregular or non-periodic cash flows.
More information: https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d