Computing performance of a share portfolio

[Ext User(kdv09@excite.com)]

Just a thought here -
I do realise that you asked about return of the share investment - and
some good suggestions have been made in the group.

However, you might be also interested in your own performance as the
money manager. This includes performance of the money, when they are
invested into the market and when they are taken out of the market and
resting in some interest bearing account, for example. This performance
metric makes sense as well, maybe even more than just share investment
performance, as it measures how efficiently the funds are used. There
is one problem though as the size of the overall portfolio (shares +
other investments) may not be constant over time, so it can not be
assumed that all money that are not invested in shares are sitting in
the bank.

I guess the task is very similar to measuring performance of
diversified superannuation fund.

regards
kdv09

[Ext User(John Wright)]

<kdv09@excite.com> wrote [edited]
> Just a thought here -
> I do realise that you asked about return of the share investment - and
> some good suggestions have been made in the group.
> ....
> However, you might be also interested in your own performance as the
> money manager. This includes performance of the money, when they are
> invested into the market and when they are taken out of the market and
> resting in some interest bearing account, for example.

Yes, it would be a useful measure. Particulary if I thought share markets
were too high and have been keeping a lot of cash - now I may want to see if
my judgment have paid off or has cost me ...

But I thought this task was trivially simple. The "whole portfolio" has
cash, shares, and perhaps other investments - so nothing is coming from
outside into this or going out, there is just switching between the asset
classes within the whole portfolio. Therefore, one can simply compare the
net asset value bottom line of the whole portfolio (= simple sum of its
components) between any two points in time, and annualise it to get the
overall return.

It becomes nontrivial only when there is cash flow from/to outside the
portfolio - i.e. salary, donations etc.

Regards - JW

[Ext User(BernardZ)]

In article <43e09372$0$26131$afc38c87@news.optusnet.com.au>,
notprovided@something.com says...
> "BernardZ" wrote [edited]
> > I get these problems all the time
> > ...
> > Rate MTD =(1+X)^(1/12)-1
> >
> > Where Sum = cell above *(1+Rate MTD)+change for that month
> >
>
> Pretty good solution... thanks.
>
> Excel can do goal seeking, so no need for the trial and error and using the
> binary search way you adopted. You tell it you want $390,000 on 1/7/05, it
> can directly work out in reverse the annualised %age required to give that
> result. Quite easy to use - under 'tools' menu. I have used it - works OK.
>
> In your spreadsheet I think you have left out one month - 1/6/05; hence I
> got a smaller 11.89% ROI for the year.
>
> Regards - JW
>
>
>
>
>
>
>

I have used excel for years and never knew that. I just tried it and its
great.

THANK YOU


--
A quick useful to determining the intended victim in a horror movie is
to try to determine the person who has no family in the story.

Observations of Bernard - No 95

[Ext User(BernardZ)]

In article <1138787822.774877.50500@g47g2000cwa.googlegroups.c om>,
tonen@swiftdsl.com.au says...
> Hey - BernardZ and I have the same answer. Bet my methods easier :)
>

Yep.

I took your method. Wrote an email at work and showed them how to use
it. It was an instant hit.

THANK YOU TOO


--
A quick useful to determining the intended victim in a horror movie is
to try to determine the person who has no family in the story.

Observations of Bernard - No 95

[Ext User(John Wright)]

Is there a *SIMPLE* way to calculate the performance - in terms of
annualised %age return on investment - of one's personal share portfolio?

Simply dividing the share portfolio value at the start into that at the end
of the year won't do - for new cash may have been injected to buy more
shares, or shares sold and cash taken out of portfolio. We want to measure
return on investment - annualised - all cash holding to be excluded..

At various times during the year the amount invested varies - making it
difficult to compute the ROI.

Take the following as an example -

Share Portfolio value at start of year on 1/7/04 = $300,000.

1/9/04: injected $50,000 cash from outside and bought some shares.

1/11/04: sold some old shares for $30,000 that was valued at $25,000 at
start of the year. Proceeds taken out.

1/2/05: injected $40,000 cash and bought some new shares.

1/5/05: Received $10,000 dividend (including franking credits) - taken out.

30/6/05: the share portfolio is valued at $390,000.

What is the %age return of the share investments for the year?

Assume whatever you need that is missing above.

Regards - JW

[Ext User(BernardZ)]

In article <43e043e6$0$21414$afc38c87@news.optusnet.com.au>,
notprovided@something.com says...
> Is there a *SIMPLE* way to calculate the performance - in terms of
> annualised %age return on investment - of one's personal share portfolio?
>
> Simply dividing the share portfolio value at the start into that at the end
> of the year won't do - for new cash may have been injected to buy more
> shares, or shares sold and cash taken out of portfolio. We want to measure
> return on investment - annualised - all cash holding to be excluded..
>
> At various times during the year the amount invested varies - making it
> difficult to compute the ROI.
>
> Take the following as an example -
>
> Share Portfolio value at start of year on 1/7/04 = $300,000.
>
> 1/9/04: injected $50,000 cash from outside and bought some shares.
>
> 1/11/04: sold some old shares for $30,000 that was valued at $25,000 at
> start of the year. Proceeds taken out.
>
> 1/2/05: injected $40,000 cash and bought some new shares.
>
> 1/5/05: Received $10,000 dividend (including franking credits) - taken out.
>
> 30/6/05: the share portfolio is valued at $390,000.
>
> What is the %age return of the share investments for the year?
>
> Assume whatever you need that is missing above.
>
> Regards - JW
>
>
>
>

I get these problems all the time


I use a spreadsheet to solve these problems.

High Rate = H
Low Rate = L

Rate YTD X=(H+L)/2

Rate MTD =(1+X)^(1/12)-1

It looks like this


Date .................Change.........Sum
1/07/2004 0 $300,000.00
1/08/2004 0 303091.943
1/09/2004 $50,000 356215.753
1/10/2004 $0 359887.0823
1/11/2004 -$30,000 333596.2501
1/12/2004 $0 337034.4521
1/01/2005 $0 340508.0898
1/02/2005 $40,000 384017.5285
1/03/2005 $0 387975.3961
1/04/2005 $0 391974.0555
1/05/2005 -$10,000 386013.9269
1/07/2005 0 389992.3704


Where Sum = cell above *(1+Rate MTD)+change for that month

Now I keep playing around with the rate till I get it right by going

Initially I make L =0 H=100% => X=50%
Too high L=0 H=50% => X=25%
Too high L=0 H=25% => X=12.5%
Too low L=12.5% H=25% => X=18.75%
etc

Until L and H are so close that it does not matter.

Answer = 12.3%






--
A quick useful to determining the intended victim in a horror movie is
to try to determine the person who has no family in the story.

Observations of Bernard - No 95

[Ext User(Cliff Bott)]

One way to do this is to calculate a weighted average amount invested by
multiplying the amount invested at each step by the number of days to which
that amount applies, adding these amounts together and dividing by 365. In
your example the weighted average amount invested (in $ thousands) is
((300*62)+(350*61)+(320*92)+(360*89)+(350*61))/365 = 336.38

Therefore your rate of return is (5+10+90)/336.38 = 31.2%

