MATH MODELS WITH APPLICATIONS



MATH MODELS WITH APPLICATIONS NAME ______________________ HOUR __________

CAR BUYING PROJECT March 8, 2005 7/3/2005

OBJECTIVE: Accumulate enough information to make an intelligent and fiscally prudent vehicle buying decision.

GRADE: This project will contain 12 class work grades, which include answers to project research and Excel calculations. There will be 5 quizzes with this project and a test after project completion.

REPORT: The report will be organized as described under STEPS. There will be steps on vehicle selection, financing, amortization, depreciation, insurance, and leasing.

DUE: Unit test day, 10% penalty one day after, 20% two days after, and not accepted after that.

RESEARCH: This report can be completed using any combination of two sources of information: the traditional hard copy and the internet. The traditional method would include classified ads, calling insurance and financing sources, and visiting local vehicle dealers. Internet printouts cannot be used. This handout and your Excel file (name is CarBuying) will contain all required information. The term “vehicle” includes car, truck, SUV, and mini-van. It does not include motorcycles or specialty vehicles.

WORKING WITH OTHERS: This is encouraged if you are not that familiar with the internet or vehicle shopping. Working together is considered a privilege that will be canceled if it disturbs others or accessing non-related internet sites is observed. However, I expect you to come up with your own choices, rationale, report, and Excel file.

STEP 1: VEHICLE SELECTION: So many selections, so little time (and money)

1. Make a list of at least two new vehicles; two used (between one and eight years old) vehicles, and a dream vehicle of your choice. The first four choices must be what you can most realistically afford to buy in the next year. New vehicles will be limited to $50,000 retail price. Describe year, model, and option choices you would consider. You goal is to gather enough information to make an informed choice. If you already own a vehicle, use that vehicle as a choice to see if you did get the best possible value.

2. The objective is to compare the suggested retail price against the invoice or wholesale price. You will find at least three sources of vehicle pricing information for the first four choices. Only one is required for the fifth choice. Different web sites count as a source. For each new and used vehicle, find the retail (M.S.R.P.) and the wholesale (dealer invoice or trade-in) price. For your fifth choice, only one source is required. Calculate the mark-up and percent of markup. The comparisons will be for the vehicles most closely reflecting your choices. Note features, condition description, cost, and data source.

3. Fill out the table provided on the following page and construct one in Excel to perform the calculations.

4. You will use your time most efficiently if you know what vehicles you want to research. Remember, you are looking for retail (MSRP, sticker, asking, market, going, etc) and wholesale (invoice, dealer cost, loan, trade in, etc) prices. This is not a “beauty contest. For new and used vehicles, my recommendation would be , and . All sites contain retail and wholesale prices. Do not use web sites where you must give personal information, will have a dealer call, or information will be delivered later by mail. This will especially be the case with new vehicles. Do not go to websites that have actual cars for sale or manufacturer’s websites. They will not give you enough information. You will first do research so you are better informed when you are ready to go shopping. These web sites will give you pretty much the same wholesale and retail pricing information for new vehicles. They will give you only a general idea of what a used vehicle would be worth in the wholesale and retail markers because the price is dependent on condition. Notice the factors that decrease or increase a used vehicle’s worth. You will be better prepared to determine a vehicle’s true worth to you.

. For new vehicles, next to “New” select “Research: Reviews and Information.” For used vehicles, next to Used, “Select “Prices, Reviews, and Inventory.” Select “Research.”

. For new vehicles, select “New Car Pricing.” For used vehicles, select “Used Car Values.”

. For new vehicles, select “new Car Reviews, Spec & Pricing.” For used vehicles, select “Used Car Pricing.” Car and Driver now works with KBB so prices will probably be the same. However, Car and Driver has much better reviews.

What are your impressions of these web sites? _______________________________________________________

New Vehicle: year, name, model, and trim line: ____________________________________________________

____________________________________________________________________________________________

1.

Retail price: ______________________

Wholesale price: ______________________

2.

Retail price: ______________________

Wholesale price: ______________________

3.

Retail price: ______________________

Wholesale price: ______________________

New Vehicle: year, name, model, and trim line: ____________________________________________________

____________________________________________________________________________________________

1.

Retail price: ______________________

Wholesale price: ______________________

