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

I’m back from holiday and it’s time to start my new series on portfolio returns (as discussed in my previous post) with some basics on averages, limits and other interesting information you can extract from your data without too much effort. Since I’m working on a spreadsheet alongside these posts, I will also include some Excel references that you can use to shortcut the calculations.

Based on my outline, this is what I’m looking to cover in this post:

• arithmetic mean
• geometric mean
• frequency distribution / histograms
• maximum and minimum value
• positive / negative number of weeks / months / years

Arithmetic Mean

The arithmetic mean is more commonly referred to as the average (or just mean) and is calculated by summing all numbers of a list and subsequently dividing the result by the number of items in the list. Finding the average of three numbers A, B and C would involve forming the sum A + B + C and dividing by 3. It answers the question “If all quantities had the same value, what would that value have to be in order to achieve the same total?” and the calculation is relevant whenever the quantities concerned add together to produce a total. For instance, if you buy milk, bread and butter you will be expected to pay a total sum to cover the price of these three items and calculating the average would tell you what price a different product X would have to have so that you could buy three instances of X and end up paying the same amount.

The trouble with the simple average is that is greatly influenced by outliers, i.e. extreme values at either end of the spectrum. If you calculate the average of ten 5s and 1000 you will end up with (10 * 5 + 1000) / 11 = 95.45 which is unproportionately large and could give the impression (assuming the individual data points are not known) that the data is concentrated around values just short of 100. This problem is a simple example of skewed distributions where the mean of a data series doesn’t coincide with the median (the “middle value” if you were to list them all).

Finally, the arithmetic mean is not suitable for calculating stock performance since you’re looking for the product of the yearly performance, not the sum of those values. Consider an investment that loses 10% of its value in the first year and gains 30% in the second year. Now the annualised performance since you started investing is not [(-10%) + 30%]/2 = 10% but in fact 8.2% since the 30% increase only affects the 90% of your investment that’s left after the first year. To correctly calculate annualised performance rates like this we will have to use the geometric mean.

Excel Function: AVERAGE(number1, [number2], …)

Geometric Mean

The geometric mean is a value that gives an indication of the central tendency of a set of numbers (i.e. of a distribution) regardless of whether this distribution is normal or skewed. In other words, it shows the “typical value” of a list of values. Note that this is different from the most common value which is a mathematical value called “mode”. It answers the question “If all quantities had the same value, what would that value have to be in order to achieve the same product?” and hence is relevant whenever quantities multiply together to produce a product. The geometric mean is always less than or equal to the arithmetic mean.

To calculate the geometric mean you need to multiply all the values in your set and subsequently find a scientific calculator to take the nth root of the product where n is the number of numbers you previously multiplied. In the simplest case when calculating the geometric mean of two values A and B you would need to take the square root of A*B. Unfortunately, this also means that the geometric mean in its purest form can only be applied to positive values as (in conventional mathematics and without exploring imaginary values) you cannot take the root of a negative number.

While we all hope that our investments are going to increase year after year, we can’t simply assume this and happily apply the geometric mean without a little work first. To take negative growth into account, you will need to look at your percentage values from a slightly different angle. Let’s go back to our original example of a return of -10% in the first year and 30% in the second year. After the first year, only 90% of your original investment will remain - that is, your portfolio will stand at 0.9 instead of 1 (= 100%). In the second year, you’re more lucky and see a 30% growth of that remainder. If you would have had a fresh portfolio (i.e 100% = 1) you’d now be left with 130% of your money or 1.30. Since you only had 90% remaining we need to multiply 0.9 by 1.30 to get 1.17. This means that in relative terms to the amount you started with, your investment is worth 17% more after 2 years. To now calculate the annualised growth rate of your investment we need to take the square root of 1.17 which yields 1.0816 or a growth of 8.16% per annum.

The Excel function given below does not convert your percentages accordingly so the restriction with regards to positive values still applies. So far, I haven’t been able to find an in-built function that does the work for you which I find rather frustrating. It is extremely annoying to represent a 10% drop in my portfolio as 0.90 and if I don’t come across a more workable solution soon, I will probably end up writing a function myself If you know something that I don’t please let me know before I spent hours writing an Excel Add-In from scratch.

Excel Function: GEOMEAN(number1, [number2], …)

Frequency Distribution

If you’re a visual thinker like myself, you will appreciate that I often find it more helpful to look at a graph instead of raw numbers. An obvious choice in the case of portfolio returns is a histogram which is based on a frequency distribution. At this stage, I won’t dwell on this topic for long as I haven’t got sufficient data yet to experiment with histograms and the associated frequency distributions yet. As soon as that is the case, I will almost certainly give you a little more insight into this topic.

