IRR, XIRR : Calculating investment return

Calculating return on investment is an important aspect of any investment review. Whether investment mode is stock, mutual fund, debts, savings account etc. you need tools to calculate the return on investment so that relative effectiveness of different investments can be ascertained. Herein we will discuss two formulas which are very effective in calculating return on investment.

IRR (Internal Rate of Return)

Discount rate at which present value of a series of investments is equal to the present value of return of those investments. In layman language, this is the annualized return for a series of investment made at regular interval.

Key thing to note here is that while amount invested may vary, interval at which investment is done has to be same or equidistant.

Calculation

We can use excel’s in-built IRR formula:

• In excel, enter all payments made. For investment made put “-“ before the values.
• Enter all payments received.
• Use IRR formula = IRR (values) wherein values is the range of cells which contains amount.

Example

• An initial investment of Rs 80,000 in a business generates net income of Rs.10000, Rs. 15000, Rs. 20000, Rs.25000 and Rs.30000 for 1st, 2nd, 3rd, 4th and 5th year respectively. Then internal rate of return (IRR) for this investment is 7% and -5% after 5th and 4th year respectively.
  

XIRR (Extended Internal Rate of Return)

One of the drawback of IRR formula is that it can be only be used for equidistant investment intervals and not irregular ones. In case of irregular investments made XIRR can be used.

Calculation
We can use excel’s in-built XIRR formula:

• In excel, enter date and amount for all investments made. For investment made put “-“ before the values.
• Enter date and amount of all payment received.
• Use XIRR formula = XIRR(values, dates) wherein values is the range of cells which contains amount and dates respectively.

Example

• Lumpsum amount is invested in a company stock - Rs.10000 on 1-Feb-08, Rs 15000 on 1-Mar-2008 and Rs.5000 on 30-Oct-08. On 1-Apr-09 the stocks are sold receiving Rs.40000. The rate of return for this investment using XIRR is 32.95%.