Calculating Annualised Return on Multiple Shares

[Daniel]

Hi,

Let's say I hold position in 2 shares in my portfolio;

(a)
1000 Shares bought at €1.50 on 1/1/2014 for €1500
Now valued at €2.75 on 31/12/2015
Current value of €2750
Gain of €1250 (83.33%)

(b)
400 Shares bought at €6.00 on 1/1/2013 for €2400
Now valued at €6.25 on 31/12/2015,
Current value of €2500
Gain of €100 (4.17%)

So the annualised return for each is;

(a)
((1+83.33%)^(1/(2 Years))-1
(1.8333^0.5)-1
1.354-1
0.354
35.4%

(b)
((1+4.17%)^(1/(3 Years))-1
(1.0417^0.33)-1
1.014-1
0.014
1.4%

So now that have I have the annualised return of each share, how do I calculate the overall annualised return of my portfolio of 2 shares?

Thanks,
Daniel

 

[rob oyle]

My understanding would be that you'd have to work off a consistent timeframe. What was the return on the funds invested in share (a) during 2013?

 

[Daniel]

My understanding would be that you'd have to work off a consistent timeframe. What was the return on the funds invested in share (a) during 2013?

But I only bought share (a) on 1/1/2014 so there was no return for me on that share in 2013.

 

[PMU]

Value of portfolio on 1/1/13 is 2,400; value of portfolio on 31/12/15 is (2,750+2,500) 5,250. Compound annual growth rate (CAGR) is ((Closing value/opening value)^(1/no. of years))-1, i.e. (((5,250-2,400)^(1/3))-1) = 29.81%

 

[rob oyle]

Value of portfolio on 1/1/13 is 2,400; value of portfolio on 31/12/15 is (2,750+2,500) 5,250. Compound annual growth rate (CAGR) is ((Closing value/opening value)^(1/no. of years))-1, i.e. (((5,250-2,400)^(1/3))-1) = 29.81%

That calculation doesn't make any allowance for the further investment of €1,500 on 1/1/14 though! If the larger side of the investment only had an annualised return of 1.4% then surely the combined return should be closer to that figure?

OP, insufficient information (or maybe it's better to say inconsistent information) is available to do what you ask. What time period would you like to have considered for your investment? I don't think you can do what you're asking without working from consistent parameters.

 

[Daniel]

That calculation doesn't make any allowance for the further investment of €1,500 on 1/1/14 though! If the larger side of the investment only had an annualised return of 1.4% then surely the combined return should be closer to that figure?

OP, insufficient information (or maybe it's better to say inconsistent information) is available to do what you ask. What time period would you like to have considered for your investment? I don't think you can do what you're asking without working from consistent parameters.

I'm not entirely sure what time period should be considered, I guess from 1/1/2013 when share (b) was bought up until 31/12/2015, so 3 years in total. That's the problem really, I'm not really sure how to do this!

Value of portfolio on 1/1/13 is 2,400; value of portfolio on 31/12/15 is (2,750+2,500) 5,250. Compound annual growth rate (CAGR) is ((Closing value/opening value)^(1/no. of years))-1, i.e. (((5,250-2,400)^(1/3))-1) = 29.81%

As rob has said, if you went with this approach, is the €1500 being treated entirely as a gain? €5250 - €2400 would be suggesting that you have made a gain of €2850, when the gain was only €1250 + €100.

 

[rob oyle]

I'm not entirely sure what time period should be considered, I guess from 1/1/2013 when share (b) was bought up until 31/12/2015, so 3 years in total. That's the problem really, I'm not really sure how to do this!

Well, if you have the value of the 2013 investment at 1/1/14, you can workout the 2013 return and then an annualised return on the total investment from 1/1/14 to 31/12/15.

Alternatively, where were the funds used for the 2014 purchase invested for 2013? What was the value of the €1,500 exactly one year before?

 

[PMU]

OP, insufficient information (or maybe it's better to say inconsistent information) is available to do what you ask. .

The second investment was made on 1/1/2014. No value is given for the value of the initial investment on 1/1/2014. If this were available, you would (a) calculate the return of the initial investment to the date the second investment was made, and then (b) calculate the return on the value of the portfolio (i.e. value of initial investment on 1/1/14 plus 1500 (the value of new investment)) to 31/12/2015. You can then (c) link the two returns geometrically, e.g. = (1 + [initial period % return) x (1 + [second period % return]) – 1, giving you a time weighted return. Unless you know the value of the portfolio on the date an additional investment (or a withdrawal) was made, you can only calculate on end year values.

 

[Daniel]

Let's say the funds used for the invest on 1/1/2014 are newly acquired funds obtained from a salary, so there was no value of the €1500 one year before.

OK I think I follow what the both of you are suggesting now:


2013 Investments

(b)
400 Shares bought at €6.00 on 1/1/2013 for €2400
Valued at €6.15 on 31/12/2013
Value of €2460
Gain of €60 (2.5%)
Annualised: 2.5% (for 1 year)


2014 Investments

(a)
1000 Shares bought at €1.50 on 1/1/2014 for €1500
Now valued at €2.75 on 31/12/2015
Current value of €2750
Gain of €1250 (83.33%)
Annualised: 35.4%

(b)
400 Shares valued at €6.15 on 1/1/2014 (€2460)
Now valued at €6.25 on 31/12/2015
Current value of €2500
Gain of €40 (1.63%)
Annualised: 0.81%

1/1/2014: Share (b) €2460 + Share (a) €1500 = €3960
31/12/2015: Share (b) €2500 + Share (a) €2750 = €5250
Gain of €1290 (32.58%)
Combined Annualised: 15.14% (for 2 years)


So now how do I link the two returns geometrically; the 2.5% for the single share in 2013 and the 15.14% for both shares across 2014 and 2015?

Is it?
(1 + 2.5%) x (1 + 15.14%) – 1
(1.025 X 1.1514) - 1
0.18
18%

 

[Steven Barrett]

Use the =rate formula on excel to calculate the annualised return. But as Rob says, you need a consistent time period. You can't get one annualised return using two different time periods, it doesn't work like that.


Steven
http://www.bluewaterfp.ie (www.bluewaterfp.ie)

 

[PMU]

Your time weighted return is the geometric mean of the return your initial investment (2400) made up to the date of your second investment (i.e. (2460-2400)/2400)=2.5%, and the return your portfolio made from the date of the second investment (2460+1500=3960) to the final date 31/12/15 (((2500+2570)-3960)/3960)=32.58%. The time weighted return is then ((1+0.025)*(1+0.3258)-1)=35.89%.

I don't think annualizing a time weighted return makes sense. You have your actual rates of return for each year. That's what you made or lost. An 'annualized' return is just a makey-up number that is the equivalent yearly return you would have had to achieve each year in the multi-year period to achieve the return you actually obtained at the end of the multi-year period. The annualized return, in this case is ((1+0.3589)^(1/3))-1, which is 10.76%. So you could have achieved the return you achieved if you had achieved a return of 10.76% each year, i.e. in 2013, 2014 and 2015.

 

[dub_nerd]

I don't think any kind of mean, either arithmetic or geometric, makes sense. The periods involved are incommensurable. (Disclaimer: I am not an accountant).

 

[Daniel]

Thanks for all the replies.

Use the =rate formula on excel to calculate the annualised return. But as Rob says, you need a consistent time period. You can't get one annualised return using two different time periods, it doesn't work like that.

Thanks Steven, I didn't realise Excel has a rate function, I was just manually entering the calculations into the formula bar.

I don't think annualizing a time weighted return makes sense. You have your actual rates of return for each year. That's what you made or lost. An 'annualized' return is just a makey-up number that is the equivalent yearly return you would have had to achieve each year in the multi-year period to achieve the return you actually obtained at the end of the multi-year period. The annualized return, in this case is ((1+0.3589)^(1/3))-1, which is 10.76%. So you could have achieved the return you achieved if you had achieved a return of 10.76% each year, i.e. in 2013, 2014 and 2015.

Fair enough that an annualised return may be a makey-up number but isn't the only way to compare your rate of return on an investment with that of another (possibly from another asset class spanning another time period)?

I don't think any kind of mean, either arithmetic or geometric, makes sense. The periods involved are incommensurable. (Disclaimer: I am not an accountant).

So basically anytime someone adds to their portfolio, you should take note of all of the values at that time including the rate of return to date (lets say year 1 -3, 4 shares). Then everything starts at a gain of zero in the next period of time as the gains (or losses) have been included in the previous period. Then you start a new time period including the new addition to your portfolio (year 4 onward until you add to portfolio again, 5 shares). This way you will be using consistent time periods. You can then calculate a time weighted return across the 2 periods.

 

[Steven Barrett]

Thanks for all the replies.



Thanks Steven, I didn't realise Excel has a rate function, I was just manually entering the calculations into the formula bar.

They have loads of easy to use formulas for doing compounding calculations. =fv for future value, =pv for present value. You can calculate how much a pension would be worth for example if you paid in X amount a month (=pmt). Google it and you'll find guides for them.


Steven
http://www.bluewaterfp.ie (www.bluewaterfp.ie)

 

[PMU]

Fair enough that an annualised return may be a makey-up number but isn't the only way to compare your rate of return on an investment with that of another (possibly from another asset class spanning another time period)?

Sorry, my post was not clear enough. I wasn't suggesting that you can't use annualized returns to measure and compare individual investments; I just doubt that annualizing a figure such as a time weighted return provides a meaningful figure.

With individual investments, as returns are duration dependent (e.g. a 10% return in 1 year is not the same as a 10% return over 3 years), returns have to be normalized to make them comparable. And normalizing over a 12 month period is annualizing. You did this in your first post, so you can compare the relative performance of each of your shares.

On calculating portfolio return (i.e. where you have more than one investment, and/or, multiple additions and withdrawals of funds), the TWR gives you, in effect, the compound growth of 1 euro (or whatever is the base currency) in your portfolio over the period you are invested. This is standalone measure of portfolio performance. You can calculate it on an annual basis, but as stated above, I'm just not certain if annualizing it gives a meaningful figure to compare portfolios. Perhaps someone with a bit more mathematical knowledge could comment.

 

[dub_nerd]

So basically anytime someone adds to their portfolio, you should take note of all of the values at that time including the rate of return to date (lets say year 1 -3, 4 shares). Then everything starts at a gain of zero in the next period of time as the gains (or losses) have been included in the previous period. Then you start a new time period including the new addition to your portfolio (year 4 onward until you add to portfolio again, 5 shares). This way you will be using consistent time periods. You can then calculate a time weighted return across the 2 periods.

You should probably stand back and think -- in plain English -- what you are trying to achieve. What do you want to compare? Anything that takes into account your specific investment amounts, dates, and rates of growth, is going to be totally specific to your portfolio. Why bother trying to average anything? You made what you made.

Comparators like AER on a deposit, or rate of return on a fund, factor out differences like the actual amount invested. AER would also ignore the overall time period, whereas fund performance would have to take into account the specific years. So you could take your portfolio, assume it starts off composed 100% of share 1 purchased at price x for some period, then it is split in proportion a-b between shares 1 & 2 at prices x & y for some subsequent period, and so on, taking into account all the dates on which the portfolio balance changes (and also year ends and/or anniversaries). You could then calculate the value of €100 invested in those shares in those proportions at those prices over the specified periods. Of course, it would have no bearing on your own actual investment which would be for different amounts. But it would allow a comparison against stated percentage rate returns of other investments, or of different time periods within your own investment.

I expect it would end up looking something like the way the unit value of a fund is calculated, but as I know nothing at all about this I am only speculating. There are people here who know how that works, I am sure.

 

[Daniel]

I understand now PMU what you're saying, not that annualising itself doesn't make sense but that annualising a TMR might not make sense.

You should probably stand back and think -- in plain English -- what you are trying to achieve. What do you want to compare? Anything that takes into account your specific investment amounts, dates, and rates of growth, is going to be totally specific to your portfolio. Why bother trying to average anything? You made what you made.

I guess what I'm trying to achieve is a single annualised figure of how well my equities (purchased on various dates over the past few years) have performed to date. There's some winners in there and there are some losers in there. I want to use this as a measure of my ability to choose equities (fair enough it could have been luck as well!). I have had them for a good few years now so I reckon I have a long enough time period to judge. I want to use this measure then as input into some decisions; buy more directly held equities, buy ETFs or invest more in P2P lending.

They have loads of easy to use formulas for doing compounding calculations. =fv for future value, =pv for present value. You can calculate how much a pension would be worth for example if you paid in X amount a month (=pmt). Google it and you'll find guides for them.


Steven
http://www.bluewaterfp.ie (www.bluewaterfp.ie)

I have all of the starting values of my equities but the values of them each time I bought another equity is not something I have. I could go get all of these historical values but it would take time and some are in other currencies so I'd have to get the historical currency values as well to figure out the Euro equivalent at the time.

I've just come across a function in Excel called XIRR (Internal Rate of Return). So you can specify a list of dates when your portfolio was added to and and the amount that were added at that time. You can then specify a closing date and value and it will calculate an annualised rate for you.

I've attached a screenshot of the values in Excel and the formula is =XIRR(B1:B3,A1:A3).
[broken link removed]
[broken link removed]

The value that is has come up with is 11.99%. The value that has been calculated by PMU above using the TWR method and then annualising it is 10.76% so there is a difference. What does everyone think of this formula in Excel? Using this would be much easier than going back in time trying to figure out historical value of a portfolio.

 

[PMU]

What does everyone think of this formula in Excel?

Internal Rate of Return (IRR) is the rate of return at which your "initial investment plus additions" has grown to equal the "closing valuation minus withdrawals". TWR measures the growth of the average euro in your portfolio. They measure two different things. In effect, the TWR measures how smart you are in picking investments; and the IRR measures how smart you are in moving cash into and out of your portfolio.

 

[dub_nerd]

I guess what I'm trying to achieve is a single annualised figure of how well my equities (purchased on various dates over the past few years) have performed to date. There's some winners in there and there are some losers in there. I want to use this as a measure of my ability to choose equities (fair enough it could have been luck as well!). I have had them for a good few years now so I reckon I have a long enough time period to judge. I want to use this measure then as input into some decisions; buy more directly held equities, buy ETFs or invest more in P2P lending.

Ok, that seems like a reasonable aspiration. However, you are thereby choosing a single number to characterise your entire investment history. What if you got better over time, as presumably you would hope? Also, what number are you going to compare against for an ETF or other investment in order to make your decision?

I've just come across a function in Excel called XIRR (Internal Rate of Return). So you can specify a list of dates when your portfolio was added to and and the amount that were added at that time. You can then specify a closing date and value and it will calculate an annualised rate for you.

I've attached a screenshot of the values in Excel and the formula is =XIRR(B1:B3,A1:A3).
[broken link removed]
[broken link removed]

The value that is has come up with is 11.99%. The value that has been calculated by PMU above using the TWR method and then annualising it is 10.76% so there is a difference. What does everyone think of this formula in Excel? Using this would be much easier than going back in time trying to figure out historical value of a portfolio.

I think PMU above correctly characterised that Excel XIRR function. Just to make sure I understood what it was doing, I reproduced its results as follows (I'm reasonably savvy at maths but don't know a whole lot about investing or accounting terms):

Taking your annualised rate computed by Excel (11.99%) I computed a compounding daily rate (which is needed instead of a simple rate because it has to be used across periods of arbitrary length). I reckoned the compounding rate as:

... where a is the annualised percentage rate. The balance at the start of each period is the new amount added (or withdrawn) plus the previous period start balance plus "interest" compounded over the length of the previous period. So as PMU said (much more concisely than I have), this is like calculating the interest rate on a bank account, taking into account deposits and withdrawals, which gets you from your opening balance to your final balance. It doesn't take into account the performance of any particular stock, or the performance during any particular period. Clearly it couldn't, since you are choosing to characterise the entire portfolio over the entire period by a single number.

I have all of the starting values of my equities but the values of them each time I bought another equity is not something I have. I could go get all of these historical values but it would take time and some are in other currencies so I'd have to get the historical currency values as well to figure out the Euro equivalent at the time.

Intuitively, this is the missing information you would need in order to calculate a rate of return within each period. From my (brand new ) understanding of time-weighted return referred to by PMU, that's exactly what a TWR is. It would let you compare your performance against, say, ETFs (like these ones) based on real historical periods. But in order to calculate rolling values like "last three months" or YTD, you'd really need daily valuations of your portfolio.

 

[Daniel]

I guess what I want to measure is how smart I am in picking investments so I should really use the TWR, it shouldn't take too long to go back and check the historical values of my shares. However the only problem with this which has already been pointing out is it doesn't make sense to annualise a TWR, so I cannot compare it to the annual return of say an ETF or any other type of investment with an annual return. So how do I compare the TWR return with other investments? Do I have to say take an ETF and compare the performance of it matching the dates of my investment? So 1/1/2013 to 31/12/2015?

I've just used the XIRR function (newly discovered to me!) to calculate the return on all of my fixed income investments over the years. I've got a few different time periods in there as I've had to close one period and start another when money was taken out of one investment and moved to another. So I've ended up with several annualised rates of return. I've then converted them into gross rates for that whole period and calculated the TWR of all them. I couldn't resist annualising the overall figure even though it may not be right but I did anyway, so now I've ended up with a single annualised figure for the fixed income portion of my investment. Although I'm not entirely sure if I how I did it is correct, at least I have something. All this maths is starting to boggle me!

So like this:

Period 1 (3.22 Years) Annualised: 1.93%
Gross Equivalent: ((1+0.0193)^3.22)-1 = 6.36%

Period 2 (1.22 Years) Annualised: 2.13%
Gross Equivalent: ((1+0.0213)^1.22)-1 = 2.59%

TWR: ((1+0.0636)*(1+0.0259)-1) = 9.11%
Annualised TWR: (1+0.0911)^(1/(3.22 Years + 1.22 Years))-1 = 1.98%

Does this make sense?