XIRR Function Examples in Excel, VBA, & Google Sheets

This Excel tutorial demonstrates how to use the Excel XIRR function in Excel to calculate the internal rate of return of a set of cash flows, with formula examples.

Excel Function XIRR

XIRR Function Description:

The XIRR Function Calculates the internal rate of return for a series of cash flows. The cash flows do not need to be periodic.

To use the XIRR Excel Worksheet Function, select a cell and type:
xirr formula syntax

(Notice how the formula inputs appear)

XIRR Function Syntax and Inputs:

=XIRR(values,dates,guess)

values – The values argument consists of an array or a reference to a range of cells representing the series of cash flows. And the values argument must contain at least one negative and one positive value for the calculation.

dates – This argument refers to the array of dates at which the cash flows occur. Its length should be the same as the length of the values array. The first date should be the initial investment’s/loan’s date and subsequent dates of further payments must occur later than the first date.

guess – OPTIONAL: It’s an estimate of what the internal rate of return should be. If this argument is omitted then it assumes the default value of 0.1 or 10%.

AutoMacro - VBA Code Generator

What Is Excel XIRR Function?

XIRR stands for Extended Internal Rate of Return. The Excel XIRR function is used to calculate the return on investment in which the series of cash flows occur at specified dates. XIRR Function always returns an annualized IRR value regardless of the intervals of the cash flows.

XIRR Formula

The value of XIRR is calculated as the value of the rate that satisfies the following equation:

xirr formula

Where:

n = number of time periods

j = period number

rate = discount rate or interest rate

dj = the jth payment or the last payment

d1 = the 0th payment

Pj = the jth payment or the last payment

Calculate Internal Rate of Return of an investment

xirr function ex1 data

Consider you made an investment of $20,000 in 2019 and expects to receive profits in the next five years. The cash flows and their corresponding dates are mentioned in the above table.

Here the initial investment is entered with a negative sign as this an outgoing payment and profits are represented with positive values. The guess argument was omitted, so the XIRR function operated on the default value of 0.1 or 10%.

The formula used in the calculation of interest rate or internal rate of return is:

=XIRR(B4:B9,C4:C9)

Excel Function XIRR Example 1

The Internal Rate of Return for this investment is

XIRR = 3.84%

Calculate Internal Rate of Return of a Mutual Fund

xirr function ex2 data

Suppose, you started to invest in a mutual fund with an initial investment of $5,000 in the first year. After seeing good results on your initial investment, you increased your investment by 5% every year, for the next nine years. After nine years, you received a total return of $90,000 on your investments.

In the above table, the first nine values of the Values argument are in negative because it shows payment going out from our account into mutual funds.

The Formula used for calculation is:

=XIRR(B4:B13,C4:C13)

Excel Function XIRR Example 2

 

After calculation, the Internal Rate of Return you got on your initial and additional investments is

XIRR = 9.15%

XIRR in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

XIRR Function in Google Sheets

Additional Notes

Use the XIRR Function to calculate the Internal Rate of Return of investment.

Cash Flows do not need to be periodic. Instead, you must enter the corresponding dates along with the cash flows.

The result of the XIRR Function may appear as a decimal. Change the cell Number Formatting to Percentage to display the function result as a percentage:
percentage formatting excel shortcut

XIRR Examples in VBA

You can also use the XIRR function in VBA. Type:
application.worksheetfunction.xirr(values,dates,guess)
For the function arguments (values, etc.), you can either enter them directly into the function or define variables to use instead.

Return to the List of all Functions in Excel

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!