How to Calculate an IRR in Excel

HumanIon

Editor

posted on 13 years ago — updated on 1 second ago

54
views

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

Launch Microsoft Excel.

Create a new workbook and save it with a descriptive name.

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.

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.

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.

Input the data for each of the 3 projects, including the initial investment and the forecasted net income for each of the 5 years.

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.

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.