How To Calculate Internal Rate of Return (IRR)

What Is the Internal Rate of Return?

The internal rate of return, or IRR, is the rate of return of an investment where external factors, such as inflation or the cost of capital, aren’t considered. IRR can be used to measure the actual return on an investment made in the past, or it can be used to estimate the rate of return of a future investment.

The easiest way to understand IRR is by considering cash flows, with a negative cash flow occurring at the time of the initial investment, then either positive or negative cash flows thereafter. By definition:

IRR is the annualized effective compounded return rate that sets the net present value ((NPV)) of all cash flows from the investment, both positive and negative, equal to zero.

NPV is always an amount, and IRR is always a percentage that reflects the interest yield from the investment. Put another way, IRR is:

• The interest rate at which the net present value of the future cash flows is equal to the initial investment.
• The interest rate at which the total present value of costs, or negative cash flows, equals the total present value of the benefits, or positive cash flows.

IRR reflects what’s known as the time value of money which is a financial concept whereby an amount of money is worth more at the present time than the same amount of money at a future date. That is, a specific return on investment received at the current time is worth more than the same return received at a later time.

In the case of a fixed-income investment, such as a Treasury bond, where a bond is bought once and interest at a specified interest rate is paid every time period, such as yearly, the IRR is equal to the specified interest rate.

IRR Formula

The formula for calculating IRR is shown below, and the scary-looking symbol, the Greek letter Sigma, just means the summation of a series of terms:

• N = the total number of periods
• n = the current period, usually in years
• r = the internal rate of return
• C = yearly interest received

For an initial amount of $12,000 invested over a three-year period with returns of $3,600, $5,400, and $4,800, the expanded IRR formula would look like this:

What we don’t know in the above equation is r, or the IRR. We could try mathematically solving for r, but a much easier way is by using a spreadsheet such as Excel.

How to Calculate IRR Using a Spreadsheet

The spreadsheet below shows the IRR for two separate five-year investments, the first having an initial investment of $70,000 and the second having an initial investment of $45,000.

Returns on the first investment start out strong, returning 17% in the first year and going up to 37% in the fifth and final year. The second investment starts out similarly, returning 18% in its first year, and going up to 36% in its final year. So, which investment provides more bang for the buck?

As we can see, the second investment, while more modest than the first, provides a slightly higher internal rate of return.

To calculate the IRR using a spreadsheet:

Step 1: Add Time Period In Column A

Place a count of the periods which are typically reflected in years in a column, being sure to start with period 0.

Step 2: Add Amounts In Column B

Place the amounts starting with the initial amount which is always negative in an adjacent column.

Step 3: Add Descriptions In Column C

If you’d like, add descriptions in another column. This is optional and simply for your own reference.

Step 4: Run the Equation

In a cell below the amounts column, add an equal sign followed by the IRR function which in most spreadsheets is designated as “IRR”, then in parentheses add the column/row designation of the initial amount and the column/row designation of the last amount.

In the examples above, the formulas entered were:

=IRR(B2:B7) and =IRR(B12:B17)

Purpose & Uses for Internal Rate of Return

IRR can be used to analyze:

• Investment returns: In cases where interest payments or cash dividends aren’t reinvested back into an investment, such as in the case of annuities, IRR can determine the true return on investment.
• Capital planning: IRR allows companies to compare the profitability of creating a new operation with that of expanding an existing operation. For example, the ABC Widget Company could compare the cost of creating a new widget assembly line with that of speeding up its existing assembly line. The new widget line will cost $250,000 and can produce 50 widgets per hour. Speeding up the existing line will only cost $100,000, and it will be capable of producing 25 widgets per hour. If each widget sells for $100 and provides the company with a $55 profit, we can calculate the IRR to see which option is more cost-effective.
• The money-weighted rate of return (MWRR): The MWRR determines what rate of return is needed given an initial investment amount and changes to cash flows over the course of the investment period.
• Stock buyback programs: IRR allows a company to evaluate if it is more profitable for it to allocate capital to buy back its shares rather than use those funds in other ways, such as buying new equipment or expanding its operations.
• Insurance policies: When comparing the premiums charged by a life insurance company against the amount of its death benefit, an individual can determine those policies that are more beneficial. The IRR is always higher during the early years of a policy because the insured has paid in a smaller amount of money while receiving the same benefit.

IRR vs. ROI & CAGR

CAGR stands for compound annual growth rate, and it is a measure of the return on an investment over a given period of time. The difference between IRR and CAGR is that IRR is suitable for more complicated investments and projects, such as those having differing cash outflows and cash inflows. While IRR is difficult to calculate manually, the CAGR is easily calculated by hand.

The formula for calculating CAGR is:

CAGR = ([(Ending Value/Beginning Value) ^ (1 /# of years)] – 1) * 100

• Ending Value = the value of an individual investment, a portfolio, or a business metric at the end of the time period
• Beginning Value = the value of an individual investment, a portfolio, or a business metric at the beginning of the time period
• # of years = partial years which can be converted into full years by determining the number of days and then dividing by 365 days per year

For example, an initial value of $1,000 and a final value of $2,000 over the course of four years would yield a CAGR of 21.7%.

Return on investment, or ROI, is the percentage increase or decrease of an investment over a given period of time. The formula for calculating ROI is:

ROI = [(Expected amount – initial amount)/initial amount] * 100

For example, an initial investment of $1,000 that is currently worth $1,400 has a ROI of 40%

[($1,400 – $1,000)/$1,000] * 100 = 40%

Over the course of the first year, ROI and IRR will be almost the same, however, they will begin to differ across longer time periods.

IRR Limitations

Because IRR simply compares cash flows to the amount of capital outlay that generates those cash flows, it doesn’t take into consideration the size of projects, where larger projects can generate significantly more profit over longer periods of time.

Also, the IRR ignores reinvestment rates because while the IRR allows for the calculation of the value of future cash inflows, investments having a high IRR can’t always be reinvested at the same rate as the IRR.

Bottom Line

Any project having an IRR that is greater than its cost of capital should be a profitable investment. Companies planning capital projects will often determine a required rate of return (RRR) which is the minimum rate of return in order for the investment to be worthwhile. Projects having an IRR that is higher than their RRR will be profitable.