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 to the initial cost of the capital investment.

The internal rate of return is a way to find 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.

Calculating the internal rate of return takes trial and error when solving manually, because you are trying to find a rate at which the net present value (NPV) of future cash flows is zero. To calculate IRR in Excel, you can use the Insert Function command to add the IRR function, or you can break out component cash flows and calculate each step of the IRR formula individually. The syntax for the IRR function in Excel is IRR(values, [guess]), where "guess" is an optional argument.

Understanding Internal Rate of Return

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.

Both IRR and NPV can be used to choose which projects to move forward, but often you'll want to choose 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 long-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 Formula

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:

$\begin{aligned} &NPV = \sum_{t = 0}^n \frac { CF_t }{ (1 + r)^t } \\ &\textbf{where:} \\ &CF_t = \text{net after-tax cash inflow-outflows during} \\ &\text{a single period } t \\ &r = \text{internal rate of return that could be earned in} \\ &\text{alternative investments} \\ &t = \text{time period cash flow is received} \\ &n = \text{number of individual cash flows} \\ \end{aligned}$​NPV=t=0∑n​(1+r)tCFt​​where:CFt​=net after-tax cash inflow-outflows duringa single period tr=internal rate of return that could be earned inalternative investmentst=time period cash flow is receivedn=number of individual cash flows​

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:

$\begin{aligned} &NPV = \frac {CF_0}{(1 + r)^0} + \frac {CF_1}{(1 + r)^1} + \frac {CF_2}{(1 + r)^2} + \frac {CF_3}{(1 + r)^3}\\ \end{aligned}$​NPV=(1+r)0CF0​​+(1+r)1CF1​​+(1+r)2CF2​​+(1+r)3CF3​​​

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 complicated 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 (the IRR, MIRR, or XIRR functions)
• Breaking out the component cash flows and calculating each step individually, then using those calculations as inputs to an IRR formula; 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. 

Example of Calculating IRR With Excel

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.

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 a MIRR of 33%.

The Bottom Line

Using Excel to calculate the internal rate of return requires the IRR function and reference cells that list the cash flows for a series of periods, plus an optional guess for the rate of return. Excel makes it easier to calculate the IRR of multiple potential investments, allowing you to compare them and choose the one that provides the best return.