totn Excel Functions

MS Excel: How to use the XIRR Function (WS)

This Excel tutorial explains how to use the Excel XIRR function with syntax and examples.

Description

The Microsoft Excel XIRR function returns the internal rate of return for a series of cash flows that may not be periodic. Because you provide the dates for each cash flow, the values do not have to occur at regular intervals.

The XIRR function is a built-in function in Excel that is categorized as a Financial Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the XIRR function can be entered as part of a formula in a cell of a worksheet.

Microsoft Excel

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the XIRR function in Microsoft Excel is:

XIRR( values, dates, [estimated_irr] )

Parameters or Arguments

values
A range of cells that represent the series of cash flows.
dates
A range of cells that represent the dates that correspond to the series of cash flows.
estimated_irr
Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an estimated_irr of 0.1 or 10%.

Returns

The XIRR function returns a numeric value.

Note

  • When entering the cash flow values, the value that is dated at the beginning of the investment must be negative.
  • The cash flow values can be listed in any chronological order because the corresponding date is provided.
  • If you don't have at least 1 positive and 1 negative cash flow value, the XIRR function will return the #NUM! error.
  • If you provide an invalid date in the date parameter, the XIRR function will return #NUM! error.
  • Excel tries to recalculate the XIRR until the result is accurate within 0.000001 percent. If after 100 tries Excel has not calculated an accurate value, it will return the #NUM! error.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel XIRR function examples and explore how to use the XIRR function as a worksheet function in Microsoft Excel:

Microsoft Excel

Based on the Excel spreadsheet above:

The first investment example returns an internal rate of return of 2.660242057 in cell B10. The cash flow values are found in the range A4:A8 with the corresponding dates in B4:B8.

It assumes that you have an initial cost of $7,500 on 1/1/2016. You then have the following income: $3,000 on 2/1/2016, $5,000 on 4/15/2016, $1,200 on 8/1/2016 and $4,000 on 3/26/2017.

=XIRR(A4:A8,B4:B8)
Result: 2.660242057 or 266.0242057%

The second investment example returns an internal rate of return of 2.186309695 in cell E10. It assumes that you have an initial cost of $5,000. You then have the following income: $800 on 5/31/2016, $1,300 on 9/1/2016, $600 on 12/31/2016 and $7,500 on 1/31/2017.

=XIRR(D4:D8,E4:E8)
Result: 2.186309695 or 218.6309695%
Share on: