Simple Pound

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

How many old mobile phones have you hidden in the bottom drawer of your desk, top shelf of your wardrobe or in an old box under the bed? Honestly? I found two - and I wasn’t even looking properly. Chances are you’ll find at least one old phone knocking around somewhere, especially if are (or have been) on a phone contract that promises you a new handset every 18 months.

Have you ever thought what to do with the old one? At some point the option of “I can give it to my sister/Mum/Dad/brother-in-law (delete as appropriate)” doesn’t really work anymore and the old handset will soon be forgotten. Why not get some free cash for recycling your phone instead?

The website envirofone.com will help you with exactly that. Head over there now to see what your old phone could still be worth! It’s simple, fast and absolutely no hassle at all. When you register with them, you will automatically receive a “trade pack” consisting of a delivery card and a jiffy bag for you to send your phone in. Once you agree to trade your phone for either cash or an Argos voucher (whose value will be slightly higher than the cash value you’d receive otherwise), all there is left for you to do is to put your (old!) phone into the envelope and drop it in the nearest letter box.

The envirofone website has a “My Account” section that lets you monitor the status of your trades. Once the trade is agreed, it will show up under the “View Trades” tab where the status will say “Awaiting Receipt”. You agree to send the phone within a time frame of 10 days at most and once you have done so the trade status will soon change to “Received” indicating that the envirofone team has received your phone and is currently testing whether it is in full working order.

The quote you got when the trade was initially submitted assumes the phone is functioning properly, but you’re still encouraged to send your phone even if it is not as you might receive up to 90% of the originally quoted value. If a fault is detected by the envirofone people, you will be contacted with a new (lower) quote which you can choose to accept or refuse. If you refuse to trade for the specified amount, the phone will be returned to you. If you decide to accept the lower offer (what else are you going to do with a faulty phone??), you will receive your cheque or Argos voucher in the post within 7 days.

My old phone has been received as of this morning, so I’m waiting for my voucher as we speak. I’ll keep you posted on how long it actually takes, but their overall process seems pretty streamlined and I expect only the best So if you possess one of the 80 million mobile phones that have been forgotten about in the UK, then I think it’s time for you to act…

Bookmark It

Hide Sites

Have you ever been annoyed because you’re getting blamed for someone else’s faults? More specifically, have you ever wondered why the tube/bus/train is running late just the day you needed to be on time? I’ve cursed the London public transport system more than once since having to use it every single day, despite knowing it’s meant to be one of the most efficient and reliable means of transportation in the world - I’ve certainly seen worse (New York City subway - anyone?).

In any case, a colleague of mine told me about the TfL’s (Transport for London) refunds website that you can use to re-claim the price of a single journey in the event that your normal journey time is delayed by more than 15 minutes due to circumstances within the control of TfL (this excludes announced station and underground line closures). Just imagine getting reimbursed for all those signal failures, late track replacement and engineering works!

I’ve done it twice myself so far, and I just received my first £4 voucher (equivalent price of a single journey on the tube) which I will be able to use towards any purchase with TfL in the next 13 months. Yes, it’s not quite as good as getting a cash reimbursement and hence limits the use of the proceeds, but imagine the amount of vouchers you could collect over 12 months (for people with annual season tickets like myself) and subsequently use to reduce your next season ticket - which is fairly unavoidable if you continue living and working in London.

So, whenever you’re next sitting in a dark tunnel, surrounded by strangers and the only words you hear are: “Transport for London would like to apologise for the inconvenience caused”, you can feel a little more content knowing you’ll be £4 richer because of it. Here is the all important link that’ll achieve this: Tube refunds.

One word of caution (inspired by the reaction of a friend after I had just told him about the website): Do not start to monitor delays of other tube lines not affecting your journey and submit claims for every single one of them. Bear in mind that the people responsible for the refunds are hardly stupid and in the best case you’ll get yourself banned from using the service (hence forfeiting a refund when you are actually affected yourself), but in the worst case you’ll ruin the deal for all of us by making TfL abandon the entire scheme (if just a few people started submitting claims for all delays every day it could cost them a fortune!). Thus, use refunds responsibly!

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

It’s been quite some time that I last submitted an article to a Blog Carnival - mostly because I kept missing the deadlines - so I’m especially pleased to announce that my post about mortgage-related costs has been published as part of the most recent Carnival of Personal Finance (#146).

Blain @ Stock Trading To Go was this week’s host and did an excellent job of mixing a regular blog post (his own submission if you like) with the Carnival’s posts. This way you can learn what it takes to pick a good stock broker while reading everyone else’s thoughts on Personal Finance this week.

I strongly encourage you to have a look around and explore what else is out there. Not only because you might learn something, but also because you will probably encounter things of a little unusual nature like Financial Learn’s post about “Ten Things Women Wish Men Knew About Money“.

Bookmark It

Hide Sites

Triggered by a recent post over at No Credit Needed I wanted to introduce you to Goodgifts, a firm working together closely with a number of charities to promote donations instead of “ordinary” presents. This is obviously a rather hot topic around Christmas time where hundreds of thousands of people are left wondering what to give to their loved ones this year.

For weeks (if not months) I have been trying to find a nice present to give to my parents for Christmas. Unfortunately there’s only so much you could give that they don’t already have, not mentioning gifts they would actually want and find useful.

I came across the goodgifts.org website after staring at one of their ads in the tube on my way home from work one evening. They offer a range of presents to replace the ordinary Christmas gift by contributing to a selected number of charities instead and hence helping people who are in real need. Instead of unwrapping another boring and uninspiring present that they will probably not ever look at again, you could be giving your parents, partner or friends the warm and content feeling of having helped and probably changed someone’s life.

They currently have 180 gifts ranging from as little as £10 to as much as £5,000 which should provide a good selection of potential presents for every budget. Let me mention a few examples to give you a better idea of what sort of gifts you would be buying:

• 4 ducks or chickens including initial supply of feed for women in Sierra Leone for £15
• One lamb for Indian villagers for £15
• Clean water for a Bangladeshi family through a simple filtration system for £20
• Health checks for 20 people in India for £20
• A year’s schooling for one African child for £25
• Half an acre of rain forest for £25
• A trio of food trees for £30
• A bike for a mid-wife for £35
• A cow for an Indian family for £95
• A mobile library for African schools for £100
• A water tank for an African school for £500

What I like most about the gifts on the Goodgifts website is that they help children and families towards becoming self-sustaining rather than simply providing them with e.g. a supply of food for a week after which they will have to starve again. Giving some chickens will not only enable them to produce their own food, but they could also potentially sell any surplus they might have. In the longer run, they can breed chickens and hence not only sustain this source of food but also share the chickens with their neighbours and the village.

For every gift you choose, you receive a card as well as a cracker bearing a description of the gift. In order to ensure that every single penny of your donation is used towards the chosen gift(s), Goodgifts levy an administrative charge of £4.95 per order (an order can comprise multiple gifts…). Gift cards can also be sent overseas for a charge of £9.95 which would potentially enable people from the US to use this service as well.

I haven’t decided which particular gift I am going to give my parents (probably one each…), but I am pretty certain that it will be ordered through this website. Oxfam is running a similar scheme at the moment, so if you find that none of the gifts on Goodgifts.org are really for you, check out Oxfam Unwrapped for more ideas with a similar purpose.

Bookmark It

Hide Sites