Spreadsheets

Car ownership cost sheet: what a car really costs per year in Excel

Work out what a car costs a year with an Excel sheet: fixed and variable blocks, cost per month and per kilometre, and a side column that compares cab spending.

Editorial illustration for Car ownership cost sheet: what a car really costs per year in Excel

A car ownership sheet splits costs into two blocks. Fixed costs arrive whether the car moves or not: insurance, EMI, parking, annual service. Variable costs rise with use: fuel, tolls, repairs, washing. Add them for the year, divide by 12 for a monthly figure and by kilometres driven for a cost per kilometre, and you can put a real number against the question of whether you should be using a cab instead.

What goes in the fixed block?

Set up three columns: Item, Per payment and Times a year. In a fourth column, Yearly, write =B2*C2. List:

  • Insurance renewal, once a year
  • Car loan EMI, twelve times
  • Parking at home or office, usually monthly
  • Scheduled service, typically a fixed number of times a year; use your own pattern
  • Annual items such as the pollution check, road-side assistance or club fees if you pay them

Total it with =SUM(D2:D8). Whether to include depreciation is up to you: it is real money, but it is not a monthly cash outflow, so keep it out of the table and mention it in a note.

What goes in the variable block?

Fuel is the big one; take the total from the last twelve months of petrol pump and UPI payments rather than guessing. Add FASTag recharges or toll receipts, repairs and parts, tyres when changed, washing, and fines if any. Put a yearly amount against each, then =SUM over the block.

A worked example

The numbers below are made up. Replace each with your own.

BlockItemCalculationYearly
FixedInsurance1 x ₹22,000₹22,000
FixedEMI12 x ₹14,000₹1,68,000
FixedParking12 x ₹1,500₹18,000
FixedServices2 x ₹6,000₹12,000
VariableFuel12 x ₹6,000₹72,000
VariableFASTag and tolls12 x ₹800₹9,600
VariableRepairs and extrasas spent₹8,000

Fixed adds up to 22,000 + 1,68,000 + 18,000 + 12,000 = ₹2,20,000. Variable adds up to 72,000 + 9,600 + 8,000 = ₹89,600. The year totals ₹3,09,600, which is ₹25,800 a month. If the car runs 9,000 km in the year, the cost per kilometre is 3,09,600 / 9,000 = ₹34.40.

How do I compare the car with cabs?

Add a side column called Cab. Put your cab and auto spending for the same twelve months in one cell and the kilometres you would have travelled by cab in another. Divide cost by kilometres for a cab rate per kilometre. If the rate is ₹18 a kilometre, the break-even distance is the car's yearly cost divided by that rate: 3,09,600 / 18 = 17,200 km. Below that distance, on these rough figures, cabs cost less; above it, the car does.

That comparison leaves out convenience, comfort, resale value, the car's depreciation and what else the money could do. It also depends on honest kilometres, so read the odometer on the first of each month and write it down.

Which formulas keep the sheet tidy?

  • Monthly cost: =TotalYearly/12
  • Fixed share: =FixedTotal/TotalYearly, formatted as a percentage
  • Cost per km: =TotalYearly/KmDriven, using the cells you have named or the cell addresses
  • Cost per km if you drove 12,000 km instead: =TotalYearly/12000, which shows how fixed costs spread out

Where do the actual numbers come from?

From your payments. Fuel, FASTag recharges, service bills and parking show up as UPI and card transactions. If you import your statements into SpendixAI and file them under Fuel, Parking and Vehicle service categories, a category report gives you the twelve-month figures to type into the sheet, and you do not have to rebuild them from memory.

Review once a year

Refresh the sheet at renewal time. Check how the fixed block changed, whether the EMI ends this year, and whether your kilometres have gone up or down.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports