In this project, you will choose a car that you wish to purchase. After finding and customizing your vehicle, you will create an amortization schedule for a vehicle loan. Creating and analyzing this schedule may give you reason to reconsider your vehicle purchase.

Choose a new or used vehicle. If you are choosing a new vehicle, choose all the options you want. Most car manufacturers’ websites allow you to configure the vehicle, and give you an updated price as you do so. Following are websites for many of the major vehicle manufacturers, as well as two websites that list used vehicles for sale.

GMC Canada (includes Saturn, Saab, Hummer, Isuzu)

Chrysler/Dodge

Honda

The Auto Trader (used)

Car Click (used)

2. Find out the monthly payment on the vehicle you have chosen. Make sure that you keep a record of the purchase price (remember to add GST for a new vehicle), term of the loan and interest rate. Most of the manufacturers’ sites have payment calculators, and also affordability calculators that tell you what income is needed to support a particular payment. The following sites may help you if you need a payment calculator, or if you need help finding a loan interest rate.

www.canadiancarprices.com

MoneySense Magazine

www.tdcanadatrust.com/lending/car.html

www.scotiabank.com/cda/eventdetail/0,1605,LIDen_SID94,00.html#

Find out the rate at which you can expect your vehicle to depreciate. You can ask knowledgeable people, look in consumer magazines, search the Internet, or use any other valid research tool. You must cite the source of your information in your final report.

Produce an amortization schedule similar to the one below.

	A	B	C	D	E	F
1	Principal		25000.00
2	Annual interest rate (%)		6.9
3	Number of payments		48
4	Monthly payment		597.50
5
6	Payment Number	Opening balance	Interest rate (%)	Interest paid	Payment	Closing balance
7	1	25000.00	0.575	143.75	597.50	24546.25
8	2	24546.25	0.575	141.14	597.50	24089.89

All entries in the columns Opening Balance, Interest Charged, Payment and Closing Balance must be formulas.

Answer the following questions about your spreadsheet.

a) What formula was entered in cell D7 to calculate interest charged during the first payment period?

b) What formula was typed into cell F7 to calculate the closing balance after the first payment?

c) What formula was typed into cell B8 to calculate the opening balance for the second payment period?

d) What formula was typed into cell D8 to calculate the interest charged during the second payment period?

e) How much interest was charged during the 12^th month?

f) What is the total interest that will be charged during the loan?

g) When will the outstanding balance of the loan first be less than half the amount borrowed?

h) When will the depreciated value of the vehicle first be more than the outstanding loan amount?

i) By how much is the final monthly payment different from the payment every other month?

Final Report

When finished, your final report must include all the web URL’s you used to find your information, including the price and options for your vehicle, interest rate, payment amount, depreciation if applicable. You must also submit, by email, your spreadsheet and answers to question #5. All the sources of your information must be cited. Your spreadsheet must use formulas wherever possible. In fact, the amount borrowed, the monthly payment amount and perhaps the payment numbers are the only values that should not appear as formulas.

Street of Dreams FAQ

Do I have to be able to afford my vehicle?
No. The cost of the vehicle, and your monthly payments, do not have to be realistic or affordable.

Do I have to get a loan, even if my car is inexpensive?
Yes. The point of the assignment is to design and use a spreadsheet to account for your loan payments.

How much should I make my down payment.
$0. Nothing. Nada.

What interest rate do I use?
Follow one of the links provided in the assignment and find current interest rates offered for vehicle loans. Make sure that you include the URL in your final presentation.

What is the term of my loan?
This is up to you, within reason. You can choose any term between 2 years and 5 years to repay the loan.

Where do I get the monthly payment amount?
Follow one of the links provided in the assignment. Several of the sites listed have loan payment calculators. Make sure you know the price of your vehicle, your loan interest rate, and the term of your loan before you try to calculate the monthly payment. Make sure that you include the URL in your final presentation

Assessment

Your work will be assessed using the following rubric.

General Scoring Rubrics

Mathematical Content and Understanding

5	The student applies appropriate mathematical knowledge to find a complete and correct solution. Appropriate problem solving strategies and mathematical processes are used. The solution is fully supported and justified, and includes graph(s), equations, solutions and explanations.
4	The student applies appropriate mathematical knowledge to find a nearly complete and correct solution. Appropriate problem solving strategies and mathematical processes are used, but an error hinders understanding the response. The solution and conclusions are well-supported by graph(s) equations, solutions and explanations.
3	The student applied mathematical knowledge to find partial solutions to the problem, and reflects a basic understanding of the problem. Appropriate strategies and processes are used to find partial solutions. The solution is supported, but by insufficient or inadequate evidence. For example, an equation or graph that may have been helpful is omitted or poorly done.
2	The student applied some relevant mathematical knowledge and processes to find partial solutions to the problem; however, the response reflects a minimal understanding of the problem. There is limited support for the solution. For example, equations are given but not solved.
1	The student applied some relevant mathematical knowledge and processes to explore the initial stages of the problem, however the response reflects a misunderstanding of the problem. There is little or no valid support for the solution.

Communication

5	The student communicates strategies and solutions in a clear, complete and organized manner that reflects a thorough understanding of the problem.
4	The student communicates strategies and solutions in an organized manner but errors or omissions affect clarity.
3	The student communicates strategies and solutions in an organized manner, however errors and omissions affect clarity
2	The student communicates strategies in a manner that lacks clarity or is incomplete.
1	The student communicates very little relevant information and lacks clarity.

Technology

5	The student uses technology appropriately, efficiently and effectively.
4	The student uses technology appropriately.
3	The student uses technology, however inconsistencies may be present, or the technology was not always used in the most appropriate manner.
2	The student uses technology inappropriately, for example: with many significant errors, or did not discriminate will between tasks requiring technology and tasks not requiring technology.
1	The student uses technology inappropriately or does not use technology where it was required.

HOME