Internal Rate of Return: Meaning, Calculation, Formula

When you invest in stocks, mutual funds or other financial products, you might often see terms like rate of return or annualised return. One less commonly discussed but useful concept is the Internal Rate of Return (IRR). This measure helps capture the time value of money and the pattern of cash flows from an investment. For Indian retail investors, learning what the internal rate of return is may offer a clearer way to evaluate investments or compare potential returns, especially when multiple cash flows are involved.

This article explains the IRR meaning, how to calculate it using Excel, and how it compares with other return measures.

Table of contents

• What is the internal rate of return?
• Why use IRR as a metric?
• Calculation of internal rate of return in excel
• What if cash flows are irregularly spaced?
• What is the mathematical formula to calculate IRR?
• IRR vs CAGR vs ROI
• Why is IRR not commonly used for investments like FD (Fixed Deposit) or mutual funds?
• Limitations of using the internal rate of return

What is the internal rate of return?

The Internal Rate of Return (IRR) is a way to measure how much an investment earns over time, considering both the amount and timing of all cash flows — money going out (your investment) and money coming in (returns).

In simple terms, IRR is the rate at which the present value of all future cash inflows equals the initial investment cost, or the point where the Net Present Value (NPV) becomes zero. It tells you the annualised rate of return your investment has generated over a certain period.

A higher IRR means the investment has earned higher returns per year. However, IRR should not be viewed in isolation — it is only suitable for comparing investments of similar scale and duration and it assumes that interim cash flows can be reinvested at the same rate, which may not always happen in reality.

Past performance may or may not be sustained in future.

Read Also: What is XIRR in Mutual Funds? Meaning and Importance

Why use IRR as a metric?

There are several reasons IRR may be a useful metric:

• It captures the timing of cash flows, not just total amounts. IRR factors in the time value of money — it recognises that receiving potential returns early is better than getting the same amount later.
• It provides a single rate (expressed in percent) which may make comparison easier across options with different cash-flow patterns.
• For projects or investments where cash flows are not just “invest now, get back at end”, IRR helps in evaluating whether the investment may deliver acceptable potential returns relative to required rate or cost of capital.

However, it’s important to remember, IRR is only one of many metrics and IRR calculations have some limitations including potential multiple solution scenarios for non-conventional cash flows and assumptions about reinvestment rates. It may help compare options, but it does not guarantee that an investment is suited for your personal risk-profile or liquidity needs. Investors should consider IRR alongside other metrics such as absolute returns, XIRR, and risk-adjusted performance measures.

Calculation of internal rate of return in excel

Using spreadsheet tools makes IRR calculation manageable, especially when cash flows occur regularly or irregularly. In Excel (or Google Sheets) you could use the built-in IRR() function.

Here’s how you might do it:

1. List your cash flows: for example, Year 0 (initial investment) = –Rs. 1,00,000; Year 1 = +Rs. 25,000; Year 2 = +Rs. 30,000; Year 3 = +Rs. 40,000.
2. In Excel, select a cell and type =IRR(B2:B5) (assuming B2:B5 contains those numbers). That gives you the IRR per annum.
3. If your cash flows occur on different dates, say you invest June 2025, receive a cash flow March 2026, etc, then use =XIRR(values, dates) to handle the irregular spacing.
4. Interpret the result: if Excel shows 8.9 %, that implies the investment’s timing-adjusted potential return is ~8.9 % per annum (given the cash‐flow pattern).

Using Excel IRR calculation simplifies what would otherwise be a repetitive trial-and-error process.

Example for illustrative purposes only. This figure is purely illustrative and not a guarantee of actual returns.

Read Also: A Simple Guide to Calculate Mutual Fund Returns Online

What if cash flows are irregularly spaced?

When cash flows occur at irregular intervals, you may use XIRR (Extended Internal Rate of Return). XIRR allows you to input the actual calendar date for each cash flow and computes the annualised potential return accordingly.