(I haven't double checked the calculations, but the method should be clear)

However since the amount invested varies with the value of the portfolio,
strictly speaking you should revalue the portfolio at each step also. For
example if on 1/9/04 the portfolio had increased in value from 300 to 307,
the amount invested you should use at this step is 357, not 350.

Unless you have some very lumpy transactions, however, using the simple
average (amount invested at beginning of year + amount invested at end of
year)/2 is sufficient. In this case that gives a rate of return of
(5+10+90)/((300+390)/2) = 30.4%.


"John Wright" <notprovided@something.com> wrote in message
news:43e043e6$0$21414$afc38c87@news.optusnet.com.a u...
> Is there a *SIMPLE* way to calculate the performance - in terms of
> annualised %age return on investment - of one's personal share portfolio?
>
> Simply dividing the share portfolio value at the start into that at the
end
> of the year won't do - for new cash may have been injected to buy more
> shares, or shares sold and cash taken out of portfolio. We want to measure
> return on investment - annualised - all cash holding to be excluded..
>
> At various times during the year the amount invested varies - making it
> difficult to compute the ROI.
>
> Take the following as an example -
>
> Share Portfolio value at start of year on 1/7/04 = $300,000.
>
> 1/9/04: injected $50,000 cash from outside and bought some shares.
>
> 1/11/04: sold some old shares for $30,000 that was valued at $25,000 at
> start of the year. Proceeds taken out.
>
> 1/2/05: injected $40,000 cash and bought some new shares.
>
> 1/5/05: Received $10,000 dividend (including franking credits) - taken
out.
>
> 30/6/05: the share portfolio is valued at $390,000.
>
> What is the %age return of the share investments for the year?
>
> Assume whatever you need that is missing above.
>
> Regards - JW
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

[Ext User(Someone Else)]

"John Wright" <notprovided@something.com> wrote in message
news:43e043e6$0$21414$afc38c87@news.optusnet.com.a u...
> Is there a *SIMPLE* way to calculate the performance - in terms of
> annualised %age return on investment - of one's personal share portfolio?
>
> Simply dividing the share portfolio value at the start into that at the
end
> of the year won't do - for new cash may have been injected to buy more
> shares, or shares sold and cash taken out of portfolio. We want to measure
> return on investment - annualised - all cash holding to be excluded..
>
> At various times during the year the amount invested varies - making it
> difficult to compute the ROI.
>
> Take the following as an example -
>
> Share Portfolio value at start of year on 1/7/04 = $300,000.
>
> 1/9/04: injected $50,000 cash from outside and bought some shares.
>
> 1/11/04: sold some old shares for $30,000 that was valued at $25,000 at
> start of the year. Proceeds taken out.
>
> 1/2/05: injected $40,000 cash and bought some new shares.
>
> 1/5/05: Received $10,000 dividend (including franking credits) - taken
out.
>
> 30/6/05: the share portfolio is valued at $390,000.
>
> What is the %age return of the share investments for the year?
>
> Assume whatever you need that is missing above.
>
> Regards - JW

Have you looked at the XIRR formula in Excel? (I get 11.94%)

[Ext User(Fitzroy)]

"John Wright" <notprovided@something.com> wrote in message
news:43e043e6$0$21414$afc38c87@news.optusnet.com.a u...
> Is there a *SIMPLE* way to calculate the performance - in terms of
> annualised %age return on investment - of one's personal share portfolio?
>
> Simply dividing the share portfolio value at the start into that at the
end
> of the year won't do - for new cash may have been injected to buy more
> shares, or shares sold and cash taken out of portfolio. We want to measure
> return on investment - annualised - all cash holding to be excluded..
>
> At various times during the year the amount invested varies - making it
> difficult to compute the ROI.
>
> Take the following as an example -
>
> Share Portfolio value at start of year on 1/7/04 = $300,000.
>
> 1/9/04: injected $50,000 cash from outside and bought some shares.
>
> 1/11/04: sold some old shares for $30,000 that was valued at $25,000 at
> start of the year. Proceeds taken out.
>
> 1/2/05: injected $40,000 cash and bought some new shares.
>
> 1/5/05: Received $10,000 dividend (including franking credits) - taken
out.
>
> 30/6/05: the share portfolio is valued at $390,000.
>
> What is the %age return of the share investments for the year?
>
> Assume whatever you need that is missing above.
>
> Regards - JW
>
>


JW,

Lookup aus.invest thread 'Portfolio Tracking Software'
of July lasy year.

It contains the use of the IRR excel fucntion and
also TomN's suggestion regarding the more flexible
XIRR function.

[Ext User(Tonen)]

A time-weighted-return is accurate and easy to calculate, and can be
done on a monthly or quarterly basis, depending how whether there are
any large additions or withdrawals.
Work out starting/ending balance each month (quarter), adjust for cash
added or withdrawn (see PDF below), calculate each months (quarters)
return, and multiply all the months (quarters) for the annual return.
Piece of cake on a spreadsheet.

It's well explained in this 3 page PDF
http://www.portfoliosolutions.com/v2/pdf/AppendixI.pdf

On your figures I can't calculate the annual return accurately as I
can't calculate individual monthly or even quarterly returns,
nevertheless, I can use an even simpler time weighted method to give a
rough idea. It simply involves splitting net cash flow 50/50 to the
year's opening and closing balances.

Start 300K
Net cash flow = +50k (50K - 30K + 40K - 10K)
End 390k

return = (390 - 50/2) / (300 + 50/2) = 365/325 = 1.123
= 12.3% return
Hey - BernardZ and I have the same answer. Bet my methods easier :)

The 12.3% figure will be in the ball park, and near enough given you
have provided rounded/ approximate numbers.
I imagine you could at least retrospectively reconstruct your quarterly
figures (even better monthly) to get a more accurate result on your
actual numbers using the method provided in the PDF

Cheers
Tony G

[Ext User(John Wright)]

"Cliff Bott" wrote
> One way to do this is to calculate a weighted average amount invested by
> multiplying the amount invested at each step by the number of days to
> which
> that amount applies, adding these amounts together and dividing by 365.
> In
> your example the weighted average amount invested (in $ thousands) is
> ((300*62)+(350*61)+(320*92)+(360*89)+(350*61))/365 = 336.38
>
> Therefore your rate of return is (5+10+90)/336.38 = 31.2%
>
> (I haven't double checked the calculations, but the method should be
> clear)
>
> However since the amount invested varies with the value of the portfolio,
> strictly speaking you should revalue the portfolio at each step also. For
> example if on 1/9/04 the portfolio had increased in value from 300 to 307,
> the amount invested you should use at this step is 357, not 350.
>
> Unless you have some very lumpy transactions, however, using the simple
> average (amount invested at beginning of year + amount invested at end of
> year)/2 is sufficient. In this case that gives a rate of return of
> (5+10+90)/((300+390)/2) = 30.4%.

