## Monday, November 9, 2015

### Four Financial Math Questions

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?