Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Logging ongoing holdings in Excel.

A helpful place to also put any annual reports etc, of your own portfolios
YeeWo
Lemon Slice
Posts: 424
Joined: November 5th, 2016, 10:12 am
Has thanked: 297 times
Been thanked: 118 times

Logging ongoing holdings in Excel.

#10703

Postby YeeWo » December 1st, 2016, 8:43 am

I have read many words around XIRR and Unit-isation for holdings. I attach below the way in which I log transactions in Excel. It would be really wonderful if a kind-hearted-lemon would critique my record-keeping. I'm aware of the limitations of my method in not producing an annualised return, however, I adopt the approach of being roughly-correct rather than precisely-wrong. My Tesco holding below, constructive feedback please!: -

Code: Select all

Date      | Type      | No.   | Price       | Fee    |            |        |         
25-Sep-14 | BUY       |  5680 |    11008.69 |  69.06 |            |        |   1.93815
09-Oct-14 | BUY       |  4320 |     8061.77 |  53.32 |            |        |   1.86615
19-Dec-14 | CASH DIVI |       |     -116.00 |        |            |  0.016 |         
13-Jan-15 | SELL      | -2500 |    -5293.75 |  12.95 |            |        |    2.1175
04-Mar-16 | SELL      | -3750 |    -7102.16 |  12.95 |            |        |   1.89391
20-Jun-16 | BUY       |  3750 |     6043.13 |  40.27 |            |        |    1.6115
06-Oct-16 | SELL      | -3750 |    -7605.00 |   10.5 |            |        |     2.028
          |           |       |             |        |            |        |         
          |           |       |             |        |            |        |         
          |           |       |             |        |            |        |         
          |           |       |     4996.68 | 199.05 | £ 5,195.73 | £ 1.39 | Avg Price
          |           |       | Share Price | £ 2.09 | £ 7,826.25 |        |         
          |           |       |             |        |     50.63% |        |         
          |           |       | MoS/Loss    |        | £ 2,630.52 |        |         

Alaric
Lemon Half
Posts: 6033
Joined: November 5th, 2016, 9:05 am
Has thanked: 20 times
Been thanked: 1399 times

Re: Logging ongoing holdings in Excel.

#10717

Postby Alaric » December 1st, 2016, 9:11 am

YeeWo wrote: I attach below the way in which I log transactions in Excel.


To make both XIRR and unitisation work, you need dated stock valuations. The basic portfolio data, as would be supplied by an online Broker usually has something like

