Download Article
A step-by-step guide to IRR calculation in Excel
Download Article
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.Advertisement
-
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.
-
Copy the formula in cell B8 and paste it into cells C8 and D8.
-
Highlight the project with the highest IRR percentage rate. This is the investment with the most potential for growth and return.
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
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.Thanks
- If the IRR function returns a #NUM! error, try entering a number in the "Guess" field of the function window.Thanks
- The "IRR" function in Excel will only work if you have at least 1 positive and 1 negative entry per project.Thanks
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
Advertisement
Things You'll Need
- Project details
- Computer
- Microsoft Excel
References
About This Article
Thanks to all authors for creating a page that has been read 425,313 times.
Advertisement