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.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.