Excel© Spreadsheet Exercises Contributed by
Dr. Jeffrey Adler, P. E.
MindBox, Inc. (formerly of Civil Engineering
Rensselaer Polytechnic Institute) How to use this section: Each exercise requires the development of an Excel spreadsheet. The exercises are keyed to sections in the text Engineering Economy, 5th edition, by Blank and Tarquin. Appendix A of the text is a complete primer for using Excel and the financial functions pertinent to engineering economic analysis. Sample problems are included in this appendix for setting up each function.
The spreadsheet exercises presented here are especially well suited to an engineering economy course with laboratory sessions or activities that help a student become more adept with spreadsheet-based solutions.
Spreadsheet Exercise #5
Analysis of a Car Loan Exercise Objective: Apply what you know about interest,
loans and AW to a real-world problem.
Blank and Tarquin Text Reference: Chapters 4and 6 (Loans are covered
in Chapter 1)
Problem Statement: Jeff wants to purchase a new automobile. The one
he has selected will cost $25,000 including all fees (e.g., tax, title, and
licensing). Jeff has saved $10,000 to use as a down payment. He plans to finance
the balance though a loan. Jeff has the option to seek financing from his
local bank or directly from his car dealer. The loan would be a conventional
consumer loan with equal monthly payments of compound interest and principal.
Part 1: Bank Financing
Jeff approached his local bank about financing. The bank can offer a new-car
consumer loan of 7.0% interest per annum over 48 months: a) What would be Jeff's monthly payment under this bank loan? b) If Jeff wants to take a 48 month loan but limit his monthly payment to
$300, how much of a down payment must he make?
Part 2: Car Dealer Financing
The car dealership advertises a special financing promotion: 1.9% financing
for 24-36 months OR 3.9% financing for 37-60 months. c) Compute the monthly payment for both a 36 month loan and 60 month loan. d) The car company claims in their advertising that if you select the 36
month loan over the 60 month loan you will save over $1100 in total interest
paid. Does this make sense?? What would you advise Jeff to do? Why? (Hint:
consider the opportunity costs in relationship to the inflation rate and MARR.)
Part 3: The Real-World Choice
Today, it is common for a car dealer to provide the customer with an option
- low financing or a cash rebate. Suppose the dealership offers Jeff a choice
of $1000 cash back or 3.9% financing for 48 months. Assume that
if Jeff takes the rebate he will seek a 48-month loan from his bank at 7.0%
per annum. e) If Jeff decided to make a $10,000 down payment and accept the $1000 rebate
offer, what would be the monthly savings? f) Perform an analysis to determine the minimum down payment necessary to
make the bank loan more attractive than the low financing offered by the dealer.
(This is a breakeven analysis.) Plot the monthly payment versus loan amount
(in $500 increments from $10,000 to $20,000) for both loan options.
| 
2002 McGraw-Hill Higher Education