D43: Car Total Cost of Ownership

Alice is considering buying a new car and is evaluating three options: two gasoline cars and an electric vehicle (EV). Basic information about the cars is given in the table below, where kWh is kilowatt hours of electricity:

Alice plans to use the car for 7 years (years 1-7) and drive it about 10,000 miles per year. She expects maintenance to cost 2% of the car’s purchase price each year (1-7) for the gas cars but expects it will be only only 1% each year for the EV. At the end of year 7 she expects to sell the car for 20% of its original purchase price.

In terms of fuel costs, she knows future electricity prices will be constant but is concerned about future gas prices. She'd like to know total present value cost of owning (TCO) the car under three fuel price scenarios, L, M and H using an interest rate of 5%. Note that the price of gas differs in the scenarios but the price of electricity is constant.

Step 1

Complete the paper part of the exercise and have it checked to make sure your formulas are correct.

Step 2

Add data and formulas as needed to the spreadsheet template in Teams down through Total Cost of Ownership by Scenario section. The result will be a tool that lets you evaluate the TCO of any given car. Remember that the resale value of the car reduces the TCO.

Step 3

For each car, fill in the price, miles per fuel unit, maintenance %, and fuel price cells. Then copy the Total row in the TCO section to the Analysis section and save it using paste values. 

Step 4

Once you've computed the data for all three cars, draw a bar chart of the data in the Analysis section and fill in the Best? row with the the car that has the lowest TCO.

Save and submit the results!