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!