What is the formula for calculating internal rate of return (IRR) in Excel?

The internal rate of return (IRR) is a 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? If an investment will require capital that could be used elsewhere, the IRR is the lowest level of return from the project that is acceptable in order to justify the investment.

If a project is expected to have an IRR greater than the rate used to discount the cash flows, then the project adds value to the business. If the IRR is less than the discount rate, it destroys value. The 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 understands what a 25% rate means, compared to a hypothetical dollar equivalent (the way the NPV is expressed). Unfortunately, there are also several critical disadvantages with using the IRR to value projects. 

You should always pick the project with the highest NPV, not necessarily the highest IRR, because financial performance is measured in dollars. If faced with two projects with similar risks, Project A with 25% IRR and Project B with 50% IRR, but Project A has a higher NPV because it is longer term, you would pick Project A.

The second big issue with IRR analysis is that it assumes you can continue to reinvest any incremental cash flow at the same IRR, which may not be possible. A more conservative approach is the Modified IRR (MIRR), which assumes reinvestment of future cash flows at a lower discount rate.

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:

Or this calculation could be broken out by individual cash flows. The formula for a project that has an initial capital outlay and three cash flows follows:

In this equation: 

• CF_t = net after-tax cash inflow-outflows during a single period t
• r = Internal rate of return that could be earned in alternative investments
• t = Time period cash flow is received

If you are unfamiliar with this sort of calculation, here is an easier way to remember the concept of NPV:

NPV = (Today's value of the expected future cash flows) – (Today's value of invested cash)

Broken down, each period's after-tax cash flow at time t 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 equals zero.

Financial calculators and software like Microsoft Excel contain specific functions for calculating IRR. To determine the IRR of a given project, you first need to 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.

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 works best when it is transparent, detailed and easy to audit. The trouble with piling all 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 of an IRR analysis with cash flows that are 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 grow by $50,000 for each of the next four years.

You can break out a schedule as follows (click on image to expand):

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

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

Keep in mind that the IRR is not 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 IRR helps managers determine which potential projects add value and are worth undertaking. The advantage from expressing project values 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 project in dollar terms. Multiple projects can have the same IRR but dramatically different returns due to the timing and size of cash flows, the amount of leverage used or differences in return assumptions. IRR analysis also assumes a constant reinvestment rate, which may be higher than a conservative reinvestment rate.