DOC

# Buying that Dream Car - Project

By Elizabeth Davis,2014-05-15 12:39
96 views 0
Buying that Dream Car - Project

Buying that Dream Car - Project

Lesson Subjects: Math, Economics Information: Grade / Age Level Required Software: Microsoft?Excel

High School Optional Software: Microsoft?Internet Explorer

(14-18 yrs)

Microsoft Class Server Learning Activity

What's in this Lesson:

Products

Used In This ? Teaching Guide Lesson: ? Student Activity

Step A: Create a Database Applications Step B: Adding Formulas Step C: Reality Check Excel

Find More Lessons For:

Economics perplexing math problems like How much will my dream car really

Self cost after financing? Students will calculate the cost of purchasing

Regulation their very own Lamborghini sports car and determine if the

Technology ultimate price tag is really worth the investment.

Thinking and

Reasoning To expand students' understanding of mathematics, show practical applications of math in everyday life, and provide practice in using

Themes a Microsoft Excel worksheet to solve practical everyday problems.

Careers Knows how to open and set up a Microsoft Excel worksheet and Managing can access the World Wide Web using Microsoft Internet Explorer. Money

Approximately one week

Ask students to name some exotic sports cars. If a student mentions a Lamborghini, have that student tell what he/she knows about this luxury car. Tell students that, although this elite supercar

is no longer produced, its popularity stemmed from its reputation

as a "menacing road warrior" that could go from 0-60 miles per

hour (mph) in 4.2 seconds! Go on to explain that the car was

engineered to hug the road at a top speed of 183 mph. Then tell

students that a previously owned Lamborghini sells for \$150,000.

But thanks to Honest Ed's easy finance plan, they can purchase one

of these luxury cars for a mere \$150,000 plus interest.

Review the financial concepts that students will use to complete

this project such as interest rate, loan term, and repayment period.

Explain that Microsoft Excel has a built-in "Periodic Payment Function" which calculates the amount you pay for a loan based on

constant payments and a constant interest rate. The payment

function is written as follows: PMT(rate, nper, pv) where "rate" is

the interest rate for the loan, "nper" is the total number of

payments for the loan, and "pv" is the present value or principal

(i.e., the total amount that a series of future payments is worth

now). For this function to work correctly, proper "syntax" (the order in which the function's variables are entered) must be

observed.

For some added fun, have students use Internet Explorer to visit

or Corvette.

In this activity, you will create a worksheet to calculate the actual

cost of purchasing a previously owned Lamborghini sports car that

is selling for \$150,000. You will then pose several "what if"

questions about the proposed purchase terms of this exotic "dream car" to see how various terms affect the amount of interest paid for

the car.

Step A

Create a Database

Software: Microsoft Excel

What to do: Create a new Microsoft Excel worksheet like the one

below.

Microsoft Excel worksheet designed to calculate interest and

total payment for a puchase, based on different loan terms.

Worksheet Titles: To create a bold face and centered worksheet

title, type the title in column A and press Enter. Highlight columns

A through E and then click the Merge and Center button on

Microsoft Excel's Formatting toolbar. Click the Bold button. Increase the font size of the text to 14.

Word Wrap: Enter cell contents in column A. To turn on word

wrap, highlight all cells in which text must wrap (A6:A12) then

right-click the highlighted cells. Click the Alignment tab, then

select Wrap text in the Text control section.

Column Width: To adjust column width, double click the column

boundary to the right of a column heading. To create columns of

uniform width, select those columns, choose Column from the

Format menu, choose Width, then enter a value, e.g. 14.

Formatting Currency: Format cells B4, B6, B10, B11, and B12

as Currency, by selecting B4, then pressing the Ctrl key while

clicking the other cells. Right-click on a highlighted cell, then

choose Format Cells from the pop-up menu. Click the Number tab and choose Currency with 2 decimal places.

Saving Work: Turn on AutoSave to have Microsoft Excel save a

workbook automatically at a time interval that you specify. If

from the Tools menu, place a check mark in the AutoSave Add-Ins

dialog box, click OK. Then choose AutoSave from the Tools menu

to specify the time interval for Automatic Save. Now Microsoft

Excel will pop up save reminders.

Step B

Software: Microsoft Excel

What to do :

Microsoft Excel's Periodic Payment Function

PMT Function: In Cell B10 paste Microsoft Excel's PMT

function (Periodic Payment Function). Click cell B10, then choose

Function from the Insert menu. Select Financial from the Function

Category list, then choose PMT from the Function Name list. Click

OK.

Type the following cell references in the PMT dialog box. For Rate

(the interest rate per period for the loan), type =B7/B9; for Nper

(the total number of loan payments), type =B8*B9; for Pv (loan

amount), type =B6. Choose OK.

Microsoft Excel returns a negative number to indicate that you are

making a payment.

Note: To work with positive numbers on the worksheet, calculate

PMT as follows: =PMT(rate,nper,-pv).

"Total Amount Paid" Formula: In B11, type the following

formula, which will calculate the total amount paid for a loan:

=SUM(B10*(B8*B9)), where B10=PMT (i.e., the amount paid

each payment period) and B8*B9 =nper (the total number of loan

payments).

"Interest Paid" Formula: In B12, type the following formula

which calculates the total amount of interest paid: =SUM(B11-B6),

which is the total amount paid minus the loan amount.

Showing Formulas: To have Microsoft Excel show formulas,

rather than formula results, choose Options from the Tools menu;

click the View tab, then select the Formulas check box. To display formula results, clear the check box.

Once you format the cell contents of column B, entering

appropriate formulas where necessary, you can copy cell content

and format to columns C and D using Autofill or Fill Right.

Step C

Reality Check

Software: Microsoft Excel

What to do: Once your worksheet is set up, you can pose different

"what if" questions about your loan to see how the total amount of

interest paid might change. For example, what if the interest rate

was 4% and the length of the loan was ten years? What would your

monthly payment be? How much interest would you pay over the

life of the loan?

Assume you want to pay a maximum of \$250 per month for a new

car. Also, assume that you are willing to take out a five-year loan at an interest rate of 5 percent. Calculate the maximum selling

price of a car you can afford, based on those assumption. Use the

same worksheet, entering numbers in Column E. First, select E10

(the cell with the PMT function), then choose Clear/ Contents from

the Edit menu to delete the formula. Copy contents of cells D7:D9

to E7:E9. Copy formulas in D11 and D12 to E11 and E12

respectively.

Now enter 250 in Cell E10. (This is the amount of money you want

to pay each month). On a piece of scratch paper, solve the following algebraic equation where X=price of car per month

and .05X=monthly cost of interest. For example:

X + .05X = 250

1.05X = 250

X = 238.1

Each time you pay \$250, \$238.10 is principal and \$11.90 is

interest. You can now determine the amount you can afford to pay for the car, assuming no down payment.

In E6 enter the following formula: =238.1*(E8*E9), where

\$238.10 is the monthly principal and the product of E8*E9 is the

number of total payments.

Report this document

For any questions or suggestions please email
cust-service@docsford.com