Friday, May 9, 2014

The Calculation Spreadsheet

In order to figure out how much money I'll need to save and invest in order to have a passive income stream that covers my car payment, we need to do a bunch of calculations and there's no better place for doing a bunch of calculations than on a spreadsheet! (But don't worry. As I promised last time, there's lots of little bunnies in this post to soothe you.) Being the nice guy that I am, I've made a spreadsheet just for this task and have shared it as a Google doc. You can view a read-only copy here, but here's a screenshot:




Let's look at what this shows. First, the blue cells are data that you enter. Car price and down payment should be self-explanatory. Cell D5 is the resale value of my current car, which I will be selling when I get my new Tesla. Cell D7 is where I enter how many years I want to take to save up the down payment, which is used to calculate how much I need to save per month and per week.

Cells B15 though B17 are the various tax credits that are available to buyers of all electric cars. The Federal tax credit of $7,500 is only available until Tesla has sold a certain number of cars. In truth, by the time I buy my Telsa, this credit will likely no longer be available. The next two credits are state tax credits that Arizona, where I live, offers. Currently, Arizona has a reduced car licensing fee of $25 per year for electric vehicles. On an expensive luxury car like the Tesla, this represents a significant savings, as the tax is normally based on a percentage of the value of the car. I figure that over 5 years, this will save me $4,800. (Arizona lets you pay vehicle tax 5 years in advance, so I can license the car for 5 years for $125 total.) The last tax credit is a $75 credit that the state provides to people who install an electric vehicle charging station at their house. Note this doesn't have to be available to the public. It can be in your garage and for your use only.

Don't hide, little bunny. The math isn't that bad.
The values in cells B21 and B22 are figures for the auto loan you will be getting. The interest rate I used might be slightly lower than what someone else might be able to obtain because I work for a credit union and get an employee discount. The values in these cells are used to calculate the monthly loan payment figure given in cell B25. This calculation assumes you will be putting all the money you get from selling your old car (D5) towards the purchase of the Tesla.

Moving back to the top right portion of the spreadsheet, you'll see cells G3 and G4, where you can enter your yearly gasoline and oil change costs. I've been tracking my expenses using mint.com for some time now, so these are my actual expenditures for 2013. My current car is a Prius, which gets between 48 and 52 miles per gallon, so my gas expenses are probably lower than someone who has a gas-only car. The oil change cost shown actually includes all service-related costs, not just oil changes. This is a valid value to use in the calculations because not only does the Tesla not need oil changes, it also does not need tune-ups, spark plugs, air filters, timing belts, etc. Basically the only service costs a Tesla will have are the costs for tire rotation and balancing. And most places that sell tires will provide free rotations.

Cell G8 was used in the past to include the "Ranger Service" option from Tesla, which was basically a roadside assistance plan on steriods. If anything went wrong with the car, they would come out to you, give you a loaner vehicle, and take your car back to the shop for repairs and repair it for free. This used to cost $600 per year, but it is now included in the purchase price of the Model S.

Cell G9 is the cost for the electricity used to charge the car. This is based on $5 per 230 miles and 15,000 miles per year, which is what was referenced in the web article that the text in the spreadsheet links to. This does not include the free charges you can get on trips by using Tesla's network of SuperCharger stations. Use of this is free with the purchase of the car (or at least, with the version of the car I want), so in actuality, my electricity cost might be lower.

Cells G11 and G12 take gas and oil costs and the electricity costs and show you how much money you will save per year and per month by switching to a Tesla.
Almost done, I promise!

Cell G18 is where we enter the rate of return of our passive investment. Using this, the spreadsheet calculates how much money we need to invest at this interest rate to generate a monthly return equal to the loan monthly payment in cell B25. This amount is shown in cell G19.

And finally, the last two cells of the spreadsheet, show the total amount of money you need in order to buy the car. Cell G26 shows how much you need to buy the car using just a loan and no passive investment. In this case, I'd need just over $19,000. Cell G27 tells me that, in order to buy the car with only the downpayment and the money from selling my old car, I'd need $113,925. Of that, $94,571 (cell G19) needs to be invested at 9%, which will cover the auto loan payment. The remainder goes towards the purchase of the Tesla.

So this tells me that I actually need to save MORE than the price of the car in order to be able to purchase it and not have any payments. Some of you might say "Why not just save $96,770 and buy the car?" Remember what makes passive income so magical: once my car loan is paid off, I'll have both the car AND the $94,571 and it will still be creating income for me! So in effect, I'll have purchased a $100,000 car for only about $35,000 - my down payment and whatever I sell my old car for!
We made it!

0 comments:

Post a Comment