2.

Retail price: ______________________

Wholesale price: ______________________

3.

Retail price: ______________________

Wholesale price: ______________________

Used Vehicle: year, name, model, and trim line: __________________________________________________

____________________________________________________________________________________________

1.

Retail price: ______________________

Wholesale price: ______________________

2.

Retail price: ______________________

Wholesale price: ______________________

3.

Retail price: ______________________

Wholesale price: ______________________

Used Vehicle: year, name, model, and trim line: ___________________________________________________

____________________________________________________________________________________________

1.

Retail price: ______________________

Wholesale price: ______________________

2.

Retail price: ______________________

Wholesale price: ______________________

3.

Retail price: ______________________

Wholesale price: ______________________

Dream Vehicle: year, name, model, and trim line: __________________________________________________

____________________________________________________________________________________________

Website: ____________________________________________

Retail price: ______________________

Wholesale price: ______________________

STEP 1 – Continued

Set up a table as follows, compute, and save on your diskette with a name - Vehicle:

1. Markup: difference between wholesale and retail cost.

2. Percent markup: markup divided by the wholesale cost. Calculate this to the nearest tenth of a percent.

Your MARK-UP AND PERCENT OF CHANGE spreadsheet should look like this:

|MARK-UP AND PERCENT OF CHANGE | | | | |

| | | | | | | |

| VEHICLE SELECTION |WHOLESALE |RETAIL |MARK-UP |PERCENT |

| |COST |COST | |MARK-UP |

|1. ECONOBUCKET | $ 15,235.00 | $17,852.00 | $2,617.00 |17.2% | |

|2. ECONOBUCKET | $ 16,203.00 | $17,528.00 | $1,325.00 |8.2% | |

|3. ECONOBUCKET | $ 15,897.00 | $16,852.00 | $ 955.00 |6.0% | |

|1. FLASHLIGHTENING | $ 26,523.00 | $29,852.00 | $3,329.00 |12.6% | |

|2. FLASHLIGHTENING | $ 27,852.00 | $28,457.00 | $ 605.00 |2.2% | |

|3. FLASHLIGHTENING | $ 28,900.00 | $30,258.00 | $1,358.00 |4.7% | |

|1. RUSTIE | | $ 11,500.00 | $12,451.00 | $ 951.00 |8.3% | |

|2. RUSTIE | | $ 11,750.00 | $12,587.00 | $ 837.00 |7.1% | |

|3. RUSTIE | | $ 11,350.00 | $12,985.00 | $1,635.00 |14.4% | |

|1. AFFORDABLE | $ 6,528.00 | $ 7,512.00 | $ 984.00 |15.1% | |

|2. AFFORDABLE | $ 6,289.00 | $ 6,852.00 | $ 563.00 |9.0% | |

|3. AFFORDABLE | $ 6,487.00 | $ 6,625.00 | $ 138.00 |2.1% | |

|SUPER SLUSH | $ 46,230.00 | $52,148.00 | $5,918.00 |12.8% | |

Of all 12 pricings, which of your vehicles has the greatest percent of mark-up? ________________

Of all 12 pricings, which of your vehicles had the least percent of mark-up? _________________

What is the retail cost of your final selection for each type vehicle?

NEW #1 ______________ NEW #2 ________________ USED #1 ______________ USED #2______________

STEP 2: FINANCING: How Much Will This Vehicle Cost You?

You will find the type vehicle, where the vehicle is purchased, how long financed, and your credit rating affects the interest rate. The best 36, 48 and 60-month rates for new and used vehicles will be used in the financing table on the next page.

1. Under “Vehicle Services” in the right column, select “Bad Credit Help.” Record the rates below for Excellent Credit, new and used cars. Why does the length of your loan and credit rating affect the interest rate?

New: APR ________ Months 36 APR ________ Months 60

Used: APR ________ Months 36 APR ________ Months 60

Comments: ______________________________________________________________________________________

________________________________________________________________________________________________

2. Select “Check loan Rates.” Record the interest rates below for Deal Purchased New and Dealer Purchased Used. Why are the interest rates are higher for person-to-person financing and refinancing than dealer purchase financing?

New: APR ________ Months 30 to 36 ; APR ________ Months 37 to 60; APR ________ Months 61 to 72