Yes, this is not bad at all. Only one thing though - the numerator in
(5+10+90)/336.38 is not correct - we want the net gain only there. The 90
already includes cash injected etc - we have to remove all that. The
numerator should be the final portfolio value 390 less the starting value
300 less all cash inflows (50 + 40) plus all cash outflows (30 + 10) = 40,
yielding 40/336.38 = 11.89%.

Regards - JW

..

[Ext User(John Wright)]

"Someone Else" wrote
>
> Have you looked at the XIRR formula in Excel? (I get 11.94%)
>

Wonderful - precisely what I wanted. So simple. And perhaps the most
accurate solution. Thanks.

Regards - JW

[Ext User(John Wright)]

"BernardZ" wrote [edited]
> I get these problems all the time
> ...
> Rate MTD =(1+X)^(1/12)-1
>
> Where Sum = cell above *(1+Rate MTD)+change for that month
>

Pretty good solution... thanks.

Excel can do goal seeking, so no need for the trial and error and using the
binary search way you adopted. You tell it you want $390,000 on 1/7/05, it
can directly work out in reverse the annualised %age required to give that
result. Quite easy to use - under 'tools' menu. I have used it - works OK.

In your spreadsheet I think you have left out one month - 1/6/05; hence I
got a smaller 11.89% ROI for the year.

Regards - JW

[Ext User(John Machin)]

John Wright wrote:
> Is there a *SIMPLE* way to calculate the performance - in terms of
> annualised %age return on investment - of one's personal share portfolio?
>
> Simply dividing the share portfolio value at the start into that at the end
> of the year won't do - for new cash may have been injected to buy more
> shares, or shares sold and cash taken out of portfolio. We want to measure
> return on investment - annualised - all cash holding to be excluded..
>
> At various times during the year the amount invested varies - making it
> difficult to compute the ROI.
>
> Take the following as an example -
>
> Share Portfolio value at start of year on 1/7/04 = $300,000.
>
> 1/9/04: injected $50,000 cash from outside and bought some shares.
>
> 1/11/04: sold some old shares for $30,000 that was valued at $25,000 at
> start of the year. Proceeds taken out.
>
> 1/2/05: injected $40,000 cash and bought some new shares.
>
> 1/5/05: Received $10,000 dividend (including franking credits) - taken out.
>
> 30/6/05: the share portfolio is valued at $390,000.
>
> What is the %age return of the share investments for the year?
>
> Assume whatever you need that is missing above.
>
> Regards - JW

Here's some light bed-time reading:
http://www.ifsa.com.au/index.aspx
then Information area > Standards and Guidance Notes
You need Standard 6.00 and Guidance Note 1.00

Cheers,
John

[Ext User(Cliff Bott)]

Yes, I got the numerator wrong. Sorry about that.


"John Wright" <notprovided@something.com> wrote in message
news:43e0888d$0$26973$afc38c87@news.optusnet.com.a u...
> "Cliff Bott" wrote
> > One way to do this is to calculate a weighted average amount invested by
> > multiplying the amount invested at each step by the number of days to
> > which
> > that amount applies, adding these amounts together and dividing by 365.
> > In
> > your example the weighted average amount invested (in $ thousands) is
> > ((300*62)+(350*61)+(320*92)+(360*89)+(350*61))/365 = 336.38
> >
> > Therefore your rate of return is (5+10+90)/336.38 = 31.2%
> >
> > (I haven't double checked the calculations, but the method should be
> > clear)
> >
> > However since the amount invested varies with the value of the
portfolio,
> > strictly speaking you should revalue the portfolio at each step also.
For
> > example if on 1/9/04 the portfolio had increased in value from 300 to
307,
> > the amount invested you should use at this step is 357, not 350.
> >
> > Unless you have some very lumpy transactions, however, using the simple
> > average (amount invested at beginning of year + amount invested at end
of
> > year)/2 is sufficient. In this case that gives a rate of return of
> > (5+10+90)/((300+390)/2) = 30.4%.
>
> Yes, this is not bad at all. Only one thing though - the numerator in
> (5+10+90)/336.38 is not correct - we want the net gain only there. The 90
> already includes cash injected etc - we have to remove all that. The
> numerator should be the final portfolio value 390 less the starting value
> 300 less all cash inflows (50 + 40) plus all cash outflows (30 + 10) =
40,
> yielding 40/336.38 = 11.89%.
>
> Regards - JW
>
> .
>
>