Internal Rate of Return (IRR): Meaning, Formula and Calculation

An investment does not always involve one payment at the beginning and one amount at the end. You may invest money at different times, receive periodic payouts or make partial withdrawals along the way. Looking only at the final value may not show the complete return experience.

The IRR full form is Internal Rate of Return. It is a return measure that considers both the amount and timing of cash flows. The internal rate of return is particularly useful when money moves in and out at different points during an investment or project.

The phrase internal interest rate is sometimes used informally, while the search phrase IRR rate return is also common. However, Internal Rate of Return is the recognised financial term.

What is the internal rate of return?

Internal Rate of Return is the discount rate at which the Net Present Value, or NPV, of all cash flows becomes zero. At this rate, the present value of the money received equals the present value of the money invested.

For example, a business may make an initial investment, receive income over several years and sell an asset at the end of the project. IRR brings these different cash flows together and expresses the result as a rate for the relevant period.

IRR is not automatically an annualised return. If the cash flows are annual, the result is an annual rate. If the cash flows are monthly, the result is a monthly rate unless it is annualised separately.

A higher IRR indicates a higher calculated return for the specific cash-flow pattern. It does not capture every aspect of an investment, such as risk, liquidity, taxation or the absolute rupee gain.

Why use IRR as a metric?

IRR can be useful because it recognises that money received today is financially different from the same amount received several years later. This is the time value of money.

It can also make comparisons easier where investments have different cash-flow patterns. A project that generates income steadily over time may need to be assessed differently from one that provides a single amount at maturity.

For businesses, IRR is commonly used while evaluating capital expenditure projects. For investors, XIRR can help calculate returns from multiple investments and redemptions made on different dates. It works best as one part of a broader assessment.

Calculation of internal rate of return in Excel

Once the cash flows are listed in sequence, Excel can calculate IRR for regularly spaced periods, such as every month, quarter or year.

1. Enter each cash flow in a single column.
2. Enter the initial investment as a negative number.
3. Enter money received as positive numbers.
4. Use the formula =IRR(B2:B5), replacing the range with the cells that contain the cash flows.

For example:

Using =IRR(B2:B5) gives an IRR of approximately 8.90%.

In this example, the result is an annual rate because the cash flows are one year apart. If the cash flows are monthly, Excel gives a monthly IRR. It can be converted into an annualised rate using:

(1 + monthly IRR)^12 – 1

The figures shown are for illustrative purpose only.

What if cash flows are irregularly spaced?

IRR works when cash flows occur at regular intervals. When they occur on different dates, XIRR is more suitable because it uses the actual date of each investment, withdrawal or redemption.

This is particularly relevant for an SIP. Although instalments may be made every month, each instalment has its own investment date and holding period. XIRR can calculate the return based on an individual investor’s actual transactions.

It is important to distinguish this from a mutual fund scheme’s published return. XIRR reflects the investor’s own investment dates and amounts.

What is the mathematical formula to calculate IRR?

IRR is the rate that solves the following equation:

0 = -C₀ + Σ [Cₜ / (1 + r)ᵗ]

Where:

• C₀ is the initial investment or cash outflow
• Cₜ is the cash flow received or paid during a period
• t is the time period
• r is the Internal Rate of Return

The calculation usually requires repeated estimation, which is why spreadsheet functions and financial calculators are commonly used.

What are the differences between IRR and simple interest returns?

IRR and simple interest are designed for different situations.

IRR vs CAGR and ROI

IRR, CAGR and ROI are all return measures, but each looks at investment performance differently. IRR considers the amount and timing of multiple cash flows, which makes it useful where money is invested or received at different points in time.

CAGR measures the compounded annual growth between an investment’s beginning value and ending value. ROI, on the other hand, shows the total gain or loss as a percentage of the initial investment. It is easy to calculate, but it does not usually account for the time taken to earn that return.

Is IRR the same as ROI?

No. ROI shows the gain or loss relative to the amount invested.

ROI = (Gain or loss / Initial investment) x 100

For example, if ₹1 lakh grows to ₹1.20 lakh, the ROI is 20%. However, this calculation alone does not show whether the gain was earned in one year or five years.

IRR considers the timing of cash flows and gives a rate for the relevant period. It is more suitable where an investment involves several inflows and outflows.

IRR vs NPV

IRR and NPV are closely linked, but they present investment outcomes in different ways. IRR is expressed as a percentage rate, while NPV shows the rupee-value difference between the present value of cash inflows and outflows at a selected discount rate.

Using both measures can be helpful when comparing projects of different sizes or durations. A project may have a high IRR but still generate a smaller absolute financial benefit than another project with a lower IRR.

Internal rate of return example and calculation

Tushar, a working professional in Chennai, makes an initial investment of ₹1,00,000. At the end of the first year, he receives ₹30,000. He receives another ₹40,000 at the end of the second year and ₹50,000 at the end of the third year.

When Tushar enters these cash flows into Excel’s IRR function, the result is approximately 8.90%. Since each cash flow is one year apart, this represents the annual rate associated swith the stated cash-flow pattern.

The figures shown are for illustrative purpose only.

Why is IRR not commonly used for investments like FDs or mutual funds?

A fixed deposit usually has a straightforward cash-flow pattern, such as one deposit followed by interest payments or a maturity amount. The stated interest rate or effective annual yield may therefore be easier to understand than an IRR calculation.

Mutual funds are different because every investor may invest and redeem on different dates. Scheme returns are commonly presented as absolute returns or CAGR for specified periods, while an investor can use XIRR to calculate the return from their own transactions.

Returns on fixed deposits/savings accounts are fixed, however, returns on mutual funds are subject to market risks.

Investing based on IRR

IRR is a useful measurement tool, but it should not be the sole basis for an investment decision. A high IRR may result from earlier cash inflows, even where the absolute rupee gain is modest.

For mutual fund investments, investors may also consider the scheme’s investment objective, risk level, asset allocation, portfolio, expense ratio, benchmark and investment horizon. The return figure should be read in the context of these factors.

What is a good internal rate of return?

There is no single IRR that is suitable for every investment. The interpretation depends on the risk involved, investment duration, inflation, taxes, costs and the returns available from comparable options.

For a business project, IRR may be compared with a relevant hurdle rate or the company’s cost of capital. For individual investors, the return should be considered in relation to financial goals, risk appetite and liquidity needs.

Limitations of using the internal rate of return

IRR can be informative, but it has limitations that need to be kept in view.

• Multiple IRRs: Cash flows that change direction more than once may produce more than one IRR.
• Reinvestment assumption: IRR assumes interim cash inflows can be reinvested at the same rate, which may not be realistic.
• No measure of scale: IRR does not show the absolute rupee gain or loss.
• No direct risk measure: It does not capture market risk, liquidity risk or the certainty of projected cash flows.
• Different durations: Comparing investments solely on IRR can be misleading when their sizes or tenures differ significantly.

Where financing and reinvestment assumptions need to be assessed separately, analysts may also use Modified Internal Rate of Return, or MIRR.

Using IRR with WACC

IRR and Weighted Average Cost of Capital, or WACC, are often used together in corporate finance.

WACC represents a company’s average cost of debt and equity financing. A company may compare a project’s IRR with its WACC while evaluating the project.

• If IRR is higher than WACC, the project’s calculated return is above the company’s estimated financing cost.
• If IRR is lower than WACC, the project’s calculated return is below the estimated financing cost.
• If IRR and WACC are equal, the project’s calculated return broadly matches the estimated financing cost.

This comparison is mainly relevant for business projects and has limited direct relevance for mutual fund investors.

FAQs

What is the IRR full form?

IRR stands for Internal Rate of Return. It is the rate at which the Net Present Value of an investment’s cash inflows and outflows becomes zero.

Is IRR an annualised return?

IRR is a rate for the interval between cash flows. If the cash flows occur annually, IRR is an annual rate. If they occur monthly, IRR is a monthly rate unless annualised separately.

Can IRR be negative?

Yes. IRR can be negative when the cash inflows do not recover the initial cash outflow on a time-adjusted basis.

Why does Excel show a #NUM! error for IRR?

Excel may show a #NUM! error if the cash-flow range does not contain at least one positive and one negative value, or if it cannot find a rate that makes the calculation work. Check the cash-flow sequence and use a different guess value if needed.