Car Cost Calculator

[waue1978]

I've been on what they call a "Development Pathway" at work since October & one of the things that I identified as a missing skill was use of Excel spreadsheets. Seeing as I've just bought a new car (that is using a fair bit of fuel at the mo) I thought I'd get a bit of practise & see if I could put something useful together.

I've managed to construct a spreadsheet that works out an average annual cost of owning a car taking most common factors into account - fuel consumption, depreciation, maintenance, insurance & road tax - with 2 identical sheets for 2 cars & a 3rd tab for comparisons between the 2.

Was going to upload it as an attachment so that I could get a bit of feedback from some of the more computer skilled members & maybe see if anybody could improve on it a bit, but I got the message "The extension xlsx is not allowed".

Is there any way I can upload it or host it somewhere so that other people can use it or download it?

[jonsowman]

You could try zipping it? I'm not sure what file types the forum permits...

[waue1978]

Top man. Should hopefully be attached to this.

[rwb]

Good effort

I spend quite alot of time developing this kind of thing (here's one I made earlier), so here's the benefit of my hindsight:

* The data tables for insurance and maintenance are fiddly to enter data into because of the blank lines. The other issue here is that these tables (especially the maintenance one may grow quite long. Therefore put all of the fields whose dimensions are fixed up at the top of the worksheet, then put the tables at the bottom so that they can grow downwards indeffinitely without crashing into anything. (Put totals above the tables, not at the bottom.)

* Use some colour People like that.

Here's what I haked it round to.

[waue1978]

Good effort

I spend quite alot of time developing this kind of thing (here's one I made earlier), so here's the benefit of my hindsight:

* The data tables for insurance and maintenance are fiddly to enter data into because of the blank lines. The other issue here is that these tables (especially the maintenance one may grow quite long. Therefore put all of the fields whose dimensions are fixed up at the top of the worksheet, then put the tables at the bottom so that they can grow downwards indeffinitely without crashing into anything. (Put totals above the tables, not at the bottom.)

I'm very much a beginner at it, so I was relatively chuffed that all of the formulas worked. Plus the original idea was just for something I could use to work out whether it was worth getting something slightly less fuel efficient to balance out maintenance costs.

Here's what I haked it round to.

Is that one that you'd produced or did you do all that to the one I made? That looks great. Any chance I can get a copy or some tuition on how to do it on mine?

[rwb]

It's an ambitious first project, and has come out pretty good. You should be really pleased with it.

Have a go at copying what I did -- you'll learn more trying to re-produce it yourself than just looking at my copy.
Ask away! All you need are the three bottom right buttons in the 'Font' section on the 'Home' ribbon: cell border, cell colour, and font colour.
Then of course you'll have to re-jig all your formulae. I learned the hard way to leave 'elbow room' -- something you don't forget once you've spend all day adding a single column to a worksheet.

So now you've got formulae and formatting cracked, let's move onto macros

[sirwiggum]

Our intranet wiki in work uses external macros that pull data from an excel sheet and use it to display data on the page.

[waue1978]

So now you've got formulae and formatting cracked

Apparently not quite. I found a flaw with it today. When the term of ownership is less than a year, it screws up the figures in the insurance/road tax total. Used 2 months as an ownership term & because 2/12 is less than 1, when it divides by that in the insurance column at the bottom it increases the total. Works fine as long as a multiple of 12 is used in the Term of Ownership, but goes wrong with the partials. Any ideas?

Also, when you rejigged the tables about in your version, were you able to just copy & paste it all or did you have to input the formulae again?

[rwb]

[waue1978]

& that is where your vast knowledge & experience puts my effort to shame.

=G5/YEARFRAC(C$6,C$7)

Never even knew about YEARFRAC. I was just working with basic +, -, x & /.

Can't find the Wayne's World - "We are not worthy" smiley...


Is that your current & previous car in the examples? Amazing when you put everything together to see what it costs. Definitely useful for people considering lease deals (which I wouldn't though).

[rwb]

I swear I never knew about that function
I was just browsing through the list of date functions to see what might be useful and it fitted.

Date stuff in Excel can be quite difficult. In fact, date calculations are normally fairly unpleasant.

Yeah, those are my two 406s. So now you can all see how ripped off I've been
Even the first one -- which was a complete lemon -- turned out to be way cheaper than leasing.

In hindsight I should've got a 2.2 HDi in the first place. I'm convinced -- and perhaps this shows why -- that the best way to buy a car is something 3 to 4 years old for about £5k with about 100k on it that's been on the motorway all day every day.

[waue1978]

Know it's been a while, but finally got a bit of time with a full version of Excel to be able to polish it up a bit. Didn't go into the YEARFRAC thing as for some reason it was going funny with the version at work when I tried playing with the formulae on Rich's one. Instead I've kept the formulas simple & got round the problem with partial year ownership by adding in the monthly costs. If you want to add in details or dates, just type them over where it says "Garage Bill" etc.

[waue1978]

[waue1978]

Found a glitch in it last night, but fixed it now. Would be interested if anybody finds this useful at all. Certainly gave me an eye opener when I compared my costs over a couple of years with my 530d compared to my potential replacement.

[rwb]

Just updated my spreadsheet, having had an oil change and the handbrake adjusted. Air con re-gas is tomorrow morning. And I think I'll get new air filter and pollen filters -- the current ones have done 30k in two years.

Currently: £168 per month on tax, insurance, servicing, MOT fees, and depreciation.

Including fuel too, I've spend over £22k on this car!

I also just realised that to make £10 go for 100 miles you need to be doing about 64MPG.
Back in 2001 I used to get 100 miles from £10 in my Renault 5 1.4.