Calculate an IRR in Excel

Businesses will often use the Internal Rate of Return (IRR) calculation to rank various projects by profitability and potential for growth. This is sometimes called the "Discounted Cash Flow Method," because it works by finding the interest rate that will bring the cash flows to a net present value of 0. The higher the IRR, the more growth potential a project has. The ability to calculate an IRR on Excel can be useful for managers outside of the accounting department.

Steps

  1. Launch Microsoft Excel.
  2. Create a new workbook and save it with a descriptive name.
  3. Determine the projects or investments you will be analyzing and the future period to use.
    • For instance, assume that you have been asked to calculate an IRR for 3 projects over a period of 5 years.
  4. Prepare your spreadsheet by creating the column labels.
    • The first column will hold the labels.
    • Allow one column for each of the projects or investments that you would like to analyze and compare.
  5. Enter labels for the rows in cells A2 down to A8 as follows: Initial Investment, Net Income 1, Net Income 2, Net Income 3, Net Income 4, Net Income 5 and IRR.
  6. Input the data for each of the 3 projects, including the initial investment and the forecasted net income for each of the 5 years.
  7. Select cell B8 and use the Excel function button (labeled "fx") to create an IRR function for the first project.
    • In the "Values" field of the Excel function window, click and drag to highlight the cells from B2 to B7.
    • Leave the "Guess" field of the Excel function window blank, unless you have been given a number to use. Click the "OK" button.
  8. Confirm that the function returns the number as a percentage.
    • If it does not, select the cell and click the "Percent Style" button in the number field.
    • Click the "Increase Decimal" button twice to apply 2 decimal points to your percentage.
  9. Copy the formula in cell B8 and paste it into cells C8 and D8.
  10. Highlight the project with the highest IRR percentage rate. This is the investment with the most potential for growth and return.

Tips

  • ​Remember to enter your "Initial Investment" values as negatives, since they represent cash outlays. The "Net Income" values should be entered as positive amounts, unless you anticipate a net loss in a given year. That figure only would be entered as a negative.
  • If the IRR function returns a #NUM! error, try entering a number in the "Guess" field of the function window.
  • The "IRR" function in Excel will only work if you have at least 1 positive and 1 negative entry per project.
  • In fact, Year 0 is the initial investment at the beginning of year 1

Things You'll Need

  • Project details
  • Computer
  • Microsoft Excel

Related Articles

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Calculate_an_IRR_in_Excel&oldid=272995"