Internal Rate of Return (IRR) on Investment Concept & Modelling

Updated: Apr 27, 2018

Tags: #IRR, #internalrateofreturn, #MIRR, #XIRR, #financialmodels, #hospitalitycode

This is the third article on key financial models and calculations in modern finance and investment analysis for the capital budgeting process. As mentioned in the previous article, because capital is limited in its availability, capital projects are individually evaluated using both quantitative analysis and qualitative information.

There are 6 main tools in use;

  1. Net Present Value (NPV)

  2. Internal Rate of Return (IRR)

  3. Modified Internal Rate of Return (MIRR)

  4. Profitability Index (PI)

  5. Payback

  6. Discounted Payback

I have covered most of them in my previous articles and we will review IRR in this article. I have added downloadable free Excel file on Key Financial Models and its easy calculation samples for your use. Please visit our “Resource Hub” to download.

Internal rate of return (IRR) is a financial metric used in cash flow analysis, primarily for evaluating investments, capital acquisitions, project proposals, programs, business case scenarios as well as capital budgeting to estimate the profitability of potential investments. Internal rate of return is a discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero. IRR calculations rely on the same formula as NPV does.

The higher a project's internal rate of return, the more desirable it is to undertake. IRR is uniform for investments of varying types and, as such, IRR can be used to rank multiple prospective projects on a relatively even basis. Assuming the costs of investment are equal among the various projects, the project with the highest IRR would probably be considered the best and be undertaken first.

IRR is sometimes referred to as "economic rate of return" or "discounted cash flow rate of return." The use of "internal" refers to the omission of external factors, such as the cost of capital or inflation, from the calculation. One popular use of IRR is comparing the profitability of establishing new operations with that of expanding existing ones. For example, an energy company may use IRR in deciding whether to open a new power plant or to renovate and expand a previously existing one. While both projects are likely to add value to the company, it is likely that one will be the more logical decision as prescribed by IRR.

IRR is all about cash flow stream and there should be a negative and positive cash flow values to be able to calculate it accurately. Timing, interest rate and reinvestment rates support an investment decision. However, IRR alone would not be suitable to give accurate information and therefore variations kicks in. This article emphasises several IRR issues and we will review it accordingly.

So, What is Internal Rate of Return (IRR)

Internal Rate of Return estimates the profitability of potential investments. Internal Rate of Return (IRR) is the discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero.

There are several different IRR interpretations and formulas. Therefore, I will explain two simple IRR definitions and then MIRR and XIRR respectively.

Definition 1- IRR is the Discount Rate That Brings NPV to Zero

For any IRR calculation, negative and positive cash flow values required; there should be at least one negative and one positive cash flow to complete the calculation. The first number in the cash flow series is typically a negative number that is assumed to be the project's initial investment.

A higher IRR figure generally indicates less risk. This is because IRR shows just how high inflation rates or risk probabilities have to rise in order to eliminate the present value of this investment.

Simple IRR calculations have limited value for evaluating and comparing investment proposals. The reason behind this verdict is simple because some months have 31 days while others have 30 or fewer, the monthly ­periods are not exactly the same length, therefore, the IRR will always return a slightly erroneous result when multiple monthly periods are involved.

Definition 2- IRR is the single interest rate for financing costs and for reinvestment earnings that sets the total gains exactly equal to total costs

In this definition IRR result assumes the use of funds to pay investment costs brings additional costs, either borrowing costs or opportunity costs. Also, incoming returns are reinvested, earning additional gains.

The meaning of IRR magnitude is difficult to interpret because IRR can differ from the actual financing and earnings rate for returns. Therefore, a new Modified Internal Rate of Return (MIRR) metric has been developed to the real financing cost rate and real earnings rate for returns.

MIRR assumes that positive cash flows are reinvested at the firm’s cost of capital, and the initial outlays are financed at the firm’s financial cost. By contrast, the traditional IRR assumes the cash flows from a project are reinvested at the IRR. The MIRR more accurately reflects the cost and profitability of a project.

