How to Calculate IRR(Internal Rate of Return) in Excel

What is IRR(Internal Rate of Return)

The Internal Rate of Return(IRR) is the discount rate that sets the Net Present Value(NPV) of all future cash flows of an investment to zero.

The formula is:

$$0=NPV=\sum _{n=0}^N\frac{CFn}{(1+IRR{)}^n}$$

• NPV = Net present Value
• N = Total number of time periods
• n = Time period
• CF0 = Initial investment
• CF1, CF2, CF3, etc. = Cash flows
• IRR = Internal rate of return

If the NPV of an investment is zero, that doesn't mean it's a good or bad investment, it just means you will earn the IRR(discount rate) as your rate of return.

Excel has three functions for calculates the internal rate of return.

• Internal Rate of Return(IRR) - calculates the internal rate of return for a series of cash flows.
• Modified Internal Rate of Return(MIRR) - works with interest rates for borrowing and investing.
• Internal Rate of Return with time periods(XIRR) - calculates a more accurate internal rate of return as it considers time periods.

How to use the IRR Function in Excel

The IRR function in Excel will return the Internal Rate Return for a given cash flow, that is, the initial investment value and a series of net income values.

IRR Functions Formula

=IRR(values,[guess])

The IRR function uses the following arguments:

1. Values(reuired argument)
   • This is an array of values that represent the series of cash flows.
   • Cash flows include investment and net income values.
   • Values can be a reference to a range of cells containing values.
2. [Guess](optional argument)
   • This is a number guessed by the user that is close to the expected internal rate of return (as there can be two solutions for the internal rate of return).
   • if omitted, the function will take a default value of 0.1(10%)

IRR Function Examples

Take HKMC Annuity Plan Examples of Guranteed Monthly Annuity Payment Table for example.

If you are a male, invest HK$ 1,000,000 in the last month of your 59-year-old and then receive a monthly payment from your 60-year-old until you pass away after the end of your 85-year-old.

Then, your monthly IRR function formula is =IRR(C3:C315) and the value is 0.52%, it means your yearly IRR is 6.39%. I think it is a decent investment.

Download the Excel