Simple Pound

Thanks for visiting! If you like what you're reading, you may want to subscribe to my RSS feed.

This won’t quite be a conventional Personal Finance post, but the fact that I cannot calculate my portfolio’s performance without converting negative returns into “false positive” ones annoyed me too much to just ignore. Since I couldn’t find a straightforward way around it, I decided to simply code the Excel function I was looking for myself.

While the code is by no means perfect (yet) it does the job nicely and I’m nevertheless pleased with the result. As long as it gets called properly, it converts your negative percentages into a value equivalent to (100 - x) / 100 where x is your percentage. For positive values, it does the conversion according to (100 + x) / 100. These conversions means that a decrease of 10% will be expressed as 0.9 and an increase of 10% as 1.1 which subsequently allows you to compute the geometric mean (which is exactly what it does).

Below is the VBA code you will need to utilise this functionality. Feel free to use it as you please but don’t worry if you have no clue how exactly to do that. I have included a step-by-step description of how to install and call it from your Excel worksheet underneath the code.

Function geometric(data As Variant)
     Dim vaData As Variant
     Dim rnData As Range
     Dim i As Long
     Dim j As Long
     Dim iblank As Long
     Dim jblank As Long
     Dim bblank As Boolean
     Set rnData = data
     vaData = rnData.Value
     temp = 1
     n = 0
     For j = 1 To UBound(vaData, 2)
          For i = 1 To UBound(vaData, 1)
               If vaData(i, j) <> Empty Then
                    vaData(i, j) = 1 + vaData(i, j)
                    ’ compute product for non-empty data cells
                    temp = temp * vaData(i, j)
                    ’ count number of items in list
                    n = n + 1
               End If
          Next i
     Next j
geometric = (temp ^ (1 / n)) - 1
End Function

Follow the steps below to make the above function available in your Excel worksheet:

1. In Excel, open the file you want to use the function in. Note: at the moment the function will only be available on a per-file basis. That means, if you’re planning to use it in more than one file, you will need to repeat this procedure for every single file. I’m working on coding a proper add-on that you simply install into Excel and hence make available to any new or existing file. I told you it was work in progress…
2. In the menu bar, next to the entry “File”, right-click on the Excel icon and select “View Code”
3. The Microsoft Visual Basic Editor opens and you stare at a blank document
4. In the menu bar, click on “Insert” followed by “Module”
5. Copy and paste the above code into that “new” blank space and hit “Save”
6. After you have saved the changes, click on “File” and select the entry “Close and return to Microsoft Excel”. This brings you back to where you started, and your function is ready to use.

Here is how you use the function:

1. Open the file in which you have installed the above function
2. Pick a cell that you want the result to contain and type “=geometric(”
3. Note the opening bracket !
4. With your mouse click and hold until you have highlighted the cell range that contains the numbers you want the geometric mean of. Alternatively, type the cell range yourself as A1:D4 where A1 should be replaced by the coordinates of your first cell and D4 should be replaced by the last cell’s identifier. At the moment, the geometric function only works with a cell range, not individual cells or numbers. I’m working on it…
5. Close the bracket by typing “)”
6. Hit ENTER to see the geometric mean of your highlighted cells

If you format the cell in question in such a way to convert your result back into a percentage (just right-click, choose Format - Number Format - Percentage), the result should make sense in the context of portfolio returns.

Note that the code above has been tested and verified in Excel 2002 and should work with any other Excel version(s) since. The only concern I have regards the compatibility with - you might guess it - Excel 2007. If anyone manages to run it under the new Office version, please let me know!

As usual, if you have questions or concerns, find a bug or just need a hand to get it to work, just give me a shout or leave a comment! I hope you will find it as useful as I did.

Bookmark It

Hide Sites

Admittedly, it’s been getting very quiet on the blog front and you’ve all heard the excuses of working late and being busy, so I won’t bore you for very long before moving on to more interesting stuff. But can I just say that I also walked 20 miles for charity and that took away some blogging time! I bet that’s a new one for most of you…

Anyway, as I promised in my last post (yes it’s been a while) I have had an idea about a series of posts that might be of interest to you, especially if you really really like numbers (like I do). While reallocating my pension across several funds, I kept wondering how to best measure how well (or badly) my choices were performing compared to the market in general. For a start I have split money fairly evenly across index funds and actively managed funds in a way that will hopefully allow me to compare apples with apples - i.e. simply speaking, for each asset class I have picked an index fund as well as an actively managed fund to compare their performance against each other.

Somehow, that didn’t seem good enough and so I did some research on what else I could do. As I said, I like Maths and numbers because they have an inherent logic and beauty… great, now I sound like a total geek. Or idiot. Your choice

Hence, here’s my line-up of posts that will hopefully appear throughout June and July (bear with me as I’m also going on holiday in three days). I’m not going to explain much (or anything) at this stage, because otherwise there’s not much point in writing separate posts about each. I simply hope you’ll be excited to read what’s coming up and all the things you might be able to learn soon(ish).

