Excel XIRR function

Function: XIRR

Description: The XIRR function in Excel is a financial function that calculates the internal rate of return for an investment based on a series of irregular cash flows. The internal rate of return (IRR) is a measure of the return on an investment, expressed as a percentage. It represents the annualized rate of return that would make the net present value (NPV) of the investment equal to zero.

Syntax: XIRR(values, dates, [guess])

The XIRR function syntax has the following arguments:

• Values    Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.
• Dates    Required. A schedule of payment dates that corresponds to the cash flow payments. Dates may occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. .
• Guess    Optional. A number that you guess is close to the result of XIRR.

Examples:

For example, to calculate the internal rate of return for an investment with the following cash flows:

• $1,000 invested on January 1, 2020
• $500 earned on March 1, 2020
• $250 earned on May 1, 2020

You can use the following formula:

=XIRR({-1000, 500, 250}, {DATE(2020,1,1), DATE(2020,3,1), DATE(2020,5,1)})

This formula would return the internal rate of return as a percentage.