Posts Tagged ‘graphing’

Estimate the value of your house or flat with a graph

Thursday, November 4th, 2010

If you want to know what the current value of your house, flat or property is (useful if you are buying, selling, or extending the leasehold for example) you can go for the easy option of asking an estate agent to do a valuation, or use a tool such as Nationwide’s house price calculator. Both have their disadvantages; the estate agent will almost certainly try to sell you an overly inflated figure to get your business, and an online tool such as the one from nationwide bases the prices on a very wide region (e.g. Greater London).

If you live in an area with lots of similar properties (e.g. a block of flats, or a street with very similar houses) and are good at Excel you can produce a localised graph of property prices over time. The scatter graph below is of the prices of flats for one specific street in London. The red line is the Nationwide regional data for flats in Greater London. Based on the sale prices of other similar properties you can make a guess as to the value of yours.

house price estimation graph 10

I’m going to assume you have a reasonable amount of Excel knowledge – this is *not* a step by step guide. To follow this you need to know how to import data into Excel, produce graphs, add new data to existing graphs, and format/sort data.

To start with you’ll need historical property sale prices. There are lots of websites that you can get them from, but www.houseprices.co.uk gives you them in a form that is easy to import into Excel.

house price estimation graph 1

Try searching by postcode and by street name to get the relevant prices for your block of flats or street. I’d suggest setting the number of results to 100 so the data isn’t too broken up by their adverts.

Copy and paste the prices tables (there will be multiple tables split by ads) into your Excel sale prices spreadsheet one after the other.

house price estimation graph 2

This produces a basic date vs price graph. If you select from the first date / house price to the last then Excel seems to correctly guess which way round the X and Y axis should be. If you include the column names in the selection Excel gets it wrong.

house price estimation graph 4

If you want to compare against regional data like I did, go to Nationwide’s house price data download page. I chose the ‘Regional Series’ / ‘Flats (Post 91)’ as the best match for the actual property price data I had.

house price estimation graph 5

The Nationwide spreadsheet data looks like this with prices by quarter and region.

house price estimation graph 6

Unfortunately Excel doesn’t understand the date formats in this spreadsheet (e.g. Q2 2009) so I used Find and Replace to change the Q1/Q2/Q3/Q4 values into dates. E.g. I changed ‘Q1 ‘ into ‘01/01’. Note that I used the find and replace to remove the extra space.

house price estimation graph 7

Then I marked the column data format as being in ‘date’ format.

house price estimation graph 8

I then copied the Nationwide data into a separate area of my original property sale prices spreadsheet. The data from the Nationwide spreadsheet is in chronological order, whereas the data from houseprices.co.uk is in reverse chronological order. I therefore sorted the Nationwide data to be in reverse chronological order as well so that it could be easily added to the same graph.

Finally I added a new data series to my original graph by selecting the Nationwide dates for the X axis and the prices for the Y axis.

house price estimation graph 9

And this is of course the result (I did a bit of tidying / formatting of the graph to make it look better), which will look familiar as being the graph at the start of this post.

house price estimation graph 10

It can’t predict the future, but you can make some useful assumptions based on past property prices. And it can give you a good indication if your estate agent is over inflating his/her estimate.

Graphing the Post Office Over 50’s Life Cover plan

Saturday, October 16th, 2010

Two years ago I produced some graphs showing how much you might pay vs how much you’d get back with two over 50 plans from AXA Sun Life. Today (Saturday 16th October 2010) The Times has included one of these graphs in its paper, so I thought I’d produce another set of graphs for a different company. This time it is The Post Office.

As before I’ll mention that I’m not a financial advisor, I have no personal connection to these kinds of plans, and I’m not making any kind of recommendation. All I am doing is turning the numbers for the Post Office Over 50’s Life Cover plan into some graphs. I’ve done my best to make these graphs accurate, but if you spot any problems leave me a comment.

I got a quote for a 65 year male using their website based on paying in £7 per month. Using these figures will get you a fixed £892 lump sum (or rather your family will), payable on death. You get this lump sum if you die after the first 12 months. If you die in the first year you get your premiums back. Your payments stop after 20 years when you are 85, and there is no cash in value. So if you stop making payments you get nothing back.

There are some other terms which affect the plan: if you die in an accident within the first 12 months you get your full premium back, and if you die of an accident after the first 12 months you get double your premiums back. I am not taking these additional terms into account for my graphs, but of course you might take them into account if considering a policy like this.

That is a rough summary in words – but what does it look like when turned into a graph?

post office over 50s life cover 1

The pink line is the lump sum payable which remains fixed, and the blue line is what you pay in. This remains fixed until you have paid in for 20 years. This person starts paying more in than they’d get out of the plan once they reach 76 years old.

It can be useful to compare the figures against what you’d get if you put the money into a bank account. This graph has two additional lines. One for saving this amount monthly at 2%, and the other at 4%.

post office over 50s life cover 2

These plans are all to do with when you die. Obviously the companies selling these plans have to make a profit (you don’t think anyone would give you free money do you?), so they use the average life expectance to work out how much life cover to give you.

Some people will get more than they paid in, but as with all other kinds of insurance the company has to make more money back. If the companies who make these policies paid out more than you gave them (on average) then they’d go out of business.

The National Statistics Office produce tables of life expectancy information. By looking at the ‘Great Britain, Interim Life Tables, 1980-82 to 2007-09’ I can see that a 65 year old male has a life expectancy of another 17.61 years. This means a man who is now 65 years old will die when he is 82.61 years old (on average of course).

Here are the above two graphs again, but this time with a smaller scale. Instead of being from 65 to 100 years, they are from 65 to 85 years.

post office over 50s life cover 3

post office over 50s life cover 4

Again as I mentioned above these kind of plans do offer other benefits such as accident insurance, so you have to make up your mind using all the available information.

But I hope you can see that a little bit of work in Excel can make the numbers a lot easier to understand.

The Post Office Over 50 Life Cover page is here, and on this site you can find similar but old graphing information for the AXA Sun Life Guaranteed Over 50 plan.