1. Post : The Basics
- arithmetic mean (average loss, average gain)
- geometric mean
- frequency distribution
- maximum value
- minimum value
- positive # of years / months / weeks
- negative # of years / months / weeks

2. Post : Risk
- VAR: value at risk
- M-squared

3. Post : Statistics
- standard deviation
- semi-variance (semi-deviation)
- downside variance
- below-target probability

4. Post : All About Interaction
- covariance: degree of variability of returns between two assets
- correlation coefficient
- units of annual return per unit of std. dev.
- expected final value of $1.00 / £1.00

5. Post : First lesson in Greek
- beta coefficient or an assets’ degree of responsiveness to market movements

6. Post : Advanced Greek
- alpha or superior returns

7. Post : More Jargon
- sharpe ratio

Some of these will be rather obvious, or aren’t even really mathematical but I thought they were nevertheless useful when analysing your portfolio. I’m going to try my best to explain even the more complex concepts in a straight-forward way that will make you (and me) understand why exactly a particular formula might in fact be useful.

Knowing myself, all of the above will eventually end up in a big spreadsheet, which I naturally will also make available to you so you don’t have to play with Excel for hours to get it all onto one page (surprisingly enough not everyone finds that sort of stuff fascinating).

I’m getting rather excited while writing this, so hopefully my energy won’t be wasted and you’ll enjoy it as well! Let me know if you think I’ve omitted anything absolutely obvious that shouldn’t be missing from a series of posts on portfolio calculations.

Bookmark It

Hide Sites

Okay, you can breathe again. I haven’t actually lost money, but simply reduced the value of my model (!) portfolio by 1.5% yesterday. At work, my team decided it would be fun to see who could make the most money out of £1,000,000 and hence we all started our own little fake portfolios.

Yesterday was the first day that my portfolio, which I had set up over the weekend, was actually subjected to the market and, well I have lost a staggering total of £16,875.24. Of course, I forgot absolutely everything about thorough company research, diversification and asset allocation and simply bought what I thought would make the most money in the fastest possible way (guess that didn’t work, huh?). In my defense, this trial has more to do with competitive speculation than actual investment and hence the it becomes much more of a gamble than stock market investing should be (at least if you eventually want to live of your return!).

I thought you might be interested to see what “horses I bet on” and whether I had an ever so vague reason for doing so (follow link!):

• 3i Group (III): Venture capital firm that favours tech start-ups; + 1.98%
• Anntaylor Stores (ANN): Women’s clothing retailer in US - reason? I contributed to way too much to their profits and now I want something back! +3.24%
• Apple Inc (AAPL): manufacturer of personal computers and related software - some people seem to want to spend a lot of money on the iPhone (why?); -7.02%
• Archer Daniels Midland (ADM): agricultural processing company; -2.22%
• Atlantia (ATL): Italy’s largest operator of motorways; +1.57%
• Banco Santander (SAN): Spain’s largest bank; +0.20%
• Bateman Litwin (BNLN): oil equipment services and distribution sector; +4.44%
• BG Group (BG): Gas and oil exploration arm of old British Gas and major player in global energy market; -2.74%
• BTG (BGC): pharmaceuticals & biotechnical sector; -3.20%
• Carphone Warehouse Group (CPW): Europe’s largest independent retailer of mobile communications; -0.84%
• Clean Harbors Inc (CLHB): I don’t even know what they are/do… was recommended to me (ah well); +0.70%
• Exxon Mobil (XOM): used to be world’s largest publicly traded energy company; -2.73%
• First Group (FGP): one of Britain’s largest transport companies; 0.48%
• Google Inc (GOOG): search engine and do-gooder (*lol*); -4.80%
• L’Oreal (OR): cosmetics group - I love Lancome products and yes I am aware that this is a very girly reason for investing in a company; -1.15%
• PetroChina (PTR): Chinese energy company with market cap of $1,000bn - new world’s largest energy corporation; -5.39%
• Petroleo Brasileiro (PBR): also “PetroBras” - Brazilian oil company announced discovery of new oil field; -11.77% (do you get that??)
• Premier Foods (PFD): food manufacturer; +3.45%
• QXL Ricardo (QXL): online auctioneer; +3.87%
• Rio Tinto (RIO): Mining company for aluminium, copper, gold, diamond, iron and lead; +0.60%
• Rheinmetall AG (RHM): German defence company; +0.45%
• Ryanair Holdings (RYA): no-frills airline operator; -0.20%
• Siemens (SIE): German engineering and mobile phone company; -1.13%
• Telefonica (TEF): communications company who owns O2; +0.89%
• Tiffany and Co (TIF): internationally renowned retailer, designer, manufacturer and distributor of fine jewellery - again active profit contribution on my part; +3.28%
• Vallourec (VK): French steel tube-maker subject to take-over rumours; +2.14%

Let’s see how today’s market will be treating my portfolio…

Bookmark It

Hide Sites