For the moment, imagine that you have two buckets in which you need to throw little cards that represent your portfolio performance - whether you’re looking at weekly or monthly returns or any other values is fairly irrelevant as long as you have enough data. Your task now is to separate the positive returns from the negative ones by throwing them into their respective buckets. Once you’re done with that, you can draw a simple bar graph with the number of items per basket representing the height of an individual bar (assuming one unit width per bar). Voila - there is your first histogram

If you imagine that you could have as many buckets as you like, then you only need to come up with a criteria to decide which bucket contains what data to create whatever histogram you like. You could distinguish between negative and positive values, determine 5% steps or pick any other classification you might find useful. The only rule is that categories must not overlap - i.e. there is always one unique bucket for every single piece of data and at no point would any value (whether in your sample or not) match two or more buckets.

The interesting thing about histograms is that it’s not technically the height of a bar that determines the value it represents (unlike in a simple bar chart where only the height matters) but it’s the area that is covered by it. Hence you could draw a bar that’s one inch wide and three inches high and that would represent the same value as a bar that’s three inches wide and one inch high. For this reason the bars of a histogram have to be adjacent with no gaps in between.

Because you are free to choose however many buckets you want to use when constructing a histogram (the official term for my made-up bucket is “bin”) much research has been done to determine the optimal number of bins as too small a number might hide valuable insights while too many bins render the diagram useless. The level of granularity is crucial. One formula that has been put forward is k = [(max(x) - min(x)) / bin width h] where k represents the optimal number of bins and max(x) and min(x) denote the maximum and minimum values of your data respectively. Whatever value you calculate, you should round up to the nearest full value and use this to create your categories.

Excel 2007 includes an Add-In that creates a histogram from your data if presented in the correct way (data values in one column, bin limits in second column) and hence extends the basic FREQUENCY function that simply returned an array of values. While I have Excel 2007, the data arrangement it requires to execute properly doesn’t work very well if you’re planning to have more than just a histogram graph in your worksheet. Further, from the previews I have seen of the functionality, the histogram looks wrong as there are in fact gaps between the bars (which violates the rules given above). Hence, once more I find myself wondering whether I should invest the time and effort to create something a little more elegant myself. Stay tuned

Maximum and Minimum Value

Since I assume you graduated from primary school long ago, I don’t really need to explain the meaning of maximum and minimum value to you. I simply include it for completeness’s sake and to create a central place for all important Excel functions

Excel Function: MAX(number1, [number2], …) and MIN(number1, [number2], …)

Positive and Negative Number of Weeks

This is an interesting little insight I came across in the book I was reading about Asset Allocation not long ago. While it doesn’t tell you that much per se, it gives you valuable perspective and a sense of patience when you’re tempted to give up your long-term investing for short-term speculation. If you can see that your investment is up most weeks / months / years and only down in value occassionally, you might find it easier to sit out the rough patch - rest assured that other (better) times will come.

To avoid having to count these items manually every time you update the performance, you can use the Excel function COUNTIF that looks at a list of values and only counts the instances that fit a criteria given as part of the formula. By using the criteria “>0″ you will end up with a count of all positive values, while “<0″ returns a total of negative occurences.

Excel Function: COUNTIF(range, criteria)

I hope you enjoyed this first introduction into maths and basic statistics as I very much look forward to researching the upcoming posts! If you have any questions or concerns, just leave a comment and either myself or another reader will surely be able to help.

Bookmark It

Hide Sites

With the new tax year having started yesterday we find ourselves facing a complete overhaul of the tax system that will affect (pretty much) every one of use. Well, it’s not exactly a complete make-over, but still relevant enough to have a noticeable impact. Since my post “What the taxman claims” still holds the pole position of popular posts (awesome alliteration!), I figured it was time to update the spreadsheet that comes with that post to reflect the new tax regime. A version 2.0 if you like…

But before I’ll share a link with you that you can use to download the new version of the tax spreadsheet, a short summary of what exactly has changed since we last wondered how much tax the government was getting from us:

• Personal Allowance: This tax-free allowance was increased from previously £5,225 to £5,435 - a 4% increase to keep up with inflation, if you really wanted to know. As long as you’re under 65 years old this is the amount you don’t owe the government a penny on (per tax year).
• Tax rates: As of yesterday there is no such thing as a 10% tax rate anymore. It has been completely abandoned in favour of reducing the basic tax rate from 22% to 20%. No changes to the 40% tax rate though I’m afraid - except for the threshold after which you will have to pay it. Once your personal allowances is fully used, you can expect to pay 20% tax on the next £36,000 you earn a year. Hence, any income that goes beyond £41,435 (£1,500 higher than before!) will be taxed at 40%.
• National Insurance: In line with the tax amendments, National Insurance contributions were adjusted as well. The basic threshold now lies at £90 p.w. (or £4,680 p.a.) below which you don’t have to make any contributions at all. After that, you are expected to pay 11% of your gross pay up to a limit of £770 p.w. (£40,040 p.a.) followed by 1% of everything that exceeds £40,040. Last year’s 1% threshold was fixed at £34,840 which means that people can now expect to pay slightly more NI as the 11% rate applies to a further £5,200.
• Student loan repayments: No change here Earnings threshold is still sitting at £15,000 p.a. beyond which point you are expected to pay 9% of the remainder (i.e. everything beyond 15k) to settle your debt.

