How to Calculate an IRR in Excel

Step-by-Step Guide

1. 1

   Step 1: Launch Microsoft Excel.

   For instance, assume that you have been asked to calculate an IRR for 3 projects over a period of 5 years. , 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. ,,, 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. , 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. ,, This is the investment with the most potential for growth and return.
2. 2

   Step 2: Create a new workbook and save it with a descriptive name.

3. 3

   Step 3: Determine the projects or investments you will be analyzing and the future period to use.

4. 4

   Step 4: Prepare your spreadsheet by creating the column labels.

5. 5

   Step 5: Enter labels for the rows in cells A2 down to A8 as follows: Initial Investment

6. 6

   Step 6: Net Income 1

7. 7

   Step 7: Net Income 2

8. 8

   Step 8: Net Income 3

9. 9

   Step 9: Net Income 4

10. 10

    Step 10: Net Income 5 and IRR.

11. 11

    Step 11: Input the data for each of the 3 projects

12. 12

    Step 12: including the initial investment and the forecasted net income for each of the 5 years.

13. 13

    Step 13: Select cell B8 and use the Excel function button (labeled "fx") to create an IRR function for the first project.

14. 14

    Step 14: Confirm that the function returns the number as a percentage.

15. 15

    Step 15: Copy the formula in cell B8 and paste it into cells C8 and D8.

16. 16

    Step 16: Highlight the project with the highest IRR percentage rate.

Detailed Guide

About the Author