Internal rate of return, or IRR for short, is a common technique used to analyze investment opportunities. In essence, it tells you the interest rate you’ll receive on a series of regularly scheduled incoming cash flows. The calculations are tedious to perform manually, but they’re easy to do with a spreadsheet. Let’s learn how to find IRR in Microsoft Excel.
How to Find IRR in Excel
When you’re trying to find the internal rate of return (IRR), there are a few conditions to keep in mind. Chief among these is that the cash flows must occur at regular and consistent intervals. In other words, they can’t occur over different timespans. (Excel does have a built-in feature to address unique time periods, which we’ll take a look at later. Read on).
Secondly, you’ll need to list amounts invested as negative values, while projected returns (your positive cash flows) are written as positive values. This ensures that Excel’s IRR formula correctly analyzes and processes your inputs in order to return the correct IRR value.
The IRR formula works by analyzing your investments and payments received (incoming cash flows) as compared to a guess. The guess is simply an estimate for the IRR that you input. This step is actually optional. If no guess is inputted, Excel will default to 0.1, which corresponds to a 10% estimated return.
Let’s consider the case of a hypothetical investment. You have the opportunity to invest $15,000 today. At the end of a year, you’ll receive $3,500 in income. This pattern will continue annually for the four subsequent years, giving you five total years with a total of $17,500 earned for those five years. You’re curious what this corresponds to in terms of the IRR received.
Go ahead and build out a layout like you see below. Keep in mind, your initial investment of $15,000 should be listed as a negative number, since it’s a cash outflow. The subsequent cash inflows – of $3,500 annually – are listed as positive numbers.
Now, it’s time to create your IRR formula. This works by utilizing Excel’s built-in IRR function. Again, it’s possible to perform IRR calculations manually, but it’s quite difficult and can be extremely time-consuming. Your best, and most accurate option is to always use the IRR functions.
First, type your guess – your estimated IRR – into any empty cell, formatted as a percentage. Here, let’s use 12%. Remember, you can skip this step, and Excel will default to 10%.
Click into another empty cell, and type =. This tells Excel that you’ll be inputting a formula. Next, continue by typing IRR, then (. Your IRR formula is set up as:
=IRR(
Excel now asks for two inputs: values and guess. Again, the guess is optional, but we’ll include it here for illustrative purposes.
The values simply constitute your range of numbers. Unlike some Excel financial formulas, this will include your initial investment amount, along with the subsequent payments. In this example, those values reside in cells B2:B7. To easily add these to your formula, click and drag your cursor to select that range of cells. Then, type a comma. Your formula now reads:
=IRR(B2:B7,
Assuming you want to use the guess functionality, click into the cell containing your estimated IRR. We used 12% here, and that value is in cell D3. Click into cell D3, then close your parentheses. Your IRR formula in Excel is now complete, and it reads:
=IRR(B2:B7,D3)
internal-rate-of-return-excel.jpg
Hit Enter on your keyboard, and Excel returns the IRR for this investment opportunity: 5%.
If you continue adding time periods to your table and formula, the IRR begins to increase sharply. Thus, while you’ll receive a 5% IRR over five years, better rates could be obtained if you had additional years of earning power.
How to Find XIRR in Excel
Remember, the IRR function works only for regularly-scheduled cash flows over identical intervals. But in the real world, that isn’t always possible. Imagine now that you have the opportunity to invest $1,000. In six months, you’ll receive $400 in income. One year later, you’ll receive $900. Of course, those two intervals are very different in length. The IRR function simply won’t work.
Instead, you’ll need to use a related Excel function: XIRR. It performs the same basic duty, but it works with irregular timespans.
This will cause a minor adjustment to the layout of your spreadsheets. Predictably, for the XIRR function to work, it will need to know the dates. Add them into a column immediately beside the list of payments.
Then, add a guess (optional, again), and click into a second empty cell. Type =, as always in Excel, then open up the XIRR formula by typing XIRR(.
Your formula reads:
=XIRR(
Excel will ask for three inputs: the values, the dates, and your guess. The values are the numbers in cells C2:C4. Click and drag to select the range, then type a comma. The dates are in D2:D4, so again repeat the click-and-drag process to add them to your formula with a comma at the end. So far, you have:
=XIRR(C2:C4,D2:D4,
Finally, click on the cell containing your guess, F2. Close the parentheses, and review your formula:
=XIRR(C2:C4,D2:D4,F2)
Hit Enter on your keyboard, and Excel returns your XIRR: 25%.
As you can see, the related IRR and XIRR functions make it easy to calculate rates of return in Excel. This helps you make better, smarter financial and investment decisions, no tedious math required!
NPV in Excel