Article

Using XIRR to Calculate Your Investment Returns
July 09 , 2013

Of all formulae and excel functions available for calculating return on investments, XIRR is a favourite since it can be used in a lot of situations. XIRR is a function in MS Excel and you can use it to know annualized return of your mutual fund investments and a lot of others too.

What is xirr

Though XIRR might sound technical to some don't be put off; by the end of the article you will be ready to apply this extremely useful tool for all your future investment calculations.

Say you run a monthly SIP in a mutual fund for a year and redeem it after 4 years. The xirr function will tell you what is the annual percent return of your investment. In the excel sheet it would look like this:

 

A

B

1

1-Jan-13

-1000

2

1-Feb-13

-1000

3

1-Mar-13

-1000

4

1-Apr-13

-1000

5

1-May-13

-1000

6

1-Jun-13

-1000

7

1-Jul-13

-1000

8

1-Aug-13

-1000

9

1-Sep-13

-1000

10

1-Oct-13

-1000

11

1-Nov-13

-1000

12

1-Dec-13

-1000

13

1-Jan-17

20000

14

Annualized return by xirr

[=xirr(B1:B13,A1:A13)]

15%


All you have to do is enter data in the correct format in excel. Then when you apply the xirr formula you get neat annualized return. We'll detail the exact steps to follow for using xirr.

How to use xirr to calculate returns in excel
1. In an excel sheet fill in all dates of investment and all dates of redemption in one column. Enter date in dd-mm-yy format.

2. Fill in investment amount and redemption amount corresponding to the dates in the next column. All investments are outflows for you, so they should be entered as negative values. Redemptions are inflows so they are positive values.

3. In an empty cell type '=xirr'. Excel will display the function and prompt for values, dates and guess. All you have to do now is type an open bracket '(' after the word xirr, drag and select the amounts from first to last one, enter a comma and drag and select dates from first to final one and finally close the bracket with ')'. Guess is optional. It is your guess for a close return rate. If you ignore guess and close the bracket a rate of 10% is assumed. Now hit enter.

4. Excel will show annualized returns. The resulting number is your annual returns from the investment. To see in percent format just multiply the number by 100.

Important checkpoints for using xirr
If the function gives some error message you need to check you have not made one of these slippages:

Error message

Check that

#NUM

 

There is at least one positive amount and negative amount

No date precedes the starting date

Number of dates and number of amounts entered are equal

#VALUE

All dates are valid


Usefulness of xirr
XIRR works accurately in a number of situations. You can use it when you have a series of investments and withdrawal followed by investments and withdrawal and so on. If it is just a series of investment followed by withdrawal/s you can still use xirr. Or the reverse case when there is a single investment and a series of withdrawals xirr will work correctly. Even for lumpsum investment where you invest on a particular day and redeem on another day xirr will give you the annual return rate.

It should be noted that xirr gives yearly return on investment. Even if investment and redemption happens in the same year, xirr formula will give you an annualized return. That's the way it is programmed.

Fintotal Knowledge the best place to learn more on personal finance.

The language is simplified and written specially for non finance background individuals.


Explore more in a easy manner.

Table of Contents

Table of Contents

  • No Article Listed
  • No Article Listed
  • No Article Listed
  • No Article Listed
  • No Article Listed
  • No Article Listed
  • No Article Listed
  • No Article Listed
  • No Article Listed
  • No Article Listed

Get the all financial products under one roof only at

you will NEVER GO WRONG with us!

Unbiased . Best Deals . Appropriate Products . No Mis-selling