IRR Meaning: How to Calculate It in Excel

What IRR Means, Examples and How to Calculate It in Excel

Why should you understand IRR?

Have you ever received a call or met a stranger on social media who tried to sell you a financial product?

You politely listen. If you happen to need the product, the sales approach may press the right pain points and use exaggerated or misleading attractions until you feel that you have no reason to refuse.

You still have some doubts. You remember hearing that the product may not be good, but you cannot explain what is wrong with it.

Learning this formula is already enough to help you make a basic comparison, and many agents do not know it.

What is IRR?

IRR is short for internal rate of return, also described in the source as an annualised return.

IRR can be used widely. Any financial product with money flowing in and out can use it; it is not limited to one type of product.

The IRR formula looks complicated, but you do not need to memorise it. You only need Excel or Google Sheets to calculate it.

Example 1

Suppose a savings policy requires you to invest RM10,000 and wait five years before receiving RM12,730.

An agent may tell you that the return is an attractive 27.3%.

You know it takes five years, so you divide it by five and get 5.46%. However, the actual annualised return is only 4.95%.

The Excel formula

It is simple. Enter the years and cash flows in Excel as shown in Example 1, then use =IRR(all cash flows).

Example 2

Enter the cash flows in Excel based on what the agent tells you: put the premiums you pay as negative numbers and the money you receive as positive numbers.

At the bottom, enter the IRR formula, =IRR(all cash flows), and press Enter. Excel will calculate the IRR.

Example 2 is also a savings policy. You pay for six years, wait thirty years, receive money every year and receive another amount after thirty years.

The numbers look quite attractive on their own, don't they?

However, the calculated IRR is only 2.4%.

Why is it different from the return described by the agent?

How can it be lower than a fixed deposit?

Summary

IRR is only one basic measure, but it is already useful for comparison.

If you have invested, you can calculate it for yourself. Many people believe they are making money, but the result may look different when it is properly calculated.

Do not keep estimating roughly. I hope you have learnt something useful and can avoid being taken advantage of.