That’s a very brief summary of what’s going to change this (tax) year. The only additional change I have made to the spreadsheet concerns non-taxable benefits. If you are, like me, fortunate enough to be in a position were you get certain benefits from your employer which are deducted from your salary pre-tax, then you can now take these into account when calculating your annual tax due. Just add them into the cell below the bonus payments and it will be automatically deducted from your gross annual salary and not taken into account for the tax, NI and student loan calculations.

And now here it is - the one thing you’ve been waiting for while I’ve been rambling about boring tax changes: the new spreadsheet! Download it here.

A year ago on Simple Pound: Investment Choices - Index Funds

Bookmark It

Hide Sites

Note: an updated version of this post for 2008/09 can be found here.

When I first read through my job offer I couldn’t have been happier with my stated salary and benefits package and I starting daydreaming immediately. However, once I realised how much money would actually end up in my account and how much of that nice sum would go into Her Majesty’s pockets, life suddenly seemed a lot crueller. It took me a while to understand how the UK tax system works and I thought I would share my newly-gained knowledge with you!

First things first: income tax. Any UK resident in employment has a personal allowance of at least £5,225 for the tax year 2007/08 - you can get more if you’re older than 65 or blind, but I’m assuming the majority of my readers won’t be… This personal allowance means that you won’t pay tax on the first £5,225 you earn per tax year (starting April 6th, 2007).

The next £2,230 you earn during 2007/08 will be taxed at a starting rate of 10%. The basic rate of 22% covers all money earned that exceeds £7,455 (= £5,225 + £2,230) up to a total amount of £39,825. That means, for the next £32,370 earned after you covered your personal allowance and starting tax rate in full, you will pay 22 pence of every pound earned to the government. Anything that exceeds the £39,825 will be taxed with the higher rate of 40%.

You can find an overview of the 2007/08 tax rates here. But beware that HM Revenue & Customs quotes the values without taking account of the personal allowance. The values in their table should look familiar if I’ve done a good enough job of explaining how I got to the abovementioned values.

Once you’ve paid all your tax duties, there’s another institution patiently waiting in line to grab their share of your money: National Insurance. This money is used to fund the NHS, social security, job seeker’s benefits and the like. National insurance contributions highly confused me - but, good news, I’ve got them figured out!

The two important values for your NI contributions are the earning threshold and the upper earning limit. The earning threshold is currently set at £5,225 (does that value look familiar to you? ) and you are exempt from NI contributions up to this amount. For any annual income that lies between the earning threshold and the upper earning limit of £34,840 you need to contribute 11% of your salary (if it’s any consolation: your employer needs to pay 12.8% for your whole salary, without caps). Anything beyond £34,840 is taxed at 1%, which means that those poor folks paying 40% income tax get at least a little relief with NI.

Now you are probably thinking: “Those numbers really confuse me… I just want to know what money is gonna come into my account each month… Does it have to be this difficult?” The answer is - No.

I’ve been procrastinating hugely and set up an Excel sheet that will give you a detailed breakdown of all the things I’ve discussed in this post - but with numbers relevant to your situation! All you have to do is download the spreadsheet here, type in your annual salary (as given in your contract) and any bonuses you might be expecting. Once you’ve done that and also resisted the urge of changing the formulae behind the individual cells, you’ll see a breakdown of

• your share of taxes at 10%, 22% and 40% (if applicable)
• your total tax contributions for the tax year 2007/08
• your annual salary after tax deductions
• your NI contributions at 11% and 1% (if applicable)
• your annual NI contributions
• your annual salary after tax and NI contributions
• and finally: your net monthly salary, i.e. the money that should end up in your hands/pockets/wallet/purse

Hope you enjoy playing with the Excel sheet… I certainly do…

Once I’m actually receiving that salary I’ve based my own calculations on, I will let you know whether my predictions came out right! Any questions? Leave a comment.

Update: Upon request I have updated the Excel sheet to include student loan repayments. According to Student Finance Direct you must earn at least £15,000 per annum to be required to pay back your student loan. If you exceed this amount, you need to repay 9% of everything beyond this threshold, i.e. if you get £20,000 a year, you are required to pay 9% of (£20,000 - £15,000 =) £5,000 back to the loan company.

The spreadsheet does not account for your individual circumstances. That means, if the calculated amount exceeds what you have left to pay back, you’re lucky!

Since the student loan repayments won’t necessarily apply to everyone out there who might think this spreadsheet could be of any use, you will see two different numbers for monthly salaries in the spreadsheet now: before and after student loan repayments.

Enjoy!

Bookmark It

Hide Sites