Dream Car

Student Activity
                In this activity, you will create a worksheet to
                calculate the actual cost of purchasing two new vehicles.
                You will have two different payments figured for the two different vehicles.

 WHAT TO DO: Create a new Microsoft Excel
                worksheet like the one in Example 1

    Note: You only need two vehicles.
 
Fast Eddie's Auto Sales        
Dream Car        
Name of Car: Ford Ranger Porsche Cadillac Jeep
Sale Price: $18,500 $47,500 $32,000 $28,000
Down payment: $5,000 $12,000 $8,000 $3,200
Loan Amount: $13,500 $35,500 $24,000 $24,800
Interest Rate: 8.25% 9.00% 7.50% 4.90%
Length of Loan: 4 5 4 3
Number of Payments (Year): 12 12 12 12
Amount Paid per Payment: $331 $737 $580 $742
Total Amount Paid: $20,896 $56,215 $35,854 $29,918
Total Interest Paid: $2,388 $8,720 $3,840 $1,912

Example 1: Microsoft Excel worksheet designed
                to calculate interest and total payment for a
                purchase, based on different loan terms.

Adding Formulas
                SOFTWARE: Microsoft Excel

                WHAT TO DO: Follow these time-saving tips for
                adding formulas:

Example 2: Microsoft Excel's Periodic Payment Function
 
 
 
B
1
Ford Ranger
2
$18500
3
$5000
4
=B2-B3
5
8.25%
6
4
7
12
8
=PMT(B5/B7,B6*B7,-B4)
9
=B3+(B8*B6*B7)
10
=(B6*B7*B8)-B4
Example 3
This table lists the necessary formula entries.  Note that the number of the rows are different than the above example.  However, the cell entries are correct and do match the Ford Ranger entries in example 1.
 
 
 

Note that the following instructions refer to example 2.
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.

What you need to do to complete the assignment?

  The length of  the loan  should be 3, 4 or 5 years.  You make up the down payment.  However, be certain that your choices are reasonable.
 

Example of Dream Car Formulas.

 Example of Dream Car Assignment