Calculate Internal Rate of Return (IRR)

In summary

IRR measures the annualised return implied by an investment's projected cash flows.

• Meaning: IRR is the discount rate that makes NPV equal to zero.
• Formula: IRR is calculated by solving the NPV equation for the discount rate.
• Calculation: Excel and Google Sheets can calculate IRR using the IRR function.
• Interpretation: IRR can be compared with a required return or cost of capital.
• Limitations: IRR can be difficult to interpret with irregular or changing cash flows.

IRR works best when analysed alongside NPV, investment size, risk, and cash flow assumptions.

What is the Internal Rate of Return (IRR)?

Internal Rate of Return (IRR) is a financial measure used to assess an investment's potential return. It represents the discount rate that makes the net present value of cash flows equal to zero. In simple terms, IRR indicates the annualised return implied by projected cash flows. It considers the initial investment, subsequent cash flows, and their respective timing.

For example, suppose you invest Rs. 1,00,000 initially and receive Rs. 30,000 annually for four years.

The cash flows are:

• Year 0: -Rs. 1,00,000
• Year 1: Rs. 30,000
• Year 2: Rs. 30,000
• Year 3: Rs. 30,000
• Year 4: Rs. 30,000

Using the IRR formula, the IRR is approximately 7.71%.

The IRR represents the annual rate that makes those cash flows equal the initial investment. Investors can then compare this rate with their required return or cost of capital. IRR should not be viewed as a standalone measure of investment quality. Risk, investment size, cash flow stability, and NPV should also be considered.

How is the Internal Rate of Return (IRR) calculated?

The IRR is calculated by finding the discount rate that makes NPV equal to zero.

NPV = Σ [Ct / (1 + IRR)^t] = 0

Where:

• Ct: Cash flow during period t
• IRR: Internal Rate of Return
• t: Relevant time period

The initial investment is normally entered as a negative cash flow. Future receipts are entered as positive cash flows.

For example, consider these projected cash flows:

The IRR is the rate that makes the NPV of these cash flows zero. Manual calculation can require repeated trial calculations. Spreadsheet functions can calculate IRR more efficiently.

Calculating Internal Rate of Return (IRR)

Calculating IRR requires a complete record of the investment's cash flows. Both cash flow amounts and their timing affect the result.

Two factors are particularly important:

1. Cash inflows and outflows: Include the initial investment and all relevant future cash flows.

2. Timing of cash flows: IRR considers when each cash flow occurs, not just its amount.

   
   

For example, receiving Rs. 50,000 earlier can affect IRR differently from receiving it later. Therefore, cash flows should be recorded against their correct periods. IRR is generally most straightforward when there is one initial outflow. Multiple changes between positive and negative cash flows can create interpretation challenges.

1. Identify the cash flows

List every investment outflow and expected cash inflow. Include the initial investment as a negative value.

For example:

• Initial investment: -Rs. 1,00,000
• Year 1 return: Rs. 30,000
• Year 2 return: Rs. 30,000
• Year 3 return: Rs. 30,000

• Year 4 return: Rs. 50,000

  
  

2. Arrange the cash flows by period

Place each cash flow against its corresponding period. Use consistent periods throughout the calculation.

3. Apply the IRR formula or spreadsheet function

Manual calculation involves finding the rate that makes NPV equal zero. Excel and Google Sheets can perform this calculation using the IRR function.

4. Interpret the result

Compare the calculated IRR with your required return or relevant benchmark. A higher IRR does not automatically indicate a better investment.

The comparison should also consider investment size, risk, duration, and cash flow patterns.

Calculating IRR in Excel and Google Sheets

Excel and Google Sheets provide built-in functions for calculating IRR.

These functions eliminate the need for repeated manual calculations.

In Excel

Enter the cash flows in consecutive cells. For example, enter the following values in cells A1 to A5:

• A1: -100000
• A2: 30000
• A3: 30000
• A4: 30000

• A5: 50000

  
  

Enter the following formula in another cell:

=IRR(A1)

The spreadsheet will return the IRR based on those periodic cash flows.

In Google Sheets

Enter the same cash flow values in consecutive cells. Then use the same formula:

=IRR(A1)

The result represents the annualised IRR for those periodic cash flows. For cash flows occurring on specific dates, the XIRR function may be more appropriate. XIRR considers the actual dates of individual cash flows.

Also Read : How to calculate your employers EPF contribution

IRR changes when the amount or timing of investment cash flows changes. Several factors can therefore influence the calculated IRR.

For example, receiving the same proceeds earlier generally improves the implied annualised return. Conversely, delaying those proceeds can reduce the calculated IRR.

Changes in investment costs can also materially affect the result. Therefore, IRR should always be assessed alongside the underlying cash flow assumptions.

How is IRR interpreted?

• Measures an annualised return: IRR represents the discount rate implied by projected cash flows.
• Considers timing: Earlier and later cash flows can produce different IRR results.
• Supports comparisons: IRR can help compare investments with similar cash flow structures.
• Can be compared with required returns: Investors may compare IRR with their target return.

• Should not be used alone: NPV, risk, investment size, and cash flow stability also matter.

  
  

For example, an investment with a 12% IRR implies a 12% annualised return under its cash flow assumptions. It does not guarantee that the investor will actually earn 12% annually. A higher IRR can also arise from a smaller investment with limited absolute returns. Therefore, IRR should be considered alongside the investment's scale and expected cash generation.

What are the limitations of using internal rate of return?

IRR is useful, but it has several limitations that investors should understand.

1. It can ignore investment scale: A smaller project may have a higher IRR but lower absolute returns.
2. It relies on cash flow assumptions: Changes in projected cash flows can materially change the calculated IRR.
3. It can produce multiple results: Certain cash flow patterns can generate more than one IRR.
4. It may mislead with irregular cash flows: Non-conventional cash flows can make IRR difficult to interpret.
5. It can differ from actual investment returns: Projected IRR does not guarantee realised returns.
6. It should be considered with NPV: NPV can provide additional context when comparing investment opportunities.

IRR also assumes periodic cash flows when using the standard IRR function. For irregularly dated cash flows, XIRR can provide a more suitable calculation.

Conclusion

Calculating Internal Rate of Return helps investors evaluate returns implied by projected cash flows. It identifies the discount rate at which the investment's NPV becomes zero. IRR can support investment comparisons when cash flow patterns are reasonably comparable. However, it should not replace analysis of risk, NPV, investment size, or cash flow assumptions.

If you are considering a safe investment option, a fixed deposit can provide defined returns. Bajaj Finance Fixed Deposit currently offers rates up to 8.15% p.a. for senior citizens. Rates are applicable as per the prevailing terms and effective 0.40% 

Calculate your expected investment returns with the help of our investment calculators