These four problems
will help people learn how to solve financial problems and math problems with
an Excel spreadsheet.
These problems and eleven others were
originally published in the workbook.
Financial Problems in
Excel
The workbook includes answers to all fifteen questions. The objectives of the workbook are to teach students how to use financial functions in Excel, how
to set up and organize a spreadsheet, and to provide insight into financial issues,
which affect a lot of people.
Question One: A person is considering taking out a $180,000
mortgage and must choose between a 15-year FRM and a 30-year FRM. The interest rate on the 15-year mortgage is
2.90 while the interest rate on the 30-year mortgage is 3.40.
What are the monthly payments on the two loans?
What are the total interest payments on the two loans over
the life of the loan?
What is the after-tax cost of the interest payments on the
two loans?
What is the tax savings from the tax deductibility of
mortgage interest?
What is remaining loan balance after 15 years for the two
loans?
Question Two: Consider two people one with good credit and
one with bad credit. Each person has a
car loan, a 30-year FRM, and a personal student loan. The date of origination of each loan, the
term of each loan, and the interest rate on each loan for the two people are
presented in the table below. What was
the interest paid on these loans for the two people in 2014?
Loan information for Two
People
|
|||||
Bad Credit Rate
|
Good Credit Rate
|
Term of Loan in Years
|
Loan Origination Date
|
Initial Loan Balance
|
|
Car Loan
|
0.11
|
0.04
|
6
|
1/1/13
|
$15,000
|
Personal Student Loan
Rate
|
0.12
|
0.06
|
20
|
1/1/10
|
$45,000
|
Mortgage Rate
|
0.055
|
0.0325
|
30
|
6/1/13
|
$320,000
|
Question Three: A person has a $80,000 student loan at a 7.0% interest
rate. The student debt is the person’s
only debt. The person can set the term
of the loan at 10 years or 20 years. The
person makes $95,000 per year. The
person is seeking a mortgage. The
mortgage payment must be less than 28% of monthly income. In addition, total monthly debt payments
must be less than or equal to 38% of monthly income. The current mortgage rate is 4.5%.
Create a spreadsheet
that calculates the amount of mortgage this person can qualify for if the
student loan is set at a 10-year term and if the student loan is set at a
20-year term.
How much mortgage can the person qualify for at current interest
rates?
How much mortgage could the person qualify for if the
student loan interest rate rose by 10%?
How much mortgage could the person qualify for if the
mortgage rate rose by 10%?
Question Four:
The table below contains information on the price and the fuel efficiency for a
Toyota Prius and a Toyota Corolla.
Fuel Efficiency for the
Prius and Corolla
|
||
Toyota
Prius
|
Toyota
Corolla
|
|
City
|
51
|
30
|
Highway
|
48
|
42
|
The cost of the Prius and the Corolla are presented below.
Cost of Prius and Corolla
|
||
Prius
|
Corolla
|
|
$24,200
|
16,800
|
|
Assume both cars are driven $15,000 per year with two thirds
of the driving in the city and 1/3 of the driving on the highway.
Gas is bought on a bi-weekly basis. The price of gas is $3.00 per gallon. Both cars are driven for 12 years.
The cost of capital for the car owners is 5.0%.
Under these circumstances does it make sense to choose the
Corolla or the Prius?
What gas price would result in the Prius being more
economical than the Corolla?
Change the assumptions on miles driven to 18,000 per year
for both drivers and the composition of driving to 90% city and 10%
highway. Assume the gasoline price goes
to and stays at $4.50 per gallon. How
do the costs of the Prius and the Corolla now compare?
No comments:
Post a Comment