Used: APR ________ Months 30 to 36 ; APR ________ Months 37 to 60; APR ________ Months 61 to 72

Comments: _____________________________________________________________________________________

_______________________________________________________________________________________________

Create a spreadsheet for the cost of your choices that resembles the one below. You make the choice for each selection. The following is how the spreadsheet should look for each of your final selections. Calculations are described below.

|FINANCING | | | | | | | | |

|VEHICLE |RETAIL |DOWN |LOAN |INT |# MONTHS |MONTHLY |TOTAL |COST OF |

|SELECTION |COST |PAYMENT |AMOUNT |RATE |OF LOAN |PAYMENT |COST |FINANCING |

|3.ECON | $16,852.00 | $ 1,685.20 | $15,166.80 |8.25% |36 |$477.02 | $ 18,858.05 | $ 2,006.05 |

|3.ECON | $16,852.00 | $ 1,685.20 | $15,166.80 |8.5% |48 |$373.84 | $ 19,629.32 | $ 2,777.32 |

|3.ECON | $16,852.00 | $ 1,685.20 | $15,166.80 |8.75% |60 |$313.00 | $ 20,465.25 | $ 3,613.25 |

|2. FLASH | $28,457.00 | $ 2,845.70 | $25,611.30 |8.25% |36 |$805.52 | $ 31,844.50 | $ 3,387.50 |

|2. FLASH | $28,457.00 | $ 2,845.70 | $25,611.30 |8.5% |48 |$631.28 | $ 33,146.90 | $ 4,689.90 |

|2. FLASH | $28,457.00 | $ 2,845.70 | $25,611.30 |8.75% |60 |$528.55 | $ 34,558.48 | $ 6,101.48 |

|1. RUSTIE | $12,451.00 | $ 1,245.10 | $11,205.90 |9.5% |36 |$358.96 | $ 14,167.59 | $ 1,716.59 |

|1. RUSTIE | $12,451.00 | $ 1,245.10 | $11,205.90 |9.75% |48 |$282.87 | $ 14,822.72 | $ 2,371.72 |

|1. RUSTIE | $12,451.00 | $ 1,245.10 | $11,205.90 |10.0% |60 |$238.09 | $ 15,530.64 | $ 3,079.64 |

|3. AFFORD | $ 6,625.00 | $ 662.50 | $ 5,962.50 |9.5% |36 |$191.00 | $ 7,538.37 | $ 913.37 |

|3. AFFORD | $ 6,625.00 | $ 662.50 | $ 5,962.50 |9.75% |48 |$150.51 | $ 7,886.96 | $ 1,261.96 |

|3. AFFORD | $ 6,625.00 | $ 662.50 | $ 5,962.50 |10.0% |60 |$126.69 | $ 8,263.63 | $ 1,638.63 |

|SLUSH | $52,148.00 | $ 5,214.80 | $46,933.20 |8.25% |36 |$1,476.13 | $ 58,355.65 | $ 6,207.65 |

|SLUSH | $52,148.00 | $ 5,214.80 | $46,933.20 |8.5% |48 |$1,156.82 | $ 60,742.34 | $ 8,594.34 |

|SLUSH | $52,148.00 | $ 5,214.80 | $46,933.20 |8.75% |60 |968.57 | $ 63,329.08 | $ 11,181.08 |

The retail cost is what you would pay for the vehicle.

The 10% down payment is 10% of the retail cost.

The loan amount is the retail cost minus the down payment.

The interest rate is the best you found of three sources as documented on the previous page.

Use the PMT function for the monthly payment.

Total Cost is the down payment plus the monthly payments times the number of payment.

Cost of Financing is what you pay for the having to borrow money. It is equal to the monthly payment times the number of monthly payments minus the loan amount.

1. What happens when you compare the monthly payments of each selection to the # months of loan?_______________

___________________________________________Why? ________________________________________________.

2. What happens when you compare the total cost of each selection to the # months of loan? ______________________

____________________________________________Why?________________________________________________.

3. What happens when you compare the cost of financing of each selection to the # months of loan? ________________

_____________________________________________Why? ______________________________________________.

4. What happens when you compare the cost of financing of each selection to the total cost? ______________________

_____________________________________________Why? ______________________________________________.

