Evaluating whether an investment opportunity is attractive is crucial for investors. The internal rate of return (IRR) is one of the key metrics used in investment analysis to measure expected returns. By calculating a project’s IRR and comparing it against required return thresholds, investors can determine if a potential investment is worthwhile. This article will illustrate how to calculate IRR in an investment rollforward example, highlighting key steps and considerations when leveraging IRR to assess investment attractiveness. Properly applying IRR in investment analysis provides a quantitative and structured approach for investors to evaluate investment opportunities involving multiple cash inflows and outflows over time.
Constructing an investment rollforward model to calculate IRR
The key to correctly using IRR is developing an accurate rollforward model that captures the full cash flow profile of the investment over time. This involves itemizing all the upfront costs, ongoing cash expenses, revenue inflows, tax implications, residual value at the end, and so on. An example could be constructing a 5-year model for a new manufacturing facility, factoring in construction costs in Year 0, working capital and production costs over Years 1-5, product sales revenue each year, depreciation tax shields, and final residual value of the assets. The investment rollforward projects how the net cash position runs forward year-by-year based on the cash inflows and outflows. This full cash flow schedule is then used as the inputs to calculate IRR.
Using Excel IRR function to calculate return
Once the investment rollforward model is built, IRR can be easily calculated using Excel’s IRR function. The syntax is simply =IRR(cash flow values), where cash flow values refer to the full schedule of periodic cash inflows and outflows from the investment. The IRR result represents the discount rate at which the NPV of the investment equals zero. A project with IRR above the required return threshold is worth pursuing, as it implies a positive NPV. The Excel IRR function allows quick sensitivity analysis as well – changing inputs in the rollforward will instantly update the IRR, allowing investors to assess how different assumptions impact overall return.
Comparing IRR to required return hurdle rates
IRR should be compared against the appropriate required rate of return to make a go/no-go investment decision. Common hurdle rates include a firm’s weighted average cost of capital for overall projects, or sector-specific hurdle rates based on past deals and industry benchmarks. For example, software companies may require new IT projects to exceed 20% IRR, while oil firms use 12% for greenfield exploration projects. If the IRR is sufficiently above the hurdle rate, it implies an adequate margin of safety in returns to move forward.
Limitations of IRR in investment analysis
While a useful metric, IRR has limitations investors should recognize. IRR values can be skewed or even useless if there are cash flows that flip signs over the investment horizon. It is also challenging to properly compare IRR across different projects if they have vastly different scales or durations. Using IRR in isolation without factoring model assumptions can also be dangerous. However, as long as applied prudently with good judgement, IRR remains a valuable tool in the investment rollforward process to quantify expected returns on opportunities.
Building an investment rollforward model provides the foundation for a rigorous IRR analysis. Comparing the resulting IRR to hurdle rates allows investors to make informed go/no-go investment decisions. Despite limitations, used properly, IRR is a key metric to evaluate return attractiveness.