I. Intro
The XIRR function calculates the internal rate of return of an investment with a series of irregularly distributed cash flows.
II. About the function
- Formula: =XIRR(values,dates,[guess])
- Parameters:
- values (required): Array or range of income or expenditure related to an investment.
- dates (required): Array or range of dates that corresponds to the values parameter.
- [guess] (optional): An estimated value of the internal rate of return. If omitted, [guess] is assumed to be 0.1 (10%).
- Example: =XIRR(B2:B25,C2:C25,1%)
- Note:
- Enter at least one positive cash flow and one negative cash flow. Otherwise, the #NUM! error value is returned.
- If any value in dates is not a valid date, the #VALUE! error value is returned.
- If any number in dates is earlier than the start date, the #NUM! error value is returned.
- If the number of dates and values doesn't match, the #NUM! error value is returned.
- The XIRR function is closely related to the net present value function XNPV. The rate of return calculated by the XIRR function is the interest rate when XNPV=0.
III. Steps
Use the XIRR function
- Select a cell and enter =XIRR.
- Enter the parameters in the cell. For example: =XIRR(A2:A6,B2:B6,0.01).
- Press Enter to get the result, which is 430.8088192 in this example.
250px|700px|reset
Delete the XIRR function
Select the cell with the XIRR function, and press Delete.