﻿ Fintotal Knowledge | Using XIRR to Calculate Your Investment Returns Tabtastic

## 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.

## Explore more in a easy manner.

• 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
• 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