Ravindra BagaleCourses & study guides मराठी Track your progress

Own Car vs Cab Cost in Excel: Monthly Cost per Km and Break-even Distance

By Last updated:

Salman in Pune drives about 800 km a month and pays a car EMI. A friend says cabs would be cheaper; another says a car always pays for itself. Both are guessing. Put every cost of the car into Excel, including the value it loses each year, compare it with cab fares, and the sheet tells you which is cheaper and at what distance the answer flips.

What you'll learn on this page

  • Split car costs into fuel (per km) and fixed costs (per month)
  • Car cost per month and per km
  • Cab cost per month with fare per km and per-trip charges
  • Which is cheaper and by how much
  • The break-even km per month
  • A cost table and line chart by distance

Download the template: own-car-vs-cab-cost-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Salman's numbers for 800 km a month (made-up values):

Own car Per month
Fuel (800 km ÷ 15 km/l × ₹105) ₹5,600
Insurance (₹15,000 a year) ₹1,250
Service and repairs (₹12,000 a year) ₹1,000
Parking ₹1,000
Loan EMI ₹12,000
Value lost (₹60,000 a year) ₹5,000
Car total ₹25,850 (₹32.31 per km)

Cabs: 800 km × ₹20 + 40 trips × ₹30 = ₹17,200 (₹21.50 per km).

At 800 km, cabs are cheaper by ₹8,650 a month. The break-even is about 1,465 km a month; above that the car wins. Once the loan is paid off, the break-even falls to about 542 km, and at 800 km the car would be cheaper.

Inputs (yellow) 800 km a month Fuel, mileage, fixed costs Cab fare and trips Formulas Car = fuel + fixed Cab = km × fare + trips × fee Break-even = solve car = cab Results Car ₹25,850, cab ₹17,200 Break-even 1,465 km 542 km without EMI

Figure 1. Car cost = fuel (km ÷ mileage × price) + fixed costs (insurance, service, parking, EMI, value lost); cab cost = km × fare + trips × booking; setting them equal gives the break-even km.

The own-car-vs-cab-cost-template.xlsx template sheet with example data, yellow input cells and green formula cells

Figure 2. The template sheet, recalculated with its example data. Download it above and replace the yellow cells with your own numbers.

2. Build it step by step

Step 1. Car inputs

  1. In B3 type km per month (800). In B6:B12 type fuel price, mileage, insurance per year, service per year, parking per month, EMI per month and value lost per year (yellow).

Step 2. Car cost

  1. In B13 type =B3/B7*B6: fuel ₹5,600.
  2. In B14 type =B8/12+B9/12+B10+B11+B12/12: fixed costs ₹20,250. Yearly costs are divided by 12.
  3. In B15 type =B13+B14 and in B16 =B15/B3: ₹32.31 per km.

Step 3. Cab cost

  1. In E6:E8 type fare per km, trips per month and booking/waiting per trip.
  2. In E9 type =B3*E6+E7*E8: ₹17,200. In E10 type =E9/B3.

Step 4. Which is cheaper

  1. In E13 type =IF(E9<B15,"Cab","Own car") and in E14 =ABS(B15-E9): ₹8,650.

Step 5. Break-even km

  1. Car cost = fixed + km × (fuel price ÷ mileage). Cab cost = trips × booking + km × fare. Set them equal and solve for km.
  2. In E15 type =(B14-E7*E8)/(E6-B6/B7): (20,250 − 1,200) ÷ (20 − 7) = 1,465 km.
  3. In E16 type =(B14-B11-E7*E8)/(E6-B6/B7): about 542 km once the EMI ends (formula basics).

Step 6. Table and chart

  1. In A19:A23 type 400, 800, 1,200, 1,600, 2,000 km. In B19 type =A19/$B$7*$B$6+$B$14, in C19 =A19*$E$6+$E$7*$E$8 and in D19 =IF(C19<B19,"Cab","Own car") (mixed references).
  2. Select A18:C23 > Insert > Line: the lines cross at the break-even (line charts).

Ravindra Bagale's Tip

Use your own last three months of fuel bills and cab rides, not guesses. Real numbers settle the argument.

