Car Purchase, Loan Payments, and Amortization

Go to Discussion Section of Live to watch a video that will walk you through this lab.

Objectives

Given terms of a loan, calculate a monthly payment.
Create a spreadsheet to amortize the loan.
Calculate the total amount of principal and interest paid each month.
Calculate the total cost of a loan.
Learn to use multiple sheets within a spreadsheet.
Learn to look up data in a table
Become proficient with different advertising modes; know when to use absolute addressing and when to use relative addressing.

Preliminaries

Download and read the Excel documentation provided. Some important information is repeated below.

Cell Addressing

A cell address is made up of a column letter and a row number.

Relative Cell Address: A relative cell address is relative to the current position. When replicated, it changes relative to the new position. Relative addresses are used when the same formula applies to multiple cells operating on data relative to the cells. e.g. A3

Absolute Cell Address: Absolute cell addresses to not change when replicated. Absolute addresses are used for assumptions. e.g. $A$3, A$3, $A3

Multiple Worksheets: Excel workbooks are made up of multiple worksheets. A formula in one worksheet can address cells in other worksheets. The reference to cell in another worksheet can be absolute or relative. To address a cell in another worksheet:

sheetname!cell address

sheet1!A3

sheet2!$A$3

Subaru!b5 (if the sheet has been given a name

Built-in Functions

A function performs a predetermined set of operations. Functions take parameters and return a result. Parameters are enclosed in parenthesis and separated by commas. The parameter list is specific to a function.

This lab uses seven built-in functions:

pmt
ipmt
ppmt
vlookup
sum
min
max

vlookup

The vlookup built-in function searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table.

vlookup(lookup_value, table range, column_index_number)

PMT

We will only be using rate, nper, and pv. fv is the balance you want to remain at the end of the term. A balance of 0 is the default, so it may be omitted. Type indicates when the payment is due. By omitting it, the default is due at the beginning of the period.

PPMT

We will only be using rate, per, nper, and pv.

IPMT

We will only be using rate, per, nper, and pv.

sum, min, and max

We will be passing an address range to these functions. Sum adds the data in the range and returns the result; min finds the minimum value in the range and returns the result; max finds the maximum value in the range and returns the result.
A range can be a row range or a column range: b5:b8 c9:d9.

Overview

In this lab, you will be calculating and comparing the total cost of ownership of two different vehicles over 5 years. The lab consists of two parts. A loan is going to be taken to purchase the vehicle. Part 1 of this lab creates an amortization table for the loan for each vehicle. The cost of purchasing the vehicle must be considered in the total ownership cost. In Part 2, you will be including the cost of operating and maintaining each vehicle to calculate the total cost of ownership over the 5 years.

Part 1: Automobile Loan

You will be given loan terms and the purchase price for 2 different vehicles. You will create a worksheet for each vehicle to amortize the loan for each of the vehicles. These worksheets will be used in Part 2 of this lab to calculate the total cost of ownership of the 2 different vehicles.

Data

You are going to purchase a new vehicle. You are looking at 2 different SUV's:

Subaru Outback
Toyota RAV4

The purchase price of the Subaru Outback is $29,999. The term of the car loan is 4 years, 5.125% interest.

Toyota RAV4

The purchase price of the Toyota RAV4 is $26,500. The term of the car loan is 4 years, 6.275% interest.

Using Firefox, download a template with worksheet 2 filled in with the data for the Subaru Outback as well as worksheet 1 filled in with general assumptions and per vehicle assumptions about the cost of operation. Download the template.

The Spreadsheets

Important Note!!!!!

Important note: If your formula results in ######### in the cell, all it means is that the column is not wide enough for the data. Simply widen the column. This can be done by dragging the column border to the right.

Adding Worksheets

By default, excel provides 3 worksheets. When you open a new workbook, f you look at the bottom of your workbook, you should see tabs "sheet1", "sheet2", and "sheet3". The sheet names were changed in the template to "Cost of Ownership", "Subaru", and "Toyota". We will be using "Total Cost of Ownership" in the automotive lab where we will be comparing the cost of ownership of the 2 vehicles over 5 years. In the other two sheets, we are going to create the amortization tables for the Subaru and Toyota respectively.

While you will not need more than three worksheets in this lab, if you do need more than the default 3 sheets, use follow the following instructions to insert more:

You can also name the sheets to something meaningful by double clicking on the sheet name and typing the name.

Click on the "Subaru" tab to activate sheet2.

Your name, the title, interest rate, loan term, principal, column headings, and payment numbers for the Subaru Outback have been filled in for you. Change "Your Name" to your name.

Assumptions - Loan information

The assumptions consist of the loan information. The name of the assumption should appear in column B and the value of the assumption should appear in column C. The first assumption is in row 3. The assumptions are as follows beginning in row 3:

Payment. C3 will contain a formula to calculate the monthly payment. It is described below.
Interest Rate. C4 will contain the interest rate. See the information above for the annual interest rate.The cell must be formatted with the number of decimal places equal to that listed in the interest rate specified.
Term. The term is in C5, and it is the number of years of the loan. It is 4 for both vehicles.
Principal. The principal is in C6. This is the sale price of the car - the amount of the loan. See the data above.

Payment Number

The payment number must be created in Column A. In the template, 1 is listed in A9. The payment number for the other payments must be created. Type 2 in A10. Highlight cells A9 and A10. Replicate the information in the subsequent rows. The loan term for the both vehicles is 4 years. Therefore, there will be 4*12=48 payments. Payment information will be in rows 9-56.

Payment

In C3 you are going to use the PMT built-in function to calculate the monthly payment.

=-PMT(interest rate, number of payments, principal )

The interest rate in the assumption is the annual interest rate. Therefore, in the formula you must divide the annual interest rate by 12 months as there are 12 months in a year.

The term in the assumption is the number of years of the loan. The function requires the number of payments. The term must be multiplied by 12 months per year.

The principal is an assumption.

Since the payment is a deduction, the function returns a negative value. Therefore, for purposes of this spreadsheet, the results of the formula must be negated.

Be sure to properly use absolute addressing!

The formula for payment should be:

=-PMT($C$4/12,$C$5*12,$C$6)

The Amortization

The formula for the beginning balance for payment number 1 (B9) is going to be different than the formula for the beginning balance for payments 2-48 (B10-B56). To start with, enter the formula for payment number 1. The beginning balance comes from the assumption Principal stored in C6. The formula:

=c6

Since this formula is only going to be used in this row, it is not necessary to use absolute addressing.

C9 should contain a formula to calculate the principal portion of the payment. The PPMT built-in function is to be used.

=PPMT(interest, loan period, number of payments, principal)

The monthly interest rate is described under the PMT formula. The loan period is the payment number. It is obtained from $a9 (note absolute addressing for the column). See PMT for a description of the number of payments. The Principal is an assumption. Since this is a deduction, the formula should be preceded with the negative sign. This formula applies to all 48 payments.

Proper absolute and relative addressing must be used.

The formula for Cumulative Principal for Payment 1 will be different than the Formula for Cumulative Principal for Payments 2-48. The formula for Payment 1 in D9 is simply :

=C9

The Principal paid in this payment.

The IPMT built-in function is used to calculate the Interest Paid. The parameter list for IPMT is identical to the parameter list for PPMT. It should also be negated. It applies to all 48 payments. By specifying the column as absolute in the principal formula for c9 allows you to copy and paste and just change the name of the function from ppmt to ipmt.

The formula for Cumulative Interest for Payment 1 in F9 will be different than the formula for Cumulative Interest for Payments 2-48. For Cumulative Interest for Payment 1 in F9, it is simply the Interest Paid with this payment:

=e9

The formula for Paid to Date is the sum of the cumulative Principal and the cumulative Interest. This formula applies to all payments.

=D9+F9

As a cross check, make sure that the sum in g9 equals your payment in c3.

The formula for Ending balance is the Beginning Balance minus the Principal:

=B9-C9

This formula applies to all 48 payments.

Completing the Sheet

Replicate the formulas for principal payment, C9, interest payment, E9, paid to date, G9, and ending balance, H9, from Row 9 into Row 10.

Different formulas for Beginning Balance, Cumulative Principal, and Cumulative Interest must be written for Payment Number 2 in Row 10.

The formula for Beginning Balance will come from the Ending Balance of the previous payment:

=H9

The formula for Cumulative Principal is the sum of the principal payment from this payment plus the Cumulative Principal from the previous payment:

=c10+D9

Note the last payment the cumulative principal should equal the original principal.

The formula for cumulative Interest is similar to that of Cumulative Principal, the sum of the Interest Paid in this payment plus the Cumulative Interest from the previous payment:

=E10+F9

Row 10 should be marked and replicated through the remainder of the 48 payments.

Check your results!!!!! At the bottom of this lab page is a link to the results. Sanity check:

The ending balance should be 0

The cumulative principal should equal the starting principal in C6.

Sheet for the Toyota Rav4

You have completed a worksheet with the loan amortization for the Subaru Outback. You are now going to complete worksheet 3 with loan amortization information for the Toyota Rav4.

You must first select the data to be copied. Click in the select all entry. It is the intersection of the column headings and the row headings. This marks the entire worksheet. Click Edit, copy.
Click on the Sheet3 labeled Toyota at the bottom of the spreadsheet window. This will activate Sheet3. Place the cursor in A1 of Sheet3. Click Edit Paste. This will copy the entire sheet1.

Modify the title to Toyota Rav4. Modify the assumptions appropriately.

Check your results as described under the Subaru.

Save the file

Part 2 Cost of Ownership

Go to Sheet1 labeled Cost of Ownership.

Your name must appear in A1

Assumptions

General assumptions and per vehicle assumptions have been filled in for you in the template.

Cost of Fuel B5
% Fuel Cost Increases per year B6
Miles driven per year B7
Years of ownership B8 (note it is different than the loan period)
Table of repair costs A11:B17
The repair costs are in a two-column table. Column A contains the total miles on the vehicle and Column B contains the estimated repair costs given the miles on the vehicle. When the vehicle has 0-15,000 miles, it is expected that there will not be any repair costs. From 15001-25000, there will be repair costs of $100, etc.

Yearly Insurance
Miles of warranty
Cost of maintenance per mile
Miles per gallon

Column C will contain data for the Subaru Outback.
Column D will contain data for the Toyota Rav4.
All formulas will be written in Column C for the Subaru Outback. You will then use replication to replicate the formulas for the Toyota Rav4.

Loan Term, purchase price, and monthly payment

You are going to obtain the loan term, purchase price, and monthly payment from the amortization table worksheet for each vehicle. You must use absolute addressing.

In C24, obtain the Loan Term: from C5 of the Subaru Worksheet
In cell C24 type the formula: =Subaru!$c$5
Principal (purchase price): from C6 of the Subaru Worksheet
In cell C25 type the formula: =Subaru!$c$6
Payment: from C3 of the Subaru Worksheet
In cell c26 type the formula: =Subaru!$C$3

Important note: When a formula with a sheet number/sheet name is replicated, the sheet number does not change. You must manually change it.

Calculations: You must use absolute addressing for general assumptions

C27: Using the assumptions miles/year and years of ownership along with the miles of warranty for the Subaru, calculate the miles over warranty.
=$b$7*$b$8-c21
C28: Using the assumption miles/year and the Subaru information on miles/gallon, calculate the gallons of gas to be used per year.
C29: Use the Loan term, months/year, and the monthly payment to calculate the purchase price plus interest.
Sanity Check: look in the last row of the amortization table for paid to date. These values should match.
C30: Using the assumption years of ownership and the yearly insurance for the Subaru, calculate the total cost of insurance.
C31: Using Gallons/year and cost of fuel, calculate the cost of fuel for year 1.
C32:C35: B6 is an assumption that contains the percentage the cost fuel is going to increase per year. Using that information and the cost of fuel for year 1, calculate the cost of fuel for year 2. This can be replicated to years 3-5. The cost of fuel calculated in C31 must be increased by the percentage of the assumption in B6. There are two different formulas that are correct.
C36: Using the assumption miles/year, The assumption years of ownership, and maintenance/mile, calculate the total cost of maintenance.
C37:C41 Use the VLOOKUP function to look up the cost of maintenance for each year.
vlookup(lookup_value, table range, column_index_number)
The lookup_value is the number of miles driven given the year of ownership. Year 1 is taken directly from the assumption Miles Driven/Year. The other years, you must calculate it based on the year of ownership.
The table range is $a$12:$b$17
The column_index_number is 2, the column of the table containing the data to be returned.
Write the formula for Cost of Repairs for Year 1. It can be replicated into the subsequent years. However, it must be edited each year to calculate the total miles on the vehicle for that year. The formula for the cost of repairs for year 1:
=VLOOKUP($B$7,$A$12:$B$17,2)
C42: Using the sum function, calculate the total cost of ownership over the time period. Note: The total cost calculation starts with the purchase price plus interest in C29.
Replicate the data from the Subaru into the column for the Toyota Rav4. You must manually edit the sheet label for the loan term, purchase price, and monthly payment.
C43: using the min function, find the least expensive.
C44: using the max function, find the most expensive.
Mark the data for the vehicles and create a bar chart.

Check your results for accuracy

Submission

Submit your completed spreadsheet via D2L.

Results

12/31/2021