MIRR function (modified internal rate of return) works similarly to the IRR function, except that it also considers the cost of borrowing the initial investment funds as well as compounded interest earned by reinvesting each cash flow. The MIRR function is flexible enough to accommodate separate interest rates for borrowing and investing cash.

Because the MIRR function calculates compound interest on project earnings or cash shortfalls, the resulting internal rate of return is usually significantly different from the internal rate of return produced by the IRR or XIRR function.

You can find how both functions works below and you can find example worksheet in the “Resource Hub” section for your reference.

There is also another function in Excel which makes IRR calculations more easy and accurate called XIRR.

XIRR is a more powerful function in excel for calculating Internal Rate of Return or Annualise Yield for a schedule of cash flows occurring at irregular intervals. “X” stands for non-uniform cash flows, it is the most important information regarding investments. All decisions with respect to Investments must be taken after knowing the XIRR.

To compute XIRR on an excel sheet is easy. Below you can find IRR calculations example;

I have added downloadable free Excel file on our "Resource Hub" NPV, IRR, XIRR and MIRR calculation examples for your use. Please visit to download.

In summary when we analyse results we need to look at below infographic for our decisions. Key takeaways are;

  • NPV and IRR always lead to the same accept/reject decision for independent projects

  • NPV and IRR may lead to different accept/reject decisions for mutually exclusive projects.

  • Where NPV and IRR give different accept/reject decision then NPV results should be accepted.

  • NPV assumes re-investment of cash inflows at “r” (opportunity cost of capital)

  • IRR assumes reinvestment of cash inflows at IRR.

  • IRR indicates the minimum rate expected by the investors to get their investment back from the project. They definitely get the idea from IRR that how much extra earning are required to cover their cost of capital and net return on their investment.

  • Re-investment of cash inflows at opportunity cost is more realistic, so NPV method is best. NPV should be used to choose between mutually exclusive projects.

  • MIRR assumes cash inflows are reinvested at WACC.

  • MIRR also avoids the problem of multiple IRR’s. MIRR is better than IRR.

  • When there are non-normal cash flows and there is more than one IRRs, use MIRR.

  • IRR is an estimate project’s rate of return, so it is comparable to Yield To Maturity (YTM) on a bond.

This article concludes key financial models and tools in use for capital budgeting and investment decisions. Capital budgeting is the process most companies use to authorise capital spending on long-term projects and on other projects requiring significant investments of capital. Because capital is usually limited in its availability, capital projects are individually evaluated using both quantitative analysis and qualitative information. A Quick recap of these tools are as follows;

  1. Net Present Value (NPV): NPV is used to estimate each potential project’s value by using a Discounted Cash Flow (DCF) valuation.

  2. Internal Rate of Return (IRR): IRR determines the interest yield of the proposed capital project at which the NPV equals zero, which is where the present value of the net cash inflows equals the investment.

  3. Modified Internal Rate of Return (MIRR): MIRR assumes that positive cash flows are reinvested at the firm’s cost of capital, and the initial outlays are financed at the firm’s financial cost.

  4. Profitability Index (PI): The index that divides the present value of the cash flows by the required investment.

  5. Payback Period: The payback measures the length of time it takes a company to recover in cash its initial investment.

  6. Discounted Payback Period: DPP used to determine the profitability of a project. A discounted payback period gives the number of years it takes to break even from undertaking the initial expenditure, by discounting future cash flows and recognising the time value of money. The Net Present Value (NPV) aspect of the discounted payback period does not exist in a payback period in which the gross inflow of future cash flows are not discounted.

Please follow me on for my articles on other financial models as well as other areas of Hospitality business, trends and interpretation methods.

Lets Connect

Lets Connect

  • Email Icon
  • LinkedIn Social Icon
  • Facebook Social Icon
  • Twitter Social Icon
  • Instagram Social Icon
  • YouTube Social  Icon

© Speak2Impress 2020