Exceljet

Quick, clean, and to the point

Excel XIRR Function

Excel XIRR function
Summary 

The Excel XIRR function is a financial function that returns the internal rate of return (IRR) for a series of cash flows that occur at irregular intervals.

Purpose 
Calculate internal rate of return for irregular cash flows
Return value 
Calculated return as percentage
Syntax 
=XIRR (values, dates, [guess])
Arguments 
  • values - Array or reference to cells that contain cash flows.
  • dates - Dates that correspond to cash flows.
  • guess - [optional] An estimate for expected IRR. Default is .1 (10%).
Usage notes 

The XIRR function calculates in the internal rate of return for series of cash flows that occur at irregular intervals. To calculate the internal rate of return for a series of regular, periodic cash flows, use the IRR function.

Payments are expressed as negative values and income as positive values.  If the first value is a cost or payment, it must be a negative value. Subsequent payments are discounted based on a 365-day year.

Dates must be valid Excel dates in chronological order. You can use the DATE function to enter dates precisely if needed.

Excel uses iteration to arrive at a result, starting with the guess (if provided) or with .1 (10%) if not. If an accurate rate can't be calculated after a fixed number of iterations, the #NUM error is returned. 

Notes

  • The values array must contain at least one positive value and one negative value.
  • Dates must be valid Excel dates that correspond to values
  • Values and dates should be in chronological order.