Tuesday, November 17, 2015

Question Eight: Impact of student loan and mortgage interest rates on amount of mortgage a person can qualify for.

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%?




Answer:   First, I find the maximum allowable monthly mortgage payment.   Second, I find the mortgage balance consistent with this monthly payment given current interest rates and the term of the loan.

The maximum allowable monthly mortgage payment is the minimum of 28% of monthly income or 38% of monthly income minus monthly consumer debt payments.

In this problem the only consumer loan is the student loan.

Note that when the student loan term is 10 years the binding constraint is monthly payments less than 38% of monthly income.   However, when the student loan term is 20 years the binding constraint is mortgage payment less than 28% of monthly income.  

The allowable mortgage is the present value of the allowable maximum monthly payment.   The PV function depends on the interest rate, the term of the loan, and the payment.

 Note that PV(0.045/12,360,-$2079)= $410,406

Note also that PV(0.045/12,360,$2217) = $437.484.

The conversion of the student loan from a term of 10 years to a term of 20 years allows this person to qualify for around $27,000 more in mortgage.

The spreadsheet is set up so it is easy to examine how different mortgage or student loan details impact the allowable mortgage.   The problem asks us how to consider how a 10% change in student loan rates or a 10% change in mortgage rates would impact the allowable mortgage.

Impact of a 10% increase in student loan rates:


·      The allowable mortgage for the person with a 10-year student loan fell by around $6,000 after the student loan interest rate was increased by 10%.

·      The allowable mortgage for the person with a 20-year student was unchanged in response to a 10% increase in the student loan rate.   This occurred because even at the higher student loan interest rate the requirement that mortgage debt be no more than 28% of income remained the binding constraint.



Impact of a 10% increase in Mortgage Rates:

·      A 10% increase in mortgages results in a 5.1% decrease in the allowable mortgage for both the person with a 10-year student loan and a 20-year student loan.  This result makes sense since the mortgage debt constraint is the binding constraint on how much this person can borrow.
Calculation of Maximum Allowable Mortgage
Student Loan interest Rate
0.07
0.07
Assumption
Student Loan Term
120
240
Assumption
Student Loan Amount
$80,000
$80,000
Assumption
Payment on Student Loan
-$929
-$620.24
Calculation from PMT function
Annual Income
$95,000
$95,000
Assumption
Monthly Income
$7,917
$7,917
Calculation
28% of Monthly Income
$2,217
$2,217
Calculation: This is the first constraint
38% of Monthly Income - Consumer Debt Payments
$2,079
$2,388
Calculation: This is the second constraint
Maximum allowable monthly mortgage payment
-$2,079
-$2,217
Calculation:   The maximum allowable monthly mortgage payment is the smaller of the amount allowable from the two constraints.
Mortgage Interest Rate
0.045
0.045
Assumption
Mortgage Term
360
360
Assumption
Allowable Mortgage
$410,406
$437,484
Calculation:  From PV function

Other financial math problems can be found here.
http://financememos.blogspot.com/p/financial-math-in-excel.html


I am also doing work comparing costs of schools and salaries of future graduates of schools.  The post below looks at some data for Big Ten schools.


http://policymemos.blogspot.com/2016/05/information-on-big-ten-schools-from.html


It might be useful to combine information about median salaries and median debts from different schools to figure the maximum amount of house that a typical graduate from each school could purchase.   The data on student debt is somewhat limited because it does not include private loans or PLUS loans taken out by parents.   Despite these limitations more on the nexus between housing markets and the cost of college will follow.

No comments:

Post a Comment