Buying a Dream Car

Buying a Dream Car

Summary:

This practical spreadsheet lesson offers easy answers to life’s perplexing math problems like How much will my dream car really cost after financing? Students will calculate the cost of purchasing their very own sport car and determine if the ultimate price tag is really worth the investment.

Objectives:

To expand students’ understanding of mathematics.
To show practical applications of math in everyday life.
To provide practice in using a Microsoft Excel worksheet to solve practical everyday problems.

Step 1 – Create a Worksheet:

Create a Microsoft Excel Worksheet like the example below.

A / B / C / D / E
1 / Jose's Dream Machine
2 / Dream Car: Hummer
3
4 / Sale Price / $60,000.00
5
6 / Loan Amount / $50,000.00 / $50,000.00 / $30,000.00 / $30,000.00
7 / Interest Rate / 5% / 5% / 5% / 5%
8 / Length of Loan in years / 6 / 2 / 6 / 2
9 / Payment Period per Year / 12 / 12 / 12 / 12
10 / Amount Paid each Payment Period / $805.25 / $2,193.57 / $483.15 / $1,316.14
11 / Total Amount Paid / $57,977.76 / $52,645.67 / $34,786.65 / $31,587.40
12 / Total Interest Paid / $7,977.76 / $2,645.67 / $4,786.65 / $1,587.40

v Worksheet Titles:

1) Type the title “Your Name Dream Machine” in A1.

2) Type “Dream Car: and what your dream car is” in A2.

3) Highlight A1 – E1, click on Merge and Center on the toolbar

4) Highlight A2 – E2, click on the Merge and Center button on the toolbar.

5) Click the bold button on the toolbar.

6) Increase the font size of the text to 14.

v Word Wrap:

1) Highlight all cells A6 – A12.

2) Right Click, Format Cells.

3) Click the Alignment Tab and check Wrap Text.

4) Type the Information in Column A on the correct rows.

5) Type the numerical information for Sale Price, Loan Amount, Interest Rate, Length of Loan in Years and Payment Period per Year.

v Column Width:

1) Highlight Column A.

2) Right Click on A, Click Column width

3) Enter 16 and click Ok

4) Highlight Columns B – E

5) Right Click on B, Click Column width

6) Enter 14 and click Ok

v Formatting Currency:

1) Click on cell B4

2) Hold down the Ctrl Key and click on B6, B10, B11 and B12.

3) Click the Dollar Sign on the tool bar to change to Accounting

Step 2 – Adding Formulas:

v PMT Formula:

1) Click cell B10

2) Click Insert, Function

3) Choose PMT and click Ok

4) For Rate: Type in B7/B9 (the interest rate per period for the loan)

5) For Nper: Type B8*B9 (the total number of loan payments)

6) For Pv: Type -B6 (loan amount)

7) Click Ok

8) Right click in cell B10

9) Click copy

10) Highlight C10 – E10

11) Enter

v Total Amount Paid:

1) Click in cell B11

2) Type =Sum(B10*(B8*B9)) This calculates the total amount paid for the loan.

B10 = amount paid each payment period.

B8*B9 = total number of loan payments.

v Interest Paid:

1) Click in cell B12

2) Type =Sum(B11-B6) This calculates the total amount of interest paid

B11 = the total amount paid

B6 = the loan amount

1. How much is your monthly payment in cell C10?

______

2. How much interest will you end up paying in cell D12?

______

3. What would your monthly payment be on a $50,000 loan for 5 years at a rate of 7%? ______

4. What is the total amount paid in cell D11?

______

5. What is the total amount paid for your car in B11?

______

Bonus: You wish to purchase a new house. The house is $140,000. Your loan amount will be for $120,000 at an interest rate of 7% for 15 years.

What is the total amount paid for the loan?______

What is the total amount paid for the house?______