A:

The internal rate of return (IRR) is the core component of capital budgeting and corporate finance. Businesses use it to determine which discount rate makes the present value of future after-tax cash flows equal the initial cost of the capital investment. Or, to put it more simply: What discount rate would cause the net present value (NPV) of a project to be $0? We expect that projects to grow our business will give us some return over time, so what is the lowest level of return we can tolerate? The lowest level is always the cost of capital to fund the project.

 

If a project is expected to have an IRR greater than the rate used to discount the cash flows, then the project adds value, while if the IRR is less than the discount rate, it destroys value.  This decision process to accept or reject a project is known as the IRR Rule

One advantage of using IRR, which is expressed as a percentage, is that it normalizes returns – everyone understand what a 25% rate means, compared to the hypothetical dollar equivalent (the way the NPV is expressed).  Unfortunately, there are also several critical disadvantages with using the IRR to value projects. 

In the first place, you should always pick the project with the highest NPV, not necessarily the highest IRR – because, at the end of the day, your financial statements are measured in dollars, not percents.  If faced with two projects, Project A with 25% IRR and Project B with 50% IRR, but Project A has a higher NPV, you would pick Project A.   The second big issue is that mathematically, the IRR assumes you can always continue to reinvest any incremental cash flow at the same IRR, which is infrequently the case.  A more conservative approach is the Modified IRR (MIRR), which assumes reinvestment at the discount rate.  

The IRR Formula for Excel

The IRR cannot be derived easily. The only way to calculate it by hand is through trial and error, because you are trying to arrive at whatever rate which makes the NPV equal to zero.  For this reason, we'll start with calculating NPV:

NPV= ∑ {After-Tax Cash Flow / (1+r)^t} – Initial Investment

Broken down, each period's after-tax cash flow at time t is is discounted by some rate, r.  The sum of all these discounted cash flows is then offset by the initial investment, which equals the current NPV. To find the IRR, you would need to "reverse engineer" what r is required so that the NPV equaled zero.

Financial calculators and software like Microsoft Excel contain specific functions for calculating IRR, but any calculation is only as good as the data driving it. To determine the IRR of a given project, you need to first reasonably estimate the initial outlay (the cost of capital investment), and then all the subsequent future cash flows. In almost every case, arriving at this input data is more complex than the actual calculation performed.  

Calculating IRR in Excel

There are two ways to calculate IRR in Excel:

  • using one of the three built-in IRR formulas
  • breaking out the component cash flows and calculating each step individually, then using those calculations as inputs to an IRR formula.  As we detailed above, since the IRR is a derivation there is no easy way to break it out by hand.  

The second method is preferable because financial modeling best practices require calculations to be transparent and easy to audit.  The trouble with piling all of the calculations into a formula is that you can't easily see what numbers go where, or what numbers are user inputs or hard-coded. 

Here is a simple example. What makes it simple, among other things, is that the timing of cash flows is both known and consistent (one year apart).

Assume a company is assessing the profitability of Project X. Project X requires $250,000 in funding and is expected to generate $100,000 in after-tax cash flows the first year, and then grow by $50,000 for each of the next four years.

You can break out a schedule as follows (if table is hard to read, right-click and hit "view image"):

The initial investment is always negative, because it represents an outflow.  You are spending something now, and anticipating results later. Each subsequent cash flow could be positive or negative; it depends entirely on the estimates of what the project delivers in the future. 

In this case, we get an IRR of 56.77%.  Given our assumption of a weighted average cost of capital (WACC) of 10%, the project adds value.  

Keep in mind the limitations of IRR: It will not show the actual dollar value of the project, which is why we broke out the NPV calculation separately.  Also, recall that the IRR assumes we can constantly reinvest and receive a return of 56.77%, which is unlikely.  For this reason, we assumed incremental returns at the risk-free rate of 2%, giving us an MIRR of 33%.  

The Bottom Line

The IRR helps managers determine which potential projects add value and are worth undertaking.  The advantage from expressing project value as a rate is the clear hurdle it provides – as long as the financing cost is less than the rate of potential return, the project adds value.  

