Wall Street Prep

XIRR Function

Guide to Understanding the XIRR Function in Excel

Learn Online Now

XIRR Function

How to Use the XIRR Function in Excel

The XIRR function in Excel computes the internal rate of return (IRR), which refers to the compounded rate of return on a specific investment.

In other words, the internal rate of return (IRR) is the interest rate that an initial investment must have grown by to reach the value at exit – i.e. from the beginning value to the ending value.

The two required inputs are:

  1. Range of Dates
  2. Range of Cash Inflows/Outflows

XIRR Function Formula

In Excel, the XIRR formula is as follows:

XIRR Function: “=XIRR (Range of Cash Inflows/Outflows, Range of Dates)”
  • Range of Cash Inflows / Outflows ➝ The range of cash inflows/outflows refers to the array of cash flow values
  • Range of Dates ➝ The matching dates (i.e. mm/dd/yy) of when the cash movement occurs.

In order for the formula to work properly, you must enter the cash inflows and outflows directly in line with the corresponding date – if not, the calculated IRR will be incorrect.

Additionally, the range of cash values must contain at least one positive and one negative number.

Under the context of investing, the initial investment should be entered as a negative figure since it represents an outflow of cash.

  • Cash Outflows ➝ Negative Number
  • Cash Inflows ➝ Positive Number

Inflows of cash could potentially include dividends received during the holding period and the sale proceeds on the date of exit.

XIRR vs. IRR Excel Function: What is the Difference?

The XIRR function in Excel is more practical than the IRR function due to the increased flexibility of not being restricted to annual periods.

Unlike the IRR function, XIRR can handle irregular cash flows, which reflects reality more accurately.

The drawback to the IRR function is that Excel assumes each cell is separated by exactly twelve months, which is rarely the case.

IRR Function: “=IRR (Range of Cash Inflows/Outflows)”

For XIRR, the effective annual rate is returned with daily compounding, while the IRR function assumes a stream of equally spaced cash flows.

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.
Learn More
Comments
guest
0 Comments
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.