Car+Analysis

‍ CAR ANALYSIS

Monica Cofresi December 12, 2012

Excel has been an excellent tool to find a variety of calculations from the simplest calculations to the most complex calculations. I have posted a simple but powerful calculation that can be used by everyone who owns a car or is thinking of buying a new car. This simple calculation will compare prices between different types of cars and will show the difference in cost between the car you currently owned against the new car you want to acquire. It will also make known how much the monthly payments will be; as well as, how much each car will spend in gas, and insurance. This is a great advantage because gives people a lot of options to choose from.

This information will be useful for people when looking to buy a car. This Excel’s calculation will give the confidence necessary to find the best deal. It will also show exactly how much each car will cost per year. I personally bought a car three years ago and, I did not know how to make this calculation at that time. So, I did not know how much I will spend for gas or insurance per year. It would it be advantageous to know this information before I bought it to compare prices. For this reason and with this statement on mind, I am posting this calculation for all people to see that they have a variety of options when they are considering buying a car.

I have chosen the most common cars to compare prices. Toyota, Honda, Ford, Nissan and Hyundai are some of them. I will compare miles per gallon, interest rate, monthly payments, insurance payment and so forth. Next, I would explain step by step the procedure to obtain these results.

The Excel table will start with some basic information that you need, first at the beginning of the table (row B3) enter the estimated miles that you think you would drive or drove per year, next row (B4) enter the current gas price, next row (B5) enter the car insurance percent.

After you enter all this information excel will allow you to enter formulas or functions in any cell where you want to do the calculations- in this case- to find the cost for gas, monthly payments, and insurance for each car. The calculations will begin with the monthly payments of the car (column B). The following information is needed to enter the formula/function, the price of the car, the interest rate, the terms (this means the time or years that you will make payments for the car) and if a down payment or a trade-in is given at the time of the deal it will be included in the calculation. -For the purposes of this calculation I included down payment/trade-in for all the cars-.

So, with all this information excel makes easy the calculations; but, before I continue I will make know that when you type an equal sign (=) in the formula bar, excel recognizes that you want to enter a formula or a function. So, place the cursor in the cell that you want to enter the function/formula type equal sign = on the formula bar and go to formulas tab on the ribbon click on financial and choose PMT (payment) option then a function argument window will appear, enter the car rate (interest rate of the car) divided by 12 (this number means the 12 months of the year) then NPER (terms or years the car loan is for), next is the present value of the car less down payment or trade-in if there is any. The above picture is showing the Formulas Tab in the ribbon as well as the formula bar (//fx//).

Excel gives the option to enter the words or to reference the cell number in the formulas or functions. If you want to enter the words you have to go to the name box cell (located at the left upper side of the worksheet) enter the name of the cell and then refer the name cell in the formula.  Excel Name Box Navigation

So the function can be enter this way

=ABS(PMT(rate/12,NPER,(selling price–down payment)) or =ABS(PMT(B22/12,B23(B20-B21)).

ABS means absolute value we include this to make the amount positive; otherwise, the calculation will give a negative result. Then next calculation is to how to find the monthly insurance payments and the formula is

=monthly payment*insurance percent or = B9*$B$5

When you enter the information excel will automatically shows the amount that you will pay every month. The dollar sign around cell $B$5 is an absolute value meaning that the value of this cell will not change if you want to copy the formula to another cell.

The next column is the yearly insurance column and the formula is

=monthly insurance*12 or =D9*12

Next calculation is the yearly cost of gas and the formula is

=miles driven per year/by miles per gallon*price of gas or =$B$3/C9*$B$4

Next calculation is the monthly cost of gas and the formula is =gas per year/by 12 or =G9/12

The last cell shows the total cost of the car per year. This is the sum of all the cost and the formula is

=yearly gas+yearly insurance+monthly price*by 12 or =G9+E9+B9/12

Another great feature that excel has is that you can change the price of the car, the amount of the down payment, the interest rate, the miles driven per year, the price of the gas, or the insurance rate than excel will automatically update all the formulas/functions to the new amount. If you want to try it, please do so, change any amount above mentioned and you will see that the answer will automatically update to the new amount. All these simple steps will help everybody calculate the different cost of cars before they make a decision to buy a car.‍