Posts Tagged ‘Excel’

Graphing the AXA Sun Life 50 Plus Protector

Wednesday, August 27th, 2008

Following on from my post where I graphed the AXA Sun Life Guaranteed Over 50 plan I thought I’d look at a more complex product to see what kind of graphs I could get out of it. Unlike the Over 50 plan, the AXA Sun Life 50 Plus Protector features a lump sum and premium that increases over time. There is also a maximum number of years that the premiums are payable for. As there are a number of extra rules it should produce some interesting graphs!

As before I’ll mention that I’m not writing this to offer an opinion on this particular product. I’m not a financial advisor. My interest is to show how you can convert the information about financial product into graphs. These graphs can be of great help in deciding whether a financial product is suitable for you.

I got a quote from their website for a 60 year old male paying in a premium of £7 per month. These figures are what I was quoted on the day I did the quote (late August 2008).

The premium of £7 will rise by £0.35 per year for a maximum of 20 years when it will be double the initial premium. It will then remain level until it stops altogether at the age of 90. The cash lump sum is payable on death after two years. It is £1095 and will rise by £50 per year. If you were to die within two years the lump sum would be 1.5x the amount paid into the plan. There is no cash in value – if you stop paying money into the plan you get nothing.

The first graph I’ll make is to show how your premiums vary over the years. You can see them increasing every year, until the age of 80 when they level off. After the age of 90 you don’t pay any more premiums.

axa sun life 50 plus protector cost of premium

The next graph shows the cumulative cost of all the premiums paid into the plan, against the value of the lump sum. You can see (if you look carefully) that the premiums paid line increases in angle for the first 20 years. It is then a straight line for the next 10 years. The line then goes flat from age 90 as no more premiums are payable. The lump sum payment starts off at 1.5x the amount of premiums paid in, after two years it goes to the full lump sum value which increases by £50 each year.

axa sun life 50 plus protector plan cost vs lump sum

You can see that there is a crossover point at which you pay more in premiums than the lump sum you get back. You can also see that as you don’t pay any more premiums after age 90 the lines start coming together again. I continued the age range to 120 to see at what point they diverge for the second time.

I then produced a graph to show by what percentage the lump sum and the premiums go up. The text on their website says that both go up by 5% of the original lump sum / premium each year. An increase of £0.35 is indeed 5% of the £7 premium. However £50 of £1095 is actually 4.6%. I’m not sure whether they are rounding the lump sum increase down, or whether there is some error in their calculation.

axa sun life 50 plus protector lump sum increase

Despite the slight discrepancy in percentages both premium and lump sum follow an almost identical curve of decreasing percentage increases each year. The premium increases drop to 0% after the age of 80 as per the plan description. The lump sum increases by £50 each year so the percentage increase keeps dropping. It is therefore important to understand the effect that inflation will have on this plan.

I hope you found this interesting. These three graphs took me about 15 minutes to do and provide details of this product in a much easier to analyse format than the pure text description of the product as given on the AXA page.

As I started before I’m not offering you an opinion of their plan, more a reason why getting to grips with a spreadsheet package like Microsoft Excel will help you with making financial decision.

Graphing the AXA Sun Life Guaranteed Over 50 Plan

Friday, August 22nd, 2008

On TV recently I’ve been bombarded by adverts about the AXA Sun Life Guaranteed Over 50 Plan. The current version of the advert is presented by Michael Parkinson. Previous versions have been presented by June Whitfield.

I am no way near the age of 50 and these plans have no relevance to me. I should also point out that I am not a financial advisor, and am not intending to offer any opinion on these plans. My interest is to look at them from a simple mathematical point of view.

In case you’ve missed the advert the basic idea is this. If you are over 50 you can pay AXA a fixed monthly sum for the rest of your life. When you die a fixed sum (fixed at the time you open the plan) is payable to your family. If you die within two years you don’t get the fixed sum, but your family do get 1.5x your premiums back. If you ever stop contributing you don’t get anything.

I went on their website and got a quote for a 60 year old male paying in £6 per month (the minimum a 60 year old male can pay in on the day I got the quote). This produces a cash lump sum of £760, payable on death after 2 years.

Here is a graph plotting how much you pay in, against how much you get back. You can see there is a cross-over point at which you end up paying in more money than you get back. In this case you end up having paid in more then you’d get out when you reach 71 years old.

axa sun life over 50 plan graph

The government publish data on life expectancy. I got the latest male life expectancy data from 2004 and 2006 and plotted this into another graph. Note how your life expectancy goes up as you get older. This is because you have already managed to avoid dying in the preceding years.

uk life expectancy

You might not be able to make out the detail on the graph but the life expectance for a 60 year old male in the UK is 80.81 years.

This means that if you are an average person you are likely to be paying in 9 years of premiums beyond the lump sum value that you would get back.

However calculating the benefit of these plans isn’t quite as simple as this – they often provide extra benefits such as extra payouts in the event of dying in an accident or whilst travelling.

An obvious factor to look into is the effects of compound interest when adding the same amount (£6) into a bank savings account every month.

compound interest graph on a monthly saving of £6 at 4%

This graph is showing the effect of saving £6 a month based on a modest 4% gross interest rate. I based the calculations to produce this graph on the formula given on patrick schneider blog post. The final figure after 40 years matches the figure given by other compound interest monthly savings calculators I’ve seen on the internet so hopefully the graph is accurate!

Below I’ve put the compound interest curve on top of the previous graph comparing contributions against the cash lump sum.

compound interest graph on a monthly saving of £6 at 4% compared against AXA Sun Life Plan with £6 per month contribution

There are further complications to consider such as the effect of inflation. The real worth of any money your family would get back when using these plans is reduced every year due to inflation. On the other hand the real world cost to you goes down each year as your £6 per month will gradually constitute a smaller percentage of your income.

What are my conclusions? I’m not giving you any! Any decision on whether to use a financial product should be taken based on your personal financial circumstances and with the help of a trained financial advisor (which I’m not).

I would say that these are the kind of analyses you should be doing when investigating or comparing any kind of financial product, whether it be a savings account, loan or mortgage. Turning financial information into simple graphs is a very powerful tool that can save you a lot of money!

Weekly planner in Excel

Friday, April 25th, 2008

If you find that you never manage to get everything done, or if your life is a bit disorganised you may find it helpful to make a weekly plan.

Here is a very simple (and completely free!) weekly planner in an Excel spreadsheet. The best way to use it is to print out a set of them (the whole week fits on one A4 page) and then at the end of each week plan what you will be doing for the next week.

Weekly time planner in Excel spreadsheet

You can download the weekly planner from this link: Week Planner in an Excel Spreadsheet (15kb)

2008 Excel One Page Calendar

Thursday, January 3rd, 2008

For the past few years I’ve found Excel to be useful for creating one page yearly calendars.

2008 Excel Calendar

Here is is a download link to my 2008 Excel calendar – 2008 calendar. It is free to download for personal or business use. I’ve highlighted UK bank holidays in yellow but you can blank out these cells if you live somewhere else.

It will work with Microsoft Excel 97 and above, as well as the free OpenOffice.

Update: A 2009 Excel calendar is now available.