5. Which of your five vehicles would be the best deal given the options, mileage, interest rate, payments, etc. _______

Why? ___________________________________________________________________________________________.

6. Which of your five vehicles could be the worst deal given the options, mileage, interest rate, payments, etc. ________

Why? ___________________________________________________________________________________________.

STEP 3 – AUTO INSURANCE, CLEAR TITLE, AND SAFETY

Know your parent’s vehicle insurance company and bring your vehicle’s Vehicle Identification Number (VIN) number to class. It is on your vehicle registration and on your left dashboard next to the windshield.

Basic Insurance Coverages - More information can be found in to the site below.

Bodily Injury (BI) Liability: protection against claims or lawsuits due to injuries of others.

Property Damage (PD) Liability: coverage of property damage caused by your vehicle.

Medical Payment (MP): covers medical bills of the driver and other persons in the driver’s vehicle.

Collision: Covers damage done to the driver’s vehicle.

Uninsured/Underinsured Motorists Coverage (UM/UIM): covers other drivers who are under or not insured

Comprehensive Physical Damage: covers damage to the driver’s vehicle when not moving.

Texas state law requires each driver to carry BI and PD liability coverage so the other people and property you damage have basic coverage. Your medical, collision, UM/UIM are optional. For BI you must have $20,000 minimum per person and $40,000 minimum per accident. For PD it is $15,000 per accident.

Insurance rates also depend on your vehicle safety, how easy it is to damage, cost to repair, and susceptibility to theft. You will research for four of your vehicle choices and summarize the results. Not all information will be available on all choices. Go . In the right column, under “More Help & Advice – Your Car,” select “Auto insurance basics. “ In the left column, select “Interactive Tools” and the “Car Crash Performance.“ Use “Show me this model.” Start with your first vehicle. Information available may be different for each vehicle, especially the 2005. If so, use the 2004 model. Generally your titles will be Crashworthiness and Injury; Death, Injury, and Collision, and Theft; Average Damage Repair Cost; Comprehensive and Collision Insurance Costs; and Head Restraint Ratings. Click on the title to see how they are measured. Summarize your findings. Would this affect your choice of vehicle? Scroll down to enter another vehicle.

Vehicle __________________ Summary and Conclusion: _______________________________________________.

_______________________________________________________________________________________________

_______________________________________________________________________________________________

Vehicle __________________ Summary and Conclusion: _______________________________________________.

_______________________________________________________________________________________________

_______________________________________________________________________________________________

Vehicle __________________ Summary and Conclusion: _______________________________________________.

_______________________________________________________________________________________________

_______________________________________________________________________________________________

Vehicle __________________ Summary and Conclusion: _______________________________________________.

_______________________________________________________________________________________________

_______________________________________________________________________________________________

Auto Insurance Companies. They are not all the same. Go to . Under “Insurance Shopping Tools” in the left column, select the “Insurance Company Guide.” tab. Select “Auto” and doing business in Texas. Look up your or your parent’s vehicle insurance company. What is the Standard and Poor’s rating? _____________________ Click on the rating. What does that rating mean? ______________________________ Select other links to learn more about ratings and your company. What have you learned about your company? _______

________________________________________________________________________________________________

Why are the ratings important? _______________________________________________________________________

________________________________________________________________________________________________

Title Laundering. You’ll need the Vehicle Identification Number (VIN), which can be found on the dashboard or door. A used vehicle will be more interesting. This search is important everywhere, but particularly in states with notably lax lemon laws, such as Texas and many states in the southeast, where title laundering is particularly prevalent. Go to . Enter the VIN and your ZIP for their FREE CARFAX Record Check. You should get the legal description of your vehicle. Read what it says about your vehicle and the reports available. Select “Sample Reports” at the top of the page and read what information can be retrieved. What does say about your vehicle? _______________________________________________________________________________________________

In what buying situation would this be most important? __________________________________________________

_______________________________________________________________________________________________

