How to Calculate IRR in Excel

Knowing the internal rate of return (IRR) of an investment allows you to plan for future growth and expansion of that investment. To calculate those numbers, use the IRR formula in Microsoft Excel 2019, 2016, 2013, 2010, 2007, Excel for Mac, Excel for Microsoft 365, and Excel Online.

Understanding the IRR Function

The syntax for the IRR function is as follows: =IRR(values,[guess]) where "values" are the list of values representing a series of cash flows in equal increments like a particular date every month, or monthly. Values can also be cell references or ranges of references. For example, A2:A15 would be the values in the range of cells A2 through A15.

The "guess" is an optional argument that you guess is close to your IRR result. If you don’t use this argument, Excel defaults to the value of 0.1 (10%). When using the Guess value, you receive a #NUM error, or the end result isn’t what you expected. But you can always change this value.

Using the IRR Formula in Excel

Your values must contain at least 1 positive number and 1 negative number for the internal rate of return formula to work correctly. Your first negative number will most likely be the initial investment, but could have other negative values in the array.

Additionally, you have to make sure you enter your values in the order you want. IRR uses the order of the values to calculate. You must also make sure your array of numbers is formatted as numbers. Text, logical values, and empty cells will be ignored by Excel’s IRR formula.

How to Calculate IRR in Excel

You’ll first want to ensure that your sequence of values for net cash flow in all your entries is in the Number format. To accomplish this, do the following:

1. Select the cells you want to check or change the format to.

2. Right-click and select Format Cells.

3. Under the Number tab, select Number or Accounting.

   You might use the Accounting format if you want to use parenthesis around negative values.

4. Make any formatting adjustments to the left of the Category field, then select OK.

   For example, if using Accounting, and you want to set the Symbol to $, use the drop-down menu to select $. You can also select how many Decimal places your values have.

5. Select a cell you want to place the IRR value in and enter the following:

   =IRR(values,[guess])
   
   

   In the example, the formula would read:

   =IRR(B4:B16)
   
   

   Here, a cell range is being used instead of actual values. This is especially helpful if working in financial spreadsheets where values can change, but the cell location doesn’t. Also, note that the formula used is using Excel’s default value of 0.1. If you want to use another value, such as 20%, enter the formula as: =IRR(B4:B16,20%).

6. Once you’ve entered and formatted the formula to your needs, press Enter to see the value.

   If you want to use a cell value, use the cell reference instead of the number. In this example, a list of Guess values has been entered into the E column. To calculate a 20% Guess, use this formula: =IRR(B4:B16,E5). This makes it easier to figure different values by entering in a reference cell where you can keep changing the number and not the formula.