Financial Math in Excel

All fifteen questions and answers can be purchased at Teachers Pay Teachers at the link below.


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 the 15-year FRM and the 30-year FRM 10 years after mortgage origination.   Both homeowners plan to stay in their home with their mortgage for an additional 5 years.  


What is the reduction in the mortgage balance over these five years?

What are total interest payments for the two homeowners over this five-year period?



Question Three examines the impact of a bad credit rating.

Question Three:  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



http://financememos.blogspot.com/2015/11/question-three-cost-of-bad-credit.html

Questions Four and Five look at how long it takes to repay credit card debt under different assumptions.

Question Four:  A person has $15,000 in credit card debt divided over 5 credit cards with identical terms.   The interest rate on all five cards is 12%.  The minimum required monthly payment is 3.0% of the monthly balance.  

How long would it take for the person to repay the entire $15,000 in credit card debt if the person continues to pay 3.0% of  $15,000?

How long would it take for the person to cut the loan balance in half if the person continues to make this monthly payment?

Question Five:  Consider the person in problem four with five credit cards each with a $3,000 balance, a 3% minimum payment requirement, and a 12% interest rate.   In addition, each card has an annual fee of $60.   The credit card holder pays the annual fee with $5.00 monthly installments.

What is the person’s credit card balance after 41 months?   How long would it take the person to repay all credit cards when the $5.00 fee is included?



http://financememos.blogspot.com/2015/11/question-five-payoff-debt-for-credit.html

Question Six, Seven Eight, Nine, and Ten all deal with how student debt and credit card debt impact mortgage markets.

 Question Six:  A person with a $100,000 student loan with a 10-year term at a 6% interest rate and no other debts is applying for a mortgage.  In order to qualify for the mortgage two constraints must be satisfied.  First, total mortgage interest must be less than 28% of monthly income.  Second, total interest payments from all sources must be less than 38% of monthly income.


What monthly income does this person need in order to qualify for a $300,000 mortgage if the mortgage interest rate on a 30-year FRM is 4.25%?   

How much income does the person need to qualify for the same loan if the student loan is converted to a 20-year term?

 Question Seven:  A person has $30,000 in credit card debt in addition to the $100,000 student loan.  


Modify the student debt and mortgage qualification spreadsheet to calculate the amount of income needed to qualify for a $300,000 mortgage at 4.25% if the minimum required credit card payment is 3.0% of the monthly credit card balance.  

What is the required income for the mortgage if the student loan is kept at a 10-year term and if the student loan is moved to a 20-year term?

Question Eight:  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 Nine:  Consider the information on income and student debt in the table below on five recent college graduates in the table below.   All student loans have a 10-year term and a 7.0% interest rate.   The interest rate on all mortgages is 5.0%.   Use the calculator on the maximum allowable mortgage to determine how many of these five people will qualify for a $300,000 mortgage.


Income and Student Debt Information for Five College Graduates
Income
Student Debt
1
$95,000
$80,000
2
$60,000
$20,000
3
$120,000
$67,000
4
$80,000
$110,000
5
$75,000
$30,000

 Question Ten:  One person graduates college with no debt and immediately purchases a home for $200,000 with a 30-year FRM.   The LTV is 90%.   The interest rate is 5.0%


The second person graduates college with a lot of student debt and chooses to wait five years before buying an identical house.   The second person also gets a 30-year FRM with an interest rate of 5.0% and a LTV of 90%. 

The price of both houses increases 2.0% per year for a 15-year period.  What is the house equity for the two people after 15 years?


http://financememos.blogspot.com/2015/11/question-ten-impact-of-house-purchase.html

Questions Eleven and Twelve involve the calculation of house equity over a lifetime for people who might purchase more than one home.   The strategic variable in question eleven is the type of mortgage.   The strategic variable in question twelve is the number of times  a person moves.

 Question Eleven:  Two people buy two homes over a 24-year period.   The first home for both consumers cost $225,000.  The second home cost $600,000.   Both people stay 12 years in each home.


All equity obtained after the sale of the first home and the subtraction of a 6% sales commission is used as a down payment for the purchase of the second home.

The real estate transactions for the two consumers differ in two respects --- (1) the term of the loans and (2) the interest rates on the loan.

·      The first consumer uses 30-year FRMs.   The interest rate on the first house was 3.84% and the interest rate on the second house was 6.5%.   

·      The second consumer uses 15-year FRM.    The interest rate on the first home is 0.0308.   The interest rate on the second house was 5.2%.

House prices increased 1.0% per year.

What is the available equity after the sale of the second house for the two house buyers?







 Question Twelve:  This problem examines the potential cost of moving on ultimate house equity accumulated over a period of time.


In this problem all houses are purchased with 15-year FRM at an interest rate of 5.0%.  Also, all houses appreciate in value at a rate of 1.0% per year. 

At the beginning of a 15-year period two people purchase a $300,000 house.   One person stays in the same house for all 15 years.   The other person buys a new house identical in value to the he is currently living in after 7 years.

The sales commission on the first house is 6.0%.   In addition, closing costs on the new house are equal to 4.0% of the house price.

The second person lives in his second house for eight more years.

How much equity do the two people have after 15 years?






Question Thirteen involves the net present value of the tax deduction for mortgage interest for two types of mortgages.



Question Thirteen looks at the Net Present Value of different mortgages.


 Question Thirteen: What is the net present value of the mortgage tax deduction for a 15-year FRM and a 30-year FRM?


The interest rate on the 30-year FRM is 3.84%
The interest rate on the 15-year FRM is 4.5%. 
The marginal tax rate is 30%.


Question Fourteen involves the impact of sequential market risk on 401(k) assets.   It shows the timing of returns impacts final wealth.


Question Fourteen:  Consider a person age 50 with 15 years to go prior to retirement.   The person has $200,000 in his 401(k) plan.    The person makes $80,000 a year and invests 10% of her salary in ongoing biweekly contributions.

What is the final balance in the 401(k) plan if bi-weekly returns are 0.06/26 for the first 10 years and -0.07/26 for the last five years?

What is the final balance in the 401(k) plan if bi-weekly returns are -0.07/26 for the first five years and 0.06/26 for the next 10 years?

Observe the average return over the two time periods is identical.  However, the sequence of returns differs.  

Does the sequence of returns impact the 401(k) balance?   Why?





The final question consider whether it makes economic sense to buy a Toyota Prius instead of a Toyota Corolla.


Question Fifteen: 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?






Excel is a powerful tool.   I hope these finance math problems help people learn how to use it.  




No comments:

Post a Comment