Companies often use the Internal Coverage Rate to assign ownership to projects based on profitability and growth potential. This is sometimes called Discounted Cash Flow (DCF), because you have to find the interest rate to guarantee a net profit of 0. The higher the IRR, the greater the growth potential of the project. The ability to calculate the IRR on Excel can be useful for managers who are not currently in the accounting office.
Steps
Step 1. Launch Microsoft Excel
Step 2. Create a new worksheet and save it
Step 3. Determine the projects or investments to be analyzed in a given period
For example, let's assume that you are asked to calculate an IRR for 3 projects over a 5 year period
Step 4. Format the worksheet by creating two labels in two columns
- The first column contains the labels
- Dedicate a column to each project or investment to compare and analyze.
Step 5. Enter the labels for the rows from cell A2 to cell A8, as follows:
Initial Investment, Net Profit 1, Net Profit 2, Net Profit 3, Net Profit 4, Net Profit 5 and IRR.
Step 6. Enter the data for each of these 3 projects, including the initial investment and the expected net profit for each of the 5 years
Step 7. Select cell B8 and use the Excel function button (“fx”) to create the TIR function for the first project
- In the "values" field of the Excel function window, select cells B2 to B7 by clicking on B2 and dragging the selection up to B7.
- Leave the "Guess" field of the Excel function window blank, unless you have this data. Click on the "OK" button.
Step 8. Make sure the value returned by the function is a percentage
- Otherwise, select the cell and click the "Percent" button in the number field.
- Double click on the "Increase Decimal" button to add 2 decimal numbers to your percentage.
Step 9. Copy the formula into cell B8 and paste it into cells C8 through D8
Step 10. Select the project with the highest IRR percentage
This is the investment with the greatest potential for growth and profit.
Advice
- Remember to enter the “Initial investment” value as a negative number, as this value represents the exits. The "net profit" should be entered as positive, unless you have forecast a profit at a loss in a given year.
- The TIR function of Excel only works if you have a positive and 1 negative income for each project.
- If the TIR function returns a #NUM! Error, try entering a number in the “Guess” field of the function window.
== Things You Will Need ==
- Project details
- Computer
- Microsoft Excel
