How to Calculate the Modified Internal Rate of Return
Modified internal rate of return (MIRR) represents the annual return earned on a multi-year project. Many companies use MIRR to compare the attractiveness of different projects or investment opportunities that have different time horizons. For this reason, MIRR is a critical component of the capital budgeting process. MIRR differs from the traditional internal rate of return calculation (IRR) in that MIRR accounts for the fact that a project's cash flows are usually reinvested and a different rate of return than the project itself. By not accounting for this difference, an IRR calculation gives an unrealistically optimistic view of the return on a project. MIRR is calculated according to a complex mathematical formula, but it is possible to compute MIRR very easily using Microsoft Excel.
Other People Are Reading
Things You'll Need
- Microsoft Excel
- Estimation of a project's cash flows
- Estimated rate of return for the project's reinvested cash flows
- Your firm's cost of capital
Instructions
-
-
1
Estimate the revenue you expect a project to generate as well as the costs associated with the project. Use these figures to develop an estimation of the project's cash flows over its entire life. In addition to the ongoing costs of running the project, the cash flow estimations should include the initial cost of setting up the project as well as any proceeds you expect to generate from the sale of the project or equipment when the project is finished.
-
2
Estimate the amount of cash flow you expect to reinvest in each period, and calculate the net cash flow for each period. For example, if you expect a project to generate $10 million in period one and you expect to reinvest 80 percent of that amount, the net cash flow for the period is -$8 million.
-
-
3
Calculate the rate of return you expect to earn on the reinvested cash flows. Estimating this rate of return, which is more of an art than a science, depends upon the riskiness of the investments relative to other investments your company has funded. For example, if you are planning to reinvest the cash flows into a project that is 20 percent riskier than a project that generated a 10 percent rate of return, you should use a rate of return for the reinvested cash flows of 12 percent.
-
4
Determine your firm's after tax cost of capital. If your firm has been around for awhile, you probably already know what the appropriate cost of capital is. The firm's cost of capital is calculated according to the following formula: Rd*(1-T)*(D/V) + Re*(E/V), where Rd is the interest rate your firm pays on debt; T is your company's corporate tax rate; Re is the rate of return expected by equity investors in your firm; D is the market value of your firm's debt; E is the market value of your firm; and V is equal to D + E.
-
5
Enter the upfront cash flow of the project, the net cash flow for each period calculated in Step 2, and the cash flows generated from the project's sale into Excel. Enter the cash flows next to one another in a horizontal row. Also enter the rate of return on reinvested cash flows (calculated in Step 3) and your firm's cost of capital (calculated in Step 4) in separate cells in Excel.
-
6
Use the MIRR formula in Excel to calculate the modified internal rate of return. Type =MIRR( into Excel, then select the vertical row of cash flows from the project. Add a comma, and then select the firm's cost of capital. Add another comma, and select the rate of return on reinvested cash flows. Close the parenthesis and press Enter. The resulting number is the project's modified internal rate of return.
-
1
Tips & Warnings
Enter cash outflows (such as the initial upfront cost of the project) as negative numbers and cash inflows (such as cash received on the sale of the project) as positive numbers. Since estimating the rate of return on reinvested cash flows requires a number of assumptions, it may make sense to do a sensitivity analysis, where you calculate the MIRR of the project for a range of rates of return.
Make sure you do not select blank cells, such as the cells at the end of the data row, when selecting the cash flow figures in the MIRR formula. Excel interprets blank cells as zeros, which will depress the calculated MIRR.
