# can XIRR in excel be used for multiple years?



## joncnca (Jul 12, 2009)

someone on the forums gave me these links a little while ago, and i've also tried to read up about it online, but i'm not sure if i'm understanding correctly and it seems like sometimes people are inconsistent in the application of some definitions, or maybe it's just me..

http://www.gummy-stuff.org/petrovski.htm

http://www.gummy-stuff.org/XIRR-stuff.htm

does XIRR always calculate the annualized return (return needed every year to obtain equivalent ending value) regardless of how many years of data you enter, while including effects of contributions and withdrawals?

in the XIRR example above, he uses XIRR for 2 quarters and gets different numbers. Does this mean that for the first quarter Jan to Mar, based on the opening, closing values, and contributions, 17.4% would be the return if extrapolated to the full year? 

similarly, for the second quarter Mar to Jun, -4.8% is the annualized return if the data from those months were extrapolated to the full year?

so let's say that i have 4 years worth of data (i.e. bigger data set than the example, more than 1 year as opposed to quarters). there's a starting value and ending value each year, and amount of contribution throughout the year. if i use XIRR for the entire 4 years worth of data, will that give me the annualized return? that is, the equivalent % return per year that would give me the same ending value at the end of 4 years, and accounting for contributions/withdrawals?

the alternative is that, if XIRR can only be useful for a 1 year time frame or less, then i'll calculate the return for each year using XIRR, and then calculate the geometric average for the 4 years?

thanks!


----------



## CanadianCapitalist (Mar 31, 2009)

joncnca said:


> so let's say that i have 4 years worth of data (i.e. bigger data set than the example, more than 1 year as opposed to quarters). there's a starting value and ending value each year, and amount of contribution throughout the year. if i use XIRR for the entire 4 years worth of data, will that give me the annualized return? that is, the equivalent % return per year that would give me the same ending value at the end of 4 years, and accounting for contributions/withdrawals?
> 
> the alternative is that, if XIRR can only be useful for a 1 year time frame or less, then i'll calculate the return for each year using XIRR, and then calculate the geometric average for the 4 years?
> 
> thanks!


You can use XIRR for any period of time. Input the starting value and date, the account additions/withdrawals and dates, the end value and date. Run the formula and you'll get the annualized rate of return. You don't really need the starting and ending value for each year. 

http://en.wikipedia.org/wiki/Internal_rate_of_return


----------



## Xoron (Jun 22, 2010)

I've done XIRR for both 1 year and lifetime returns on my investments. As CC says, just have one large table with the start date and each input (outflow) of money. Just end with the negative of the value of your investments for the formula to work properly.


----------



## GoldStone (Mar 6, 2011)

This is an OpenOffice example, but the same technique works in Excel:

http://canadianmoneyforum.com/showt...calculate-XIRR?p=162579&viewfull=1#post162579


----------



## londoncalling (Sep 17, 2011)

I use xirr for a variety of things including return on individual stocks for monthlies, quarterlies etc. Here is a helpful tip as well in case you ever get a negative return.

http://www.financialwebring.org/gummy-stuff/XIRR-bug.htm

I hope you never need to use it. :encouragement:


----------



## joncnca (Jul 12, 2009)

thanks for the replies, everyone!

it occurs to me that this accounts for deposits and withdrawals. what if there's a long period of time when there's no contribution, and you're just tracking growth of a (e.g) stock.

if you're just calculating the annualized return without any additional contributions, that's simple, just (1+R)^(1/N)-1 where R is final/initial amount, N is time. but can XIRR account for a period of no contribution? do you just add the 'growth' as if it's a contribution (for instance, in the case of a distribution payment)?


----------



## GoldStone (Mar 6, 2011)

joncnca said:


> but can XIRR account for a period of no contribution?


Sure. Enter two rows:
1. Start balance and the date
2. End balance and the date
XIRR calculates the annualized return.



joncnca said:


> do you just add the 'growth' as if it's a contribution (for instance, in the case of a distribution payment)?


You never enter the internal money flows when you do XIRR. Ignore dividend payments, interest payments, fees, etc.


----------



## joncnca (Jul 12, 2009)

GoldStone said:


> Sure. Enter two rows:
> 1. Start balance and the date
> 2. End balance and the date
> XIRR calculates the annualized return.


does this calculate correctly if the period of no contribution occurs within a period of periodic contribution? for instance, monthly contribution for 6 months, then no contribution for next 6 months, then quarterly contributions for the next 12 months. if i enter everything in there and use XIRR, it'll calculate the annualized return for EVERYTHING?




GoldStone said:


> You never enter the internal money flows when you do XIRR. Ignore dividend payments, interest payments, fees, etc.


perhaps i'm not fully understanding how internal rate of return works yet...if you ignore internal money flows, then XIRR can help to determine the rate of return based purely on your contributions/withdrawals. but it doesn't tell you how much the investment has grown in total (?). it seems that the growth must be a different calculation, which you compare against XIRR to determine the difference. is that how you get the amount of growth, excluding your own contributions/withdrawals

thanks


----------



## GoldStone (Mar 6, 2011)

joncnca said:


> does this calculate correctly if the period of no contribution occurs within a period of periodic contribution? for instance, monthly contribution for 6 months, then no contribution for next 6 months, then quarterly contributions for the next 12 months. if i enter everything in there and use XIRR, it'll calculate the annualized return for EVERYTHING?


Yes. You have to enter transactions dates. XIRR takes them into account.



joncnca said:


> perhaps i'm not fully understanding how internal rate of return works yet...if you ignore internal money flows, then XIRR can help to determine the rate of return based purely on your contributions/withdrawals. but it doesn't tell you how much the investment has grown in total (?). it seems that the growth must be a different calculation, which you compare against XIRR to determine the difference. is that how you get the amount of growth, excluding your own contributions/withdrawals


XIRR calculates annualized rate of return, adjusted for contributions and withdrawals. 

Try this tutorial, maybe it will clear things up. 

http://www.financialwebring.org/gummy-stuff/xirr.htm


----------



## joncnca (Jul 12, 2009)

thanks, Goldstone. i've read other pages of that site, but somehow missed that one.

is this right? the first number is always the initial investment/value corresponding to the first date. the last number is always the negative of the final value corresponding to the last date. 

all other entries between first and last are external contributions/withdrawals? and somehow, XIRR knows how to calculated annualized return and adjusts for contributions and withdrawals?



i was getting confused because i know the first entry was the initial investment, and didn't know where the final value would be to determine the change between initial and final... i thought ALL entries were contributions/withdrawals, and didn't understand how XIRR could determine the amount of change/growth.

addition:
let's say i have 3 separate accounts in my portfolio that i merged into 2 accounts. during the merger, would you include these as a withdrawal then a contribution (of a similar amount). Should XIRR be used for each individual account or for the entire portfolio?


----------



## GoldStone (Mar 6, 2011)

joncnca said:


> is this right? the first number is always the initial investment/value corresponding to the first date. the last number is always the negative of the final value corresponding to the last date.
> 
> all other entries between first and last are external contributions/withdrawals? and somehow, XIRR knows how to calculated annualized return and adjusts for contributions and withdrawals?


Right.



joncnca said:


> let's say i have 3 separate accounts in my portfolio that i merged into 2 accounts. during the merger, would you include these as a withdrawal then a contribution (of a similar amount).


If you track 3 separate accounts as a single portfolio, you ignore the merger. The transfers between the accounts are internal money flows within the portfolio.



joncnca said:


> Should XIRR be used for each individual account or for the entire portfolio?


It's up to you. Do you want to know the rates of return for each account, or one rate of return for the entire portfolio? You can do both if you wish.


----------

