Purpose

Return value

Syntax

=XIRR(values,dates,[guess])
  • values - Array or reference to cells that contain cash flows.
  • dates - Dates that correspond to cash flows, in any order.
  • guess - [optional] An estimate for expected IRR. Default is 0.1 (10%).

Using the XIRR function

The XIRR function calculates the internal rate of return for a series of cash flows that occur at irregular intervals. 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. To calculate the internal rate of return for a series of regular, periodic cash flows, use the IRR function .

XIRR is related to the XNPV function . The rate returned by XIRR is the interest rate when XNPV = 0. The XIRR function uses iteration to arrive at a result. Starting with guess (which defaults to 0.1 if not provided) XIRR iterates through a calculation until the result is accurate to 0.000001 percent. If no result is found after 100 tries, XIRR returns the #NUM! error.

The XIRR function takes three arguments : values , dates , and guess . Values represent a series of cash flows. The first value is optional and corresponds to a cost at the beginning of the investment. If the first value is a cost or payment, it must be a entered as a negative number. Values must include at least one positive and one negative value, or XIRR will return a #NUM! error. If values contains any non-numeric values, XIRR returns a #VALUE! error.

The dates argument represents a schedule of dates that correspond to values . The values supplied for dates must be valid Excel dates . Dates do not need to be entered in chronological order. Typically, dates is supplied as a range . If any date is not recognized as a date, XIRR returns a #VALUE! error.

The guess argument is optional and represents the seed value to start with for the iterative calculation used by XIRR. If not provided, guess defaults to 10% (0.10). Typically, you can safely omit guess . If XIRR returns #NUM!, and values contains at least one positive and one negative value, try different percentages for guess between 0 and 1.

Example

In the example shown, dates are in the values are in the range B5:B10, and dates are in the range C5:C10. The formula in cell F4 is:

=XIRR(B5:B10,C5:C10)  // returns .0788

The result returned by XIRR is .0788, displayed as 8% when the percentage number format is applied.

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
  • Dates do not need to be in chronological order.
  • XIRR is related to the XNPV function .

Purpose

Return value

Syntax

=XNPV(rate,values,dates)
  • rate - Discount rate to apply to the cash flows.
  • values - Values representing cash flows.
  • dates - Dates that correspond to cash flows, in any order.

Using the XNPV function

The XNPV function returns the net present value (NPV) of an investment based on a discount rate and a series of cash flows that occur at irregular intervals. Values represent cash flows and be correspond to dates . Negative values represent cash paid out; positive values represent cash received. The first date indicates the beginning of the schedule of payments and must be the earliest date. Subsequent dates may occur in any order.

The XNPV function takes three arguments : rate , values , and dates . Rate represents the discount rate to apply to the cash flows. Enter rate as a percentage like 6% or the decimal value 0.06.

Values represent a series of cash flows that correspond to dates . The first value is optional and corresponds to a cost at the beginning of the investment. If the first value is a cost or payment, it must be a entered as a negative number. All subsequent payments are discounted based on a 365-day year. Values must include at least one positive and one negative value, or XNPV will return a #NUM! error.

The dates argument represents a schedule of dates that correspond to values . The values supplied for dates must be valid Excel dates . The first payment date indicates the beginning of the schedule of payments and must be the earliest date. Other dates must be later than this date, but do not need to be in chronological order. Typically, dates is supplied as a range .

XNPV does not discount the initial cash flow. Subsequent payments are discounted based on a 365-day year. To discount to a particular valuation date, you can set up XNPV so that the first cashflow is zero, associated with the valuation date.

Example

In the example shown, the formula in F6 is:

=XNPV(F4,B5:B10,C5:C10) // returns 177.6532

The result is 177.6532, displayed as 177.65 when formatted as a number with two decimal places.

Notes

  • Rate is provided as a percentage (.12 for 12%).
  • Dates do not need to be in chronological order, but the first payment date must be the earliest date.
  • Dates must be valid Excel dates .
  • XNPV doesn’t discount the initial cash flow.