The disadvantage to this tool is that the IRR is only as accurate as the assumptions that drive it, and that a higher rate does not necessarily mean the highest value-add project.  Multiple projects can have the same IRR but dramatically different return profiles due to the timing and size of cash flows, the amount of leverage used, or differences in return assumptions. Another factor to keep in mind: IRR's assumption of a constant reinvestment rate, which may well be higher than a conservative risk-free rate.

 

RELATED FAQS
  1. Which is better for capital budgeting, IRR or NPV?

    All other things being equal, using IRR and NPV measurements to evaluate projects often results in the same findings. However, ... Read Answer >>
  2. What is the formula for calculating the internal rate of return (IRR)?

    Learn about the internal rate of return, an important concept in determining the relative attractiveness of different investments. Read Answer >>
  3. How do hurdle rate MARR and internal rate of return IRR relate?

    In capital budgeting, projects are evaluated by comparing the internal rate of return (IRR) to the hurdle rate, also known ... Read Answer >>
  4. How do you use internal rate of return to calculate a capital budget?

    Learn about how the internal rate of return is used in the creation of a capital budget along with net present value and ... Read Answer >>
  5. What are the main differences between compound annual growth rate (CAGR) and internal ...

    The compound annual growth rate (CAGR), measures the return on an investment over a certain period of time. The internal ... Read Answer >>
  6. How can I calculate the hurdle rate in Excel?

    Learn how to evaluate potential investments. Common desktop software provides functions that can help make the decision with ... Read Answer >>
Related Articles
  1. Small Business

    Capital Budgeting: Which is Better, IRR or NPV?

    Using internal rate of return and net present value for capital budgeting evaluations often end in the same result. But there are times when using NPV to discount cash flows makes more sense.
  2. Investing

    An Introduction To Capital Budgeting

    We look at three widely used valuation methods and figure out how companies justify spending.
  3. Financial Advisor

    How to Compare Permanent Life Insurance Policies

    How you can use the internal rate of return to compare and purchase a permanent life insurance policy.
  4. Financial Advisor

    A Guide on the Risk-Adjusted Discount Rate

    When a project or investment faces higher amounts of risk or uncertainty, it may be appropriate to utilize the risk-adjusted discount rate.
  5. Investing

    How to Calculate Required Rate of Return

    The required rate of return is used by investors and corporate-finance professionals to evaluate investments. In this article, we explore the various ways it can be calculated and put to use. ...
  6. Investing

    Top 3 Pitfalls Of Discounted Cash Flow Analysis

    The Discounted Cash Flow (DCF) method can be difficult to apply to real-life valuations. Find out where it comes up short.
  7. Investing

    Free cash flow yield: The best fundamental indicator

    Cash in the bank is what every company strives to achieve. Find out how to determine how much a company is generating and keeping.
  8. Investing

    Style Matters In Financial Modeling

    If you're looking to get a job as an analyst, you'll need to know how to work it.
RELATED TERMS
  1. Conventional Cash Flow

    Conventional cash flow is a series of inward and outward cash ...
  2. Pooled Internal Rate Of Return - PIRR

    Pooled internal rate of return is a method of calculating overall ...
  3. Initial Cash Flow

    Initial cash flow is the amount of money paid out or received ...
  4. Incremental Cash Flow

    The additional operating cash flow that an organization receives ...
  5. Rate of Return

    A rate of return is the gain or loss on an investment over a ...
  6. Accounting Rate of Return - ARR

    The amount of profit, or return, that an individual can expect ...
Hot Definitions
  1. Net Present Value - NPV

    Net Present Value (NPV) is the difference between the present value of cash inflows and the present value of cash outflows ...
  2. Price-Earnings Ratio - P/E Ratio

    The Price-to-Earnings Ratio or P/E ratio is a ratio for valuing a company that measures its current share price relative ...
  3. Internal Rate of Return - IRR

    Internal Rate of Return (IRR) is a metric used in capital budgeting to estimate the profitability of potential investments.
  4. Limit Order

    An order placed with a brokerage to buy or sell a set number of shares at a specified price or better.
  5. Current Ratio

    The current ratio is a liquidity ratio that measures a company's ability to pay short-term and long-term obligations.
  6. Return on Investment (ROI)

    Return on Investment (ROI) is a performance measure used to evaluate the efficiency of an investment or compare the efficiency ...
Trading Center