Buying that Dream Car - Project
Lesson Subjects: Math, Economics Information: Grade / Age Level Required Software: Microsoft?Excel
High School Optional Software: Microsoft?Internet Explorer
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.
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,
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
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.
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.
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.