Name
Number of Shares
current price
Market Value
Book Cost ( that's the accumulation of payments for purchases of shares still held, adjusted proportionately for disposals). It's the number that would go in a CGT calculation so includes buying costs.

YeeWo
Lemon Slice
Posts: 424
Joined: November 5th, 2016, 10:12 am
Has thanked: 297 times
Been thanked: 118 times

Re: Logging ongoing holdings in Excel.

#10756

Postby YeeWo » December 1st, 2016, 10:51 am

Alaric wrote:Name
Number of Shares
current price
Market Value
Book Cost ( that's the accumulation of payments for purchases of shares still held, adjusted proportionately for disposals). It's the number that would go in a CGT calculation so includes buying costs.

Number of Shares is the running total of the "No." column. Current price (£2.09) is at the bottom. Market Value (£7826.25) as also at the bottom. Book Cost (£5195.73) also at the bottom. The dated stock price is shown on the far-right hand column. Is anything missing that would make doing an XIRR exercise impossible?

tjh290633
Lemon Half
Posts: 8209
Joined: November 4th, 2016, 11:20 am
Has thanked: 913 times
Been thanked: 4097 times

Re: Logging ongoing holdings in Excel.

#10779

Postby tjh290633 » December 1st, 2016, 11:45 am

YeeWo wrote:I have read many words around XIRR and Unit-isation for holdings. I attach below the way in which I log transactions in Excel. It would be really wonderful if a kind-hearted-lemon would critique my record-keeping. I'm aware of the limitations of my method in not producing an annualised return, however, I adopt the approach of being roughly-correct rather than precisely-wrong. My Tesco holding below, constructive feedback please!: -

Code: Select all

Date      | Type      | No.   | Price       | Fee    |            |        |         
25-Sep-14 | BUY       |  5680 |    11008.69 |  69.06 |            |        |   1.93815
09-Oct-14 | BUY       |  4320 |     8061.77 |  53.32 |            |        |   1.86615
19-Dec-14 | CASH DIVI |       |     -116.00 |        |            |  0.016 |         
13-Jan-15 | SELL      | -2500 |    -5293.75 |  12.95 |            |        |    2.1175
04-Mar-16 | SELL      | -3750 |    -7102.16 |  12.95 |            |        |   1.89391
20-Jun-16 | BUY       |  3750 |     6043.13 |  40.27 |            |        |    1.6115
06-Oct-16 | SELL      | -3750 |    -7605.00 |   10.5 |            |        |     2.028
          |           |       |             |        |            |        |         
          |           |       |             |        |            |        |         
          |           |       |             |        |            |        |         
          |           |       |     4996.68 | 199.05 | £ 5,195.73 | £ 1.39 | Avg Price
          |           |       | Share Price | £ 2.09 | £ 7,826.25 |        |         
          |           |       |             |        |     50.63% |        |         
          |           |       | MoS/Loss    |        | £ 2,630.52 |        |         

You are almost there. Add price and fee together and use that column and the date column to input the XIRR function.

The last entry should be the current date and the current holding value, as a negative figure. If your dividends are reinvested in the portfolio, then you only need to enter capital inputs and outputs of cash, and the current value, the latter two as negative items. That will give you the Total Return for the portfolio as a whole.

Here is your example rejigged slightly:

Code: Select all

Date          Type            No.      Sh Price     Cost          Fee       Cash flow
25/09/14      BUY               5680       1.9382   £11,008.69     £69.06   £11,077.75
09/10/14      BUY               4320       1.8662    £8,061.77     £53.32    £8,115.09
19/12/14      CASH DIVI                               -£116.00                -£116.00
13/01/15      SELL             -2500       2.1175   -£5,293.75     £12.95   -£5,280.80
04/03/16      SELL             -3750       1.8939   -£7,102.16     £12.95   -£7,089.21
20/06/16      BUY               3750       1.6115    £6,043.13     £40.27    £6,083.40
06/10/16      SELL             -3750       2.0280   -£7,605.00     £10.50   -£7,594.50
01/12/16     Current Value      3750       2.0510    £7,691.25              -£7,691.25
                                                                                     
             XIRR                                                                8.47%

I've added the share price column in a logical place, and created the cash flow on the right. The placed the XIRR formula as shown, using the cash flow column and the date column. I haven't included a "guess" figure.

The "Current Value" share price is what it currently is for TSCO,

TJH

TwmSionCati
Lemon Pip
Posts: 54
Joined: November 9th, 2016, 12:11 pm
Has thanked: 2 times
Been thanked: 8 times

Re: Logging ongoing holdings in Excel.

#11409

Postby TwmSionCati » December 2nd, 2016, 9:27 pm

The IRR on the series {price+fee} is a measure of the rate of total return on the investment: 8.47%pa, as TJH says.

I find it useful to go a little further by calculating the capital gain on each sale (e.g. 4/03/16: sold 3750 that cost £1.9193 each on average for £7089.21, a gain of -£108.10; 1/12/16: sold 3750 that cost £1.7708 each on average for £7691.25, a gain of £1050.89).

The IRR on the series {price+fee-gain} is then a good measure of the rate of income return on the investment: 0.39%pa, in this case.

Code: Select all

     Date           No.     Price+Fee     Av.cost     Gain      Price+Fee-Gain
     25/09/14      5680     -11077.75     1.9503                 -11077.75
      9/10/14      4320      -8115.09     1.9193                  -8115.09
     19/12/14                  116.00     1.9193                    116.00
     13/01/15     -2500       5280.80     1.9193      482.59       4798.21
      4/03/16     -3750       7089.21     1.9193     -108.10       7197.32
     20/06/16      3750      -6083.40     1.7708                  -6083.40
      6/10/16     -3750       7594.50     1.7708      954.14       6640.36
      1/12/16     -3750       7691.25     0.0000     1050.89       6640.36


So calculated, the rate of income return agrees with the ordinary measures of income return on bonds, annuities & savings accounts.

TSC

tjh290633
Lemon Half
Posts: 8209
Joined: November 4th, 2016, 11:20 am
Has thanked: 913 times
Been thanked: 4097 times

Re: Logging ongoing holdings in Excel.

#13298

Postby tjh290633 » December 8th, 2016, 12:18 pm

The IRR on the series {price+fee-gain} is then a good measure of the rate of income return on the investment: 0.39%pa, in this case.


Of course, currently the income return from Tesco is Zero, as they haven't paid a dividend for two years now.

TJH

TwmSionCati
Lemon Pip
Posts: 54
Joined: November 9th, 2016, 12:11 pm
Has thanked: 2 times
Been thanked: 8 times

Re: Logging ongoing holdings in Excel.

#13613

Postby TwmSionCati » December 9th, 2016, 11:30 am

TJH: yes, that’s clear enough from the sequence of cashflows that YeeWo supplied in the first place. But why does it matter? For your purpose and mine — that is, of illustrating internal rates of return — just about any randomly-chosen cashflow-sequence would have done as well, surely? Are you getting at some deeper point? If so, could you please be a little more explicit?

YeeWo asked for “constructive feedback”, and it was in that spirit that I suggested a method of measuring the rate of income return — which I thought he might be interested in, as he said he’d “read many words around XIRR and Unit-isation for holdings”. I was merely pointing to a technique that’s much less trouble to manage than unitisation.


TSC

tjh290633
Lemon Half
Posts: 8209
Joined: November 4th, 2016, 11:20 am
Has thanked: 913 times
Been thanked: 4097 times

Re: Logging ongoing holdings in Excel.

#13758

Postby tjh290633 » December 9th, 2016, 5:31 pm

TSC wrote:
Are you getting at some deeper point? If so, could you please be a little more explicit?

I was getting at your point:
The IRR on the series {price+fee-gain} is then a good measure of the rate of income return on the investment: 0.39%pa, in this case.

And that tells you what the rate of income return has been over the two year period, but ignores the current drought of dividends.

TJH

Jon46
Posts: 47
Joined: November 4th, 2016, 11:45 am
Been thanked: 1 time

Re: Logging ongoing holdings in Excel.

#13796

Postby Jon46 » December 9th, 2016, 7:07 pm

I think you two are at cross purposes.

TSC is talking about a general technique (which is correct and well used in finance), which he has applied to the data given. Full Stop.

It happens to be TSCO, that is irrelevant to what he has brought to the OP's attention.

TJH: He knows full well that TSCO are now not paying divis, that is not germaine to the point he is making at all.



FWIW I think TSC's method is over complicated, you can estimate the income rate of return, for practical purposes, much more easily as follows:

The IRR is 8.47%
Total profit, sum of net costs inc divis, is £2,495.52
4.65% of profits from £116 of divis
4.65% of the IRR equals, as as quick approximation, guess what..... 0.39%

As CFO I always encouraged my staff to have such simple cross checks to elaborate techniques.

TSC: Unitisation has other uses and is not complex, because once set up, and you do this only once on a properly designed system. it looks after itself too.

Jon

Breelander
Lemon Quarter
Posts: 4179
Joined: November 4th, 2016, 9:42 pm
Has thanked: 1000 times
Been thanked: 1855 times

Re: Logging ongoing holdings in Excel.

#13829

Postby Breelander » December 9th, 2016, 9:39 pm

Jon46 wrote:Unitisation has other uses and is not complex, because once set up, and you do this only once on a properly designed system. it looks after itself too.


Yes, set up unitisation to start from today and going forwards is easy to maintain. Going backwards is darn near impossible. For Income Units it requires you to know the valuation of your portfolio on each date you added or took out capital. For Accumulation Units you need in addition to know the dates and value of every dividend received.

YeeWo
Lemon Slice
Posts: 424
Joined: November 5th, 2016, 10:12 am
Has thanked: 297 times
Been thanked: 118 times

Re: Logging ongoing holdings in Excel.

#14252

Postby YeeWo » December 12th, 2016, 5:30 am

Jon46 wrote:The IRR is 8.47%
Total profit, sum of net costs inc divis, is £2,495.52
4.65% of profits from £116 of divis
4.65% of the IRR equals, as as quick approximation, guess what..... 0.39%

As CFO I always encouraged my staff to have such simple cross checks to elaborate techniques.
Jon46 I'm not clear on how the 4.65% figure has been arrived at? Would it be possible to restate the above and, perhaps, it may aid understanding?

Can anybody advise how to modify the XIRR function for holdings held for <365 days?

I'm incredibly grateful for the responses so far. I've been in Indonesia and have just got back to London, after un-packing the next thing I did was open-up my spreadsheets and add a bit more mathematical-"life" to my records!!

Jon46
Posts: 47
Joined: November 4th, 2016, 11:45 am
Been thanked: 1 time

Re: Logging ongoing holdings in Excel.

#14300

Postby Jon46 » December 12th, 2016, 10:53 am

Hi YeeWo

£116 is 4.65% of £2495.62

Can anybody advise how to modify the XIRR function for holdings held for <365 days?

Since XIRR returns the estimated whole year figure, for shorter periods you get a much more useful estimate of the return by using:

If the timespan is in days

XIRR(VALUES RANGE, DATE RANGE)*IF(timespan>365.25,1,timespan/365.25)

I always use this formula regardless, because it works for any timespan. We used this at work too.

Now for very short periods, the absolute return is usually a better measure, but as months tick by, the formula produces a number much nearer to what you would expect, way before year end.

Still won't correct violent cash flows of course, which will throw XIRR is a tizz. That is why one can produce any number of examples against XIRR, for example when a portofio is still at the nappies stage, where cash injections are a vast proportion of the pot, or when Ernie (or your favourite Aunt's Will) does you a favour. But for more normal and sedate activity, fine.

Before spreadsheets, we programmed IRR of course, and I have written, or specified, subroutines to do it in probably a dozen computer languages. We had the advantage over Excel in that subroutines accept and return more than one parameter, so that trapping poor convergence or too violent cash flows, or correcting automatically for short timespans was programmed in as required. Those 'pioneering' days were good fun, but computer hardware was cr*p!. For example ICL 2970's operating system was written in S3 holons, where the origins of object programming was already evident, so very advanced for its day, but getting the hardware to stay up for long enough was a random affair.

Jon

YeeWo
Lemon Slice
Posts: 424
Joined: November 5th, 2016, 10:12 am
Has thanked: 297 times
Been thanked: 118 times

Re: Logging ongoing holdings in Excel.

#14328

Postby YeeWo » December 12th, 2016, 12:06 pm

Code: Select all

Date      | Type       | No.  | SP     | Price       | Fee    | XIRR flow   |        |         
28-Jun-16 | BUY        | 4888 | £1.805 |   £8,824.21 | £54.67 |   £8,878.88 |        |         
14-Oct-16 | CASH DIVI  |      |        |    -£175.47 |        |    -£175.47 |        |         
02-Dec-16 | CURR. VAL. |      |        |             |        | -£11,266.84 |        |         
          |            |      |        |             |        |      57.48% | IRR    |         
          |            |      |        |             |        | #NAME?      |        |         
          |            |      |        |             |        |             |        |         
          |            |      |        |   £8,648.74 | £54.67 |   £8,703.41 | £ 1.78 | Avg Price
          |            |      |        | Share Price | £ 2.31 | £ 11,266.84 |        |         
          |            |      |        |             |        |      29.45% |        |         
          |            |      |        | MoS/Loss    |        |  £ 2,563.43 |        |         

The above example is my G4S holding which, as you can see, I've only had since 28 Jun 16. Your "XIRR(VALUES RANGE, DATE RANGE)*IF(timespan>365.25,1,timespan/365.25)" command only produced "#NAME?" as a response? I have used "=(G2/-G4)^(365/(A4-A2))" an IRR command and 57.48% is the result. I'm really not sure if the output is mathematically durable though!! My existing "Mos/Loss" metric is clearly increasingly irrelevant as time goes by, however read in conjunction with an (X)IRR measure it does seem as if my decision making analysis has suddenly become HD & Full Colour! My 8 years of recorded HSBC investing is throwing-up an XIRR of 0% while this woeful performance even includes a regular dividend-flow it really stimulates a measured critique of PYAD-ish capital-doesn't-matter dogma! Maths Feedback would be really appreciated. Thanks!

StepOne
Lemon Slice
Posts: 668
Joined: November 4th, 2016, 9:17 am
Has thanked: 195 times
Been thanked: 185 times

Re: Logging ongoing holdings in Excel.

#14353

Postby StepOne » December 12th, 2016, 1:29 pm

If you are getting a #NAME error then XIRR is probably not installed in your version of Excel. You might need to go to 'Tools -> Add-ins' on the menu and make sure the 'Analysis Toolpak' is ticked. I think I newer versions of Excel (2013/2106) XIRR is included by default.

StepOne

Jon46
Posts: 47
Joined: November 4th, 2016, 11:45 am
Been thanked: 1 time

Re: Logging ongoing holdings in Excel.

#14411

Postby Jon46 » December 12th, 2016, 3:18 pm

YeeWo wrote:

Code: Select all

Date      | Type       | No.  | SP     | Price       | Fee    | XIRR flow   |        |         
28-Jun-16 | BUY        | 4888 | £1.805 |   £8,824.21 | £54.67 |   £8,878.88 |        |         
14-Oct-16 | CASH DIVI  |      |        |    -£175.47 |        |    -£175.47 |        |         
02-Dec-16 | CURR. VAL. |      |        |             |        | -£11,266.84 |        |         
          |            |      |        |             |        |      57.48% | IRR    |         
          |            |      |        |             |        | #NAME?      |        |         
          |            |      |        |             |        |             |        |         
          |            |      |        |   £8,648.74 | £54.67 |   £8,703.41 | £ 1.78 | Avg Price
          |            |      |        | Share Price | £ 2.31 | £ 11,266.84 |        |         
          |            |      |        |             |        |      29.45% |        |         
          |            |      |        | MoS/Loss    |        |  £ 2,563.43 |        |         

The above example is my G4S holding which, as you can see, I've only had since 28 Jun 16. Your "XIRR(VALUES RANGE, DATE RANGE)*IF(timespan>365.25,1,timespan/365.25)" command only produced "#NAME?" as a response?


Well used literally it would, because it would be double dutch to Excel and produce #NAME!

By VALUE RANGE I mean the normal range of cells with the cashflow values, etc..same for the dates.

So it would actually look something like:

XIRR(G1:G3,A1:A3)*IF((A3-A1)/365.25>1,1,(A3-A1)/365.25) ,depending on where in the sheet you have stored you data.

In practice you calculate the date interval, or the year fraction, separately and store it in a cell, so the formula is easier to enter.

I can assure you that this works a treat and is nothing new. I have just bashed(a bit quickly and I am getting old) in your data, and I get:

28-Jun            £8,878.88			
14-Oct -£175.47
02-Dec. -£11,266.84

Timespan 157 raw XIRR 80.87%
Year fraction 0.430 Corrected XIRR 34.8%

As you can see, the correction is starting to get you a figure nearer to the absolute return, which is 28.87%.
As the days go by, the result would continue to improve towards a usable answer.


Hope this helps,


Jon

TwmSionCati
Lemon Pip
Posts: 54
Joined: November 9th, 2016, 12:11 pm
Has thanked: 2 times
Been thanked: 8 times

Re: Logging ongoing holdings in Excel.

#16999

Postby TwmSionCati » December 20th, 2016, 7:08 pm

Jon46: “TSC is talking about a general technique (which is correct and well used in finance)”

I didn’t know that it’s a general technique “well used in finance”, but am pleased to hear so from a CFO. Would you be kind enough to say what name it passes under, so I can read up about it? And some references to decent articles in econometric or mathematical or philosophical journals would be gratefully received as well. (I’m particularly interested in the idea that the technique is “correct”.)

Thanks.

TSC

TwmSionCati
Lemon Pip
Posts: 54
Joined: November 9th, 2016, 12:11 pm
Has thanked: 2 times
Been thanked: 8 times

Re: Logging ongoing holdings in Excel.

#17004

Postby TwmSionCati » December 20th, 2016, 7:29 pm

Jon46: “you can estimate the income rate of return, for practical purposes, much more easily as” IRR*D/TP, where D=divis and TP=Total profit, sum of net costs inc divis.

When I tested this on my records (for 210 holdings over the last two decades) I found the ratio between the estimated and the actual result to vary from 0.43 to 4.27, with
— 20% between 0.975 and 1.025;
— 36% between 0.950 and 1.050;
— 54% between 0.900 and 1.100;
— 82% between 0.750 and 1.250; and
— 93% between 0.500 and 1.500.

So for 80% of my holdings, the estimated and actual results differ by more than 5%: hardly good enough, even for practical purposes, surely?

Consider, for example, my holding of SDA:

Code: Select all

      Date         Qty        Cash                                  Trans
   27-Jun-2000     1112   -1512.32      Bt 1112 SDA @ 136p
   31-Jul-2000              61.50      5.00p, 2nd interim (25), YE 30/11
   31-Oct-2000              36.90      3.00p, Special interim (16), YE 3
   31-Oct-2000              22.24      2.00p, 3rd interim (1), YE 30/11/
   31-Jan-2001              55.60      5.00p, 4th interim (2), YE 30/11/
   30-Apr-2001              58.38      5.25p, 1st interim (3), YE 30/11/
   31-Jul-2001              58.38      5.25p, 2nd interim (4), YE 30/11/
   31-Oct-2001              58.38      5.25p, 3rd interim, YE 30/11/01
   31-Jan-2002              47.26      4.25p, 4th interim, YE 30/11/01
   25-Oct-2004    -1112                  Delisted


We have IRR=-74.4, D=399 and TP=-1114, so the estimated result is 26.6%pa; but the actual result is 6.9%pa (ratio 3.86).

Then, contrariwise, my holding of RPL:

Code: Select all

      Date         Qty        Cash                                  Trans
   28-Aug-2012      534      -3831.61      Bt 534 @ 715.19p
   23-Apr-2013               188.93      EUR0.60 gr (35.380p net), Div
   25-Apr-2014               181.92      EUR0.60 gr (34.067p net), Div
   24-Apr-2015               184.62      EUR0.70 gr (34.573p net), Div
   22-Apr-2016               217.24      EUR0.75 gr (40.680p net), Div
   20-Dec-2016     -534      10065.23      Sd 534 @ 1884.87394p (notional sale)


We have IRR=28.9, D=773 and TP=7006, so the estimated result is 3.2%pa; but the actual result is 4.8%pa (ratio 0.67).

You also said that the estimate is much more easy to calculate than the actual result. And of course from the point of view of algorithmic complexity that’s entirely true. But in practice, with an app for calculating internal rates of return, finding the rates of total and income return are as easy as one another — as easy as pie.

TSC

TwmSionCati
Lemon Pip
Posts: 54
Joined: November 9th, 2016, 12:11 pm
Has thanked: 2 times
Been thanked: 8 times

Re: Logging ongoing holdings in Excel.

#17005

Postby TwmSionCati » December 20th, 2016, 7:31 pm

Jon46: “Unitisation has other uses and is not complex ... once set up”

I know its other uses, and in particular its canonical use, by unit-trust managers. And I know it’s not complex, once set up. But Breelander’s already mentioned the nightmare that faces anyone trying to do it retrospectively.
For a private investor interested only in measuring the rates of return on his own holding of a single stock most of the obstacles that Breelander lists simply evaporate. Most obviously, he doesn’t need to know the holding’s value whenever he adds or subtracts capital; as he’s not a unit-trust manager, he need only know its value whenever he trades — and that’s easily worked out, even retrospectively, from the statements of any common-or-garden stockbroker.
It’s the last of Breelander’s obstacles that’s the killer: for the stockbroker’s statements don’t include any clue to the closing price of the stock on any dividend-payment day. That has to be looked up separately.
Which is why, even for this highly constrained task, the technique under discussion is much less trouble to manage than unitisation. All the data needed are on the statements.


TSC


Return to “Portfolio Management & Review”

Who is online

Users browsing this forum: No registered users and 3 guests