3. Formulas used

Cell Formula What it does
B13 =B3/B7*B6 Fuel per month
B14 =B8/12+B9/12+B10+B11+B12/12 Fixed costs per month
B15 =B13+B14 Car cost per month
B16 =B15/B3 Car cost per km
E9 =B3*E6+E7*E8 Cab cost per month
E13 =IF(E9<B15,"Cab","Own car") Cheaper this month
E15 =(B14-E7*E8)/(E6-B6/B7) Break-even km
E16 =(B14-B11-E7*E8)/(E6-B6/B7) Break-even without the loan
B21 =A21/$B$7*$B$6+$B$14 Own car at 1,200 km
D22 =IF(C22<B22,"Cab","Own car") Cheaper at 1,600 km

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

  1. Counting only fuel. Fuel was ₹5,600 of ₹25,850. Insurance, service, parking, EMI and lost value are real costs too.
  2. Forgetting value lost. A car is worth less every year. Leaving that out makes the car look cheaper than it is.
  3. Yearly costs not divided by 12. Insurance per year must be ÷ 12 in a monthly comparison.
  4. Ignoring per-trip cab charges. Booking, waiting and surge add up. Include a per-trip amount.
  5. Money is not the only factor. Time, comfort, late-night safety and family trips matter too. The sheet answers only the money question.

Ravindra Bagale's Tip

Look at the table, not just today's km. If your commute changes next year, the cheaper option may change too.

5. More real examples

Example 1: Two-wheeler vs auto. The same layout with bike mileage and auto fares.

Example 2: Second car for the home. Compare the extra car's costs with cabs for the trips it would replace.

Example 3: Moving to a new city. Change km per month for the new commute and see if the car still makes sense.

Quick revision cards

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

Revision card 1 for Own Car vs Cab Cost in Excel: Fuel per month: =B3/B7*B6 gives ₹5,600; Fixed costs per month: =B8/12+B9/12+B10+B11+B12/12 gives ₹20,250; Car cost per month: =B13+B14 gives ₹25,850.
Card 1 of 2: Own Car vs Cab Cost in Excel · Download PNG
Revision card 2 for Own Car vs Cab Cost in Excel: Car cost per km: =B15/B3 gives ₹32.31; Cab cost per month: =B3*E6+E7*E8 gives ₹17,200; Cheaper this month: =IF(E9<B15,"Cab","Own car") gives Cab.
Card 2 of 2: Own Car vs Cab Cost in Excel · Download PNG

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I compare owning a car with cabs in Excel?

Add up every car cost per month (fuel + fixed costs), work out the cab cost for the same km, and compare.

How do I calculate car cost per km?

Monthly car cost ÷ km per month: =B15/B3.

What is the break-even distance?

The km per month at which both cost the same: (car fixed − cab per-trip costs) ÷ (cab fare per km − fuel cost per km).

Should I include depreciation?

Yes. The value the car loses each year is a real cost even though you do not pay it monthly.

Why does the answer change when the loan ends?

The EMI is a large fixed cost; without it the break-even distance drops a lot.

7. Quick quiz

Ten questions on this page. Sign in to save your score.

Loading practice tasks...

  1. Total nights (Easy): Add up Nights in D2:D11. (sheet "Data")
  2. Average rate per night (Easy): Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Standard (Easy): How many rows have Room type "Standard"? (sheet "Data")
  4. Largest nights (Easy): The largest value in D2:D11. (sheet "Data")
  5. Days taken (Easy): C2 minus B2. (sheet "Dates")
  6. Start year (Easy): Year of B3. (sheet "Dates")
  7. Due month (Easy): Month number of C3. (sheet "Dates")
  8. Earliest start (Easy): Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Nights for Standard (Medium): Total of D2:D11 where B is "Standard". (sheet "Data")
  10. Standard in Manchester (Medium): Total of D2:D11 where B is "Standard" and C is "Manchester". (sheet "Data")
  11. Working days (Medium): Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due (Medium): Weekday number of C4 (Sunday = 1). (sheet "Dates")

See all practice tasks in the Task Hub →