SIPs have regular contributions but on specific calendar dates. Since each instalment has a different investment date, XIRR is used to correctly annualise potential returns.

Put simply, XIRR helps calculate the estimated Internal Rate of Return when cash-flow dates are irregular, potentially making it suitable for investments where money may be invested or withdrawn on different dates.

What is the mathematical formula to calculate IRR?

The IRR is found by solving the NPV (Net Present Value) equation for the discount rate that yields zero NPV. The formula is expressed as:

0 = Σ (Ct / (1 + IRR)^t) – C0, where t = 1 to T

where:

• C₀= the initial investment cost (cash outflow at t=0)
• t= time period
• C_t= net cash inflow (or outflow if negative) at time period
• T= the total number of periods (years, quarters, etc.) in the project’s life
• IRR = the internal rate of return (the unknown to solve for)

IRR vs CAGR vs ROI

ROI (Return on investment) is the net profit divided by the initial investment, expressed as a percentage. It doesn’t account for how long it took to earn that potential return.

CAGR (Compound annual growth rate) is the annualised rate at which an investment grows every year, assuming returns were reinvested and there were no interim cash flows.

IRR is an annualized return that accounts for all cash flows and their timing; it answers what is the internal rate of returns for the entire series of cash.

Example

Let’s illustrate with a simple example:

• You invest Rs. 1,00,000 at time 0 (i.e., today).
• At the end of Year 1 you receive Rs. 30,000; at the end of Year 2 you receive Rs. 40,000; end of Year 3 you receive Rs. 50,000.

We look for IRR such that:

0=-100,000+30,000/((1+IRR)^1 )+40,000/((1+IRR)^2 )+50,000/((1+IRR)^3 )

Using Excel, it would come out to~8.8963% This means you might be looking at a roughly 8.9 % annualised potential return given the timing of those cash flows.

Example for illustrative purposes only.

Why is IRR not commonly used for investments like FD (Fixed Deposit) or mutual funds?

For a FD, the cash flow pattern is simple, one outflow (deposit) and one inflow (maturity with interest). Thus, the IRR equals the FD’s effective annualised rate because cash flows are simple and predetermined.

In mutual funds, performance is reported via CAGR (Compound Annual Growth Rate), not IRR. Each investor’s actual IRR (especially with SIPs) will differ based on their cash flow timing, so fund houses don’t publish IRR figures. Also, IRR calculations are complex, so fund communications stick to simpler metrics. AMFI (Association of Mutual Funds in India) mandates CAGR reporting, not IRR, to standardise comparison and reporting.

Limitations of using the internal rate of return

• Multiple IRRs: There might be multiple IRRs if cash flow patterns are unusual, which could be confusing.
• Reinvestment assumption: IRR assumes interim cash flows get reinvested at the same rate, which might not be realistic.
• Ignores scale and risk: IRR is just a percentage; it doesn’t reflect an investment’s size or risk.

Frequently Asked Questions

What does IRR indicate about an investment's profitability?

IRR indicates an investment's annual return rate. While a higher IRR generally suggests better profitability in percentage terms, investors should also consider other factors such as investment duration, risk profile, and capital requirements when comparing different opportunities. Moreover, IRR is based on historical data. Past performance may or may not be sustained in future.

How is IRR calculated?

IRR is calculated by finding the discount rate that makes NPV equal zero:

0=∑_(t=0)^T C_t/((1+IRR)^t )

Why is IRR important for evaluating investment opportunities?

Because IRR accounts for when cash flows occur, it gives a realistic annual potential return that lets you compare different investments fairly. If an investment’s IRR exceeds your target potential return, it might be considered a favourable sign.

What are the limitations of using IRR as a sole investment metric?

There might be multiple IRRs if cash flow patterns are unusual, it assumes interim cash flows are reinvested at the same rate, and it doesn’t reflect an investment’s size or risk.