Insurance Quotation: Go to . The objective is to determine which factors will increase or decrease your insurance premiums. Input your ZIP code and “Yes,” “Yes,” “No.” Use a reasonable car, go through the questions and answer process and write the factors below as you recognize them. Consider yourself single to simplify your inputs. Working through the all the questions will result in an insurance company wanting to e-mail or mail you a quote. Do not provide that information. Insurance is a “risky business” and the more risk, the higher your premium. Use $250 deductible for all deductibles. Select the Texas minimums $20,000/40,000 for BI coverage, $15,000 for PD coverage. Reject the uninsured/under insured BI motorist. Reject the uninsured/under insured PD motorist. Reject the Personal Injury Protection. Select “No coverage” for Medical Payment. Select “No coverage” for Towing & Labor Coverage. Select “No coverage” for Rental Reimbursement Coverage.

Items that increase your insurance premiums: 1. __________________________2. __________________________

3. ______________________________4. _____________________________5. _____________________________

6. ______________________________7. _____________________________8. _____________________________.

Items that decrease your insurance premiums: 1. _________________________2. _________________________

3. ______________________________4. _____________________________5. _____________________________

6. ______________________________7. _____________________________8. _____________________________.

Car Safety: your vehicle insurance rates are effective by crash-test data. Visit the National Highway Traffic Safety Administration site at . Under “NHTSA Quick Links,” Select “Crash Tests and Rollover Ratings.” Option 2 will give you more results. Enter your data for any of your new or used vehicles to get a report. Use 2004crash-test data if 2005 crash-test data is not available. What did you find? _____________________________________________________________________________________________

______________________________________________________________________________________________

Do you think that this data would influence your selection? _______ Why? _________________________________

_____________________________________________________________________________________________

STEP 4 – AMORTIZATION, DEPRECIATION, AND EQUITY: How much is the vehicle worth?

Amortization means that you make equal payments over a specified period of time to pay off the loan.

Each monthly payment consists of interest due and note (loan) reduction. Interest due is equal to the unpaid balance multiplied by the monthly (annual rate divided by 12) interest rate. Note reduction is the monthly payment minus the monthly interest.

Depreciation is the loss in value of a possession over time. Its opposite, appreciation is the gain in value of a possession over time, which rarely happens when owning a vehicle.

Equity is your share in the value of a possession. It is equal to the fair market price (depreciated value) minus what you still own on the possession. The finance company owns the rest as long as you are making payments.

You will build an Excel table to show how monthly interest due, note reduction, amortization, depreciation, and equity are related. The column headings will look like the following. Use Alt + Enter after the top word to put the title in one cell. You data will use a new vehicle or your dream vehicle that was financed for 36 months.

DEPRECIATION AND EQUITY

|MONTH |MONTHLY |INTEREST |NOTE |UNPAID |DEPRECIATED |EQUITY |

|NUMBER |PAYMENT |DUE |REDUCTION |BALANCE |VALUE | |

Month Number: start with 0 (time you bought vehicle) and increase it by 1 up to 120 months for the depreciation calculations. Make an equation and drag it down to 120.

Monthly Payment: This is your monthly payment for your new vehicle or dream vehicle choice financed for 36 months. To make this number interactive with the previous table, make an equation and select the payment amount from your previous table. Use a dollar sign so the value does not change as you drag it from month 1 to month 36. (36 payments). Example =G$103

Interest Due: To make this interactive with the previous table, make an equation and select the Loan Amount from the previous table in Unpaid Balance for month 0. This is what you must borrow. Under Interest Due for month 1, the interest due will be the Unpaid Balance multiplied by the monthly interest rate (annual/12). To make this interactive with the previous chart, make an equation and select the interest rate/12. Use a dollar sign so the value does not change as you drag it down from month 1 to month 36. Example = location Unpaid Balance * E$101/12.

Note Reduction: this is the amount that your loan will be reduced every month until it is paid after 36 months. It is equal to the Monthly Payment minus the Interest Due. Drag this down from month 1 through month 36.

Unpaid Balance: this is what you still owe on the loan. It is equal to the previous Unpaid Balance minus the month 1 Note Reduction. Drag this down from month 1 to month 36. Notice what happens at month 36.

Depreciated Value. Put the Purchase Price from the previous table in Depreciated Value for month 0 or type = location Purchase Price. This is what your vehicle is worth when you buy it and then it looses value. For this project we are going to assume that your new vehicle will be worthless after 10 years (120 months). This will be straight lined, an equal depreciated amount per month. Your monthly Depreciated Value for month 1 will be equal to the month 0 Depreciated Value minus the month 0 Depreciated Value divided by 120. You want the monthly depreciation amount to remain the same so put in a $ sign, i.e. =F101-F$101/120 Drag this down from months 0 to 120.

