Financing Your Car

      Now that you have the prices for your car, let’s calculate what it would cost to finance your car. Take a new car and a used car price and put them onto separate lines of a worksheet. While you could try to get the rates from the credit union or the bank, the actual rate will depend on your credit rating, which means they can only give you the correct rate if you apply. For comparison purposes, get your rate from Bankrate.com (http://www.bankrate.com/yho/rate/auto_home.asp)  At Bankrate you will discover something: rates vary depending upon the length of the loan and whether the car is new or used. You will need to set up a spreadsheet to reflect the different rates. You can get new car loans, typically, in ranges from 36 to 60 months. On the other hand, used car loans  are usually for no more than 36 months and the rate is usually higher than for a new loan.

      To set up your spreadsheet to handle the different rates, combine cells by doing a right click on the cell and merging cells (If you aren’t using Excel, look at your Help menu and search on “merge cell”. Click the image to see and example of what the merge window looks like in Excel:

 

Enter the rates as decimals and then go to Format Cells and change the number to percentage. You will be doing calculations with these numbers so they need to be decimals. Click the image to see a page that shows what the number formatting looks like:

With the rate, period of the loan and the amount financed, you have the basics that are needed to calculate monthly payments and total cost of financing. If you are not familiar with Excel, you might want to look at a tutorial on formulas and functions. Here’s a tutorial from USC: http://www.usc.edu/isd/doc/productivity/excel/formula_function.html

You may remember from your elementary math that the formula for interest is I=prt, or Interest = Principal x Rate x Time. Since the rate is an annual one, you will need to divide it by 12 to get the rate for the formula. Here’s how you might write it for a $15,000 loan at 7% for 48 months:

·        Interest=15,000 x (.07/48) x 48.

·        To enter it into Excel, you will put an = sign into the entry field (you can click on the = sign in the newer versions of Excel), and then write in the others, like this: =15000*(.07/48)*48.

·        The answer would be the amount of interest that you would pay on the loan.

·        Then if you add that to the original amount, and divide the total by  48, you have your monthly payment.

To make you do all of that would be denying you the power of the spreadsheet.

           What do we mean?

First, Set up your spreadsheet so that it has columns and rows with labels similar to the example you will find on the page linked to this image: (You may actually think of a better way to organize the information; this is just a suggestion that works)

 

           Since the new price is the same for all of the new cars, you can use the Fill command. Let’s say the new price is $15,000. Put 15000 in the first box. Then go to Edit, select Fill, and select Right, as shown on the page linked to this image :

 

The $15,000 fills across all of the fields. The used car fields can be entered manually, writing over the $12,000. Fill is a very powerful feature. You can not only fill the same number, you can fill formulas and dates. Read more about Fill in the Help menu or in the tutorials listed at the beginning of the lessons.

You may find that you are scrolling off the screen when you try to input the data. You could go to the view and choose a smaller percentage, but if your spreadsheet got much larger, you’d need a magnifying glass.

Freezing the pane is a better solution.

·        Click in the upper right cell that you are entering data in with the labels that you want visible. In the example above, it would be B3

·        Go to the Windows menu and select Freeze Pane. Now the rows above and to the left stay visible as you move around the spreadsheet.

Click on the image to see a page that shows what  the freezing the panes looks like:

After freezing the pane, enter all of the other information you have. For the down payment, we’ll use $2000. To arrive at the amount financed, use the formula =B3-B6. You can click on the formula to enter the cell references or enter the numbers by hand. Hit “Enter” to finish placing the formula in the cell.

Once you have one formula entered, you can use Fill to put the formula in all of the other cells. For example, here’s the way we entered the formula to calculate the monthly payment:

=((B8*B4*B6)+B8)/B5 , as shown on the page attached to this image:

 

First we put in the = to indicate a formula was being entered. We put in parentheses to make sure that everything was properly multiplied and divided. B8 is the principal (Amount financed in  the previous pane) multiplied by the rate (B4) and the time in years (B6). That formula gives us the total paid in interest. To figure out the monthly payments, we need to add the principal to the interest and then divide the sum by the number of months (B5). Once the formula is in the first column cell, we can do the Fill Right command and save entering all of those numbers again and again!

To calculate the total amount you would pay for each car with a 36, 48, or 60-month loan using the following formula:

A. Find interest paid: Interest=(rate)(time in years)(original amount)

B. Total amount paid=Total amount of car + Total interest paid

           Finish filling out the spreadsheet, using the Total Cost of Ownership amounts for maintenance, the monthly insurance amounts that you found in your insurance research, and gas based on the average cost of gas now and the amount you’d probably drive. To add together these costs, use the Sum function. Place your cursor in the square where you are entering the monthly totals, then click on the fX beside the data entry window. (Click the image to see an example:

After you select Sum by clicking on it and hitting OK, you will have to define the area that you are totaling. The default is the entire column, which won’t work because you have percents, years, months, and other items that are not the same. Drag your cursor over the cells that you want to include and then click OK.(Click the image for a page showing an example:

Once you have monthly totals, print out two sheets: one of formulas and the other with the amounts as shown. Printing the sheet as you see it is no problem; go to File and select Print-Active Area. To print the formulas, go to Tools-Formula Auditing- select Formula Auditing Mode, as shown on the page linked to this image:

(Notice the shortcut next to the Formula Auditing Mode! You could do this simply by holding down the Control key and +. )

      Choose landscape mode in Page Setup and print the Formula Auditing Mode. Return and make a bar or line graph comparing the costs.