Cost justification modeling

Before I embark on any new adventure that may have a big impact on my family’s finances (i.e. new house, new car, new job, etc), I create a spreadsheet to determine if the idea makes financial sense or not.

I did this when I shopped for cars and needed to understand what each car’s impact on our budget would be. As a milestone, I used the car I had been driving, which was already paid off.

Here’s what the spreadsheet looked like: As you can see, I was trying to decide between a Nissan Leaf, Chevy Volt, Toyota Prius V, and a Mazda Miata Grand Touring Edition. I really, really wanted the Miata. As you can see, there was a fifth column for my 1999 Lexus ES300. It was paid off, so all I needed to enter for it was its MPG, oil change frequency/cost and brake service frequency/cost. That gave me the total cost of ownership of the Lexus (fuel, brakes and oil) of $242.33 per month. It’s important that you understand this concept. Even a car that’s paid for has continuing costs to keep it running.

The green cells in the spreadsheet are variables you need to input and they are labeled on the left side. Most are self-explanatory, but here are a few you way need to have explained:

• “kWh per 100 miles“: I got these figures from a government-run site. Once there, click on “Advanced Vehicles and Fuels” for electric and hybrids. I use this, rather than the MPGe value to calculate cost of ownership because I use the cost of electricity and gasoline to calculate costs. These fluctuate, with respect to one another, but I think they give a better evaluation of cost than using the BTUs in a gallon of gasoline to determine MPGe. Mainly this is because electric motors are much more efficient at turning power into motion than internal combustion engines.
• “Electric range in miles“: This is critical for pure electric vehicles. The value will turn red, if the “Miles per weekday” or “Miles per weekend day” exceeds the range of the vehicle. If you have a pure electric and this value turns red, you’ll be stranded, until you can recharge. I also use this value to determine how many miles are driven each day on electricity versus gasoline in the “Extended range hybrid” category.
• “(R)egular or (P)remium gas?“: Enter an R if the vehicle can use regular gasoline or P if it requires premium gasoline. (notice the purely electric vehicle has N/A as it does not use gasoline)
• “Fuel cost per year” is calculated for you, based on the type of vehicle and fuel(s) it uses (i.e. electricity, gas or both).
• The “Warranty” information row is just for evaluation/comparison.
• “Price” is the list price of the vehicle.
• “Trade-in + Down Payment” is the sum of these two items and are taken off the top of the amount financed for purchase or lease.
• “Lease incentives” are incentives that only apply to leases. Notice Nissan was running a $10,000 incentive for people who lease a Leaf! Again, this amount it taken off the amount financed for a lease.
• “Residual %” is the estimated value that the vehicle retains at the end of the lease term. If you haven’t leased before, or are unfamiliar with the term, it works like this: The amount you pay toward the lease is how much the car’s value is estimated to depreciate during the time you lease it. The Leaf and Volt had very high residual value (60%), so if I leased one of those two vehicles, I was financing only 40% of the price (100%-60% residual value). Notice that traditional hybrids and conventional vehicles have lower residuals. The higher residual of an electric vehicle is probably more of an incentive than actual value at the end of the lease. Ah, the games we play…
• “Length of lease in months” and “Lease interest rate” are fairly obvious. The interest rate is the annual rate.
• The “Monthly lease payment” is calculated for you, using the values already entered, as well as and tax incentives offered by federal, state or city governmental programs (that will come up later)
• The “Loan term in months” and “Purchase interest rate” are the same as on the lease, with the exception that you’re purchasing the vehicle, rather than leasing it.
• The next section contains maintenance costs. The “Oil change frequency” and “Brake service frequency” is the recommended distance in miles that you expect to travel before having these items serviced. Notice that the Leaf, which has no gasoline-powered engine, does not require oil changes. Also, the brake service frequency is much longer on the Leaf and Volt because the braking systems use a portion of the energy generated, slowing that car down, to recharge the battery. In fact, I have seen my “miles remaining” in my Volt count up, instead of down, when in stop and go traffic. Traditional vehicles convert the energy of motion into heat and consequently require brake service more often. In the case of the Volt, the “Oil change frequency” is dictated by the gasoline-powered miles only. This is because, if the gas engine isn’t running, the miles traveled should not count as if the gas engine is running. This ratio is calculated by the spreadsheet, based on an estimated one battery charge per day.
• The “Oil change cost” and “Brake service cost” values were just estimates, and may actually be higher for the Leaf and Volt, due to the advanced nature of the braking/recharge system.
• The “Monthly cost of ownership” is then a combination of lease or purchase monthly payments, fuels/electricity usage and maintenance (brakes/oil). This is a true representation of the comparative monthly costs of the vehicle types being evaluated. (I did not include insurance, inspection or registration costs, but probably should have) One thing to notice here: the Volt only increased my expenditures by $156.17 per month, compared to the old car I had already paid off! ($398.50 – $242.33 = $156.17) Not bad for a $40K car!
• The “Inputs” section are constants used for the calculations.
• “Electricity $ per kWh” can be obtained from your electric bill and this spreadsheet assumes you’ll be charging at home. The cost of charging at a commercial site, if not free, is much higher than at your residence. I wrote about that in an earlier blog post.
• “Gasoline cost per gallon” has entries for both “Regular” and “Premium” because each car may have different requirements.
• “Miles driven per year” is either the miles you signed up for on your lease or an estimate of how many miles you will drive the vehicle.
• “Miles per weekday” is entered by you and was selected because most people know the number of miles in their daily commute but not how many they drive on a weekend.
• “Miles per weekend day” is calculated by subtracting 52 weeks of daily commute miles from the annual miles and dividing by 52 weeks X 2 weekend days. I know you don’t drive the same number of miles every weekend day, but we had to estimate this somehow! Again, this value will turn red if it is a negative number. A negative value in this field means you don’t have enough miles on your lease for your weekday commute, much less any left over for the weekend, or you are driving more miles per year than you estimated.

If you want to compare multiple cars of the same type (say a Leaf versus a Tesla Model S, or a Volt versus a Fisker Karma) you just need to insert a new column and copy and paste the column that already exists for that type of vehicle into the new column. The cell references float when they should and are locked when the should be.

Both Numbers and Excel versions of the spreadsheet are available for download. Just click on the link for the file type.