Buying that Dream Car - Project

By Elizabeth Davis,2014-05-15 12:39
99 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:


    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:

    Learning Area This practical spreadsheet lesson offers easy answers to life's

    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


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

    the Lamborghini Home page or the web sites of Porsche, Ferrari,

    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


Microsoft Excel worksheet designed to calculate interest and

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

Follow these time-saving tips:

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

    AutoSave is not available from the Tools menu, choose Add-Ins

    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

    Adding Formulas

    Software: Microsoft Excel

    What to do :

    Follow these time-saving tips for adding formulas:

    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


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


"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


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