Equity. This is your share of the value of the vehicle. It is equal to the Depreciated Value minus the Unpaid Balance. Drag this down from months 0 to 120.

1. What happens to the Interest Due as you make payments? ___________ Why? ___________________________

________________________________________________________________________________________________

.

2. What happens to the Note Reduction as you make payments? ___________Why? __________________________

_________________________________________________________________________________________________

.

3. What happens to the Equity as you make payments? ___________Why? __________________________________

_________________________________________________________________________________________________

.

4. What happens to the Equity after your loan is paid? ___________ Why? ___________________________________

_________________________________________________________________________________________________

5. The month 0 value for Equity is equal to what in the previous table? ___________Why? _____________________

________________________________________________________________________________________________ .

6. The month 60 Depreciated Value is equal to what percent of the month 0 Depreciated Value? _________________

Why? ___________________________________________________________________________________________

STEP 5 - LEASING AS ALTERNATIVE FINANCING

Car dealers want you to drive out in a new car, whether you buy or lease it. Leasing is not for everyone but it is an alternative to buying because you can stretch your payments over a much greater period of time thus lowering your monthly payments. The good points for leasing are that the monthly payments are low and you can exchange it for another new vehicle. The bad points are that you can build up no equity in a vehicle, will be making monthly payments for a long time, and the finance charge will be high. The balloon payment (also called final or residual) can be either paid in cash or financed. This exercise will assume financing the balloon payment. Complete the two tables using the below data which was taken from local dealers. Tax, title, and license will not be considered.

1. ’05 Mitsubishi Eclipse: Lease/Alternative: $199 per month for 66 months, final (balloon) payment $7,271, finance final payment at 3.9% for 36 months. Buy: $18,624 with 10% down, 3.9% financing for 36 months.

2. ’05 Lincoln Navigator: Lease/Alternative: $499 per month for 36 months, final payment $21,917, financed final payment at 3.9% for 36 months. Buy: $37,650 with 10% down, 2.9% financing for 36 months.

3. ’05 Kia Sephia: Lease/Alternative: $99 per month for 59 months, residual $2,402 financed final payment at 4.7% for 36 months. Buy: $7,995 with 10% down, 4.7% financing for 36 months.

4. ’05 Montero Sport: Lease/Alternative: $319 per month for 66 months, residual $9,406 financed final payment at 3.9% for 36 months. Buy: $24,737 with 10% down, 3.9% financing for 36 months.

Lease Then Buy Option. Set up the following columns, record the input data above, and calculate for each vehicle.

Vehicle Monthly Months Lease Balloon Months Interest Monthly Balloon Total Lease

Selection Payment Lease Cost Payment Financed Rate Payment Cost Cost

Balloon Cost: Months Financed times Monthly Payment

Total Lease Cost: sum of Lease Cost and Balloon Cost.

Buy Only Option. Set up the following columns, record the input data above, and calculate the rest for each vehicle.

Vehicle Purchase Down Loan Months Interest Monthly Total Buy Total Lease Difference

Selection Price Payment Amount Financed Rate Payment Cost Cost

Total Buy Cost: sum of Down Payment plus Months Financed times Monthly Payment.

Total Lease Cost is from first table.

Difference: Total Buy Cost minus Total Lease Cost.

Which option, lease/alternative or buying, would you prefer? ______________ Why? __________________________

_________________________________________________________________________________________________

What can you conclude about the cost difference between leasing and then buying the vehicle versus buying it outright?

_________________________________________________________________________________________________

Why do you think this is so? _________________________________________________________________________

Take the total number of months in the Lease and Then Buy Option and plug that into the number of months in the Buy Only Option. Do this for all four vehicles. What can you conclude about the differences in monthly payments and the Difference between the two options? ___________________________________________________________________

_________________________________________________________________________________________________

Describe anything new that you learned from this entire (all 5 steps) project or was unexpected. ____________________

_________________________________________________________________________________________________

How will this help you find a vehicle when you are ready to buy? ____________________________________________

_______________________________________________________________________________________

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download