A House Equity and Mortgage Payoff Spreadsheet

A House Equity and Mortgage Payoff Spreadsheet:

Question:   A person buys a house and plans to either sell and move or pay off the mortgage in twelve years.

The person is considering taking out a 15-year or a 30-year fixed rate mortgage.

The assumptions on the home purchase, house equity growth, the cost of selling and moving, and the cost of funds for the payoff of the mortgage are presented in the table below.

Table One: Assumptions for 30-year vs 15-year FRM Comparison:

Label 30-year FRM 15-year FRM
Purchase Price of House $500,000 $500,000
Down payment percentage 0.9 0.9
Initial Loan Balance $450,000 $450,000
Mortgage Term 30 15
House appreciation rate 3.0% 3.0%
Mortgage Interest Rate 4.0% 3.3%
Years person owns house 12.00 12.00
Cost of selling and moving to a new home as % of house value 9.0% 9.0%
Tax Rate on Disbursements from 401(K) Plan 30.0% 30.0%

 

Create a spreadsheet that provides estimates of house equity after the sale and move or mortgage payoff amounts after twelve years when the house buyer uses a 30-year FRM and when the house buyer uses a 15-year FRM

Base your mortgage payoff calculation on the assumption that the source of funds for the mortgage payoff are fully taxed funds from a 401(k) plan.

Spreadsheet:

http://wp.me/a2WYXD-4i

 

 

Results:

The results for the comparison of the 15-year and 30-year FRM for the assumptions presented in table one are presented in Table 2.

Table Two: Results for the 30-year vs 15-year FRM Comparison:

 

30-year FRM 15-year FRM
House Equity after Selling and Moving Costs $318,303 $540,109
Forecasted Mortgage Payoff Amount -$472,025 -$155,160

 

Observations on the 30-year vs 15-year FRM comparison:

The person taking out the 15-year FRM mortgage has around $222,000 more in house equity at the end of the 12-year holding period.

The mortgage payoff calculation when funds are disbursed from a 401(k) plan includes tax on the disbursements.   Inclusive of the tax bill, the mortgage payoff amount is $317,000 higher for the buyer who uses the 30-year FRM than for the buyer who uses the 15-year FRM.

Other Applications for the House Equity or Mortgage Payoff Spreadsheet:

 Modify the mortgage payoff calculation to allow for a situation where funds for the mortgage payoff are obtained from three sources – (1) a savings account, (2) sales of common stock, and (3) disbursements from a 401(k) plan.   Treat tax rates as an endogenous variable in the new model.

Compare results for both mortgage types under the 90% LTV assumption to results under an 80% LTV assumption.

Run the model on 15-year and 30-year FRMs for holding periods ranging from 1 to 15 years.   How does the advantage of the 15-year FRM vary with holding period?

Authors Note:   This problem was discussed further in the post below.

Essay Nine: Retire Mortgage Debt or Accumulate in Your 401(k) Plan:

https://financememos.com/2015/10/09/essay-nine-retire-mortgage-debt-or-accumulate-in-your-401k-plan/

Essay nine points out that many financial advisors stress accumulation of wealth in 401(k) plans rather than mortgage balance reductions even when their clients are nearing retirement.  The major banks employing the same financial advisors issue mortgages and sponsor 401(k) plans.   As a result, the interests of the financial advisors and the interests of their clients are not automatically aligned.

This approach can backfire when stock markets underperform nearing retirement.

During working years. the tax code favors people with large mortgages and people who are contributing to their 401(k) plan.  However, after retirement the person who must disburse funds from a 401(k) plan often has a hefty tax bill.

 

 

How to correctly calculate portfolio PE ratios?

 

How to correctly calculate portfolio PE ratios?

Question:   Two analysts are given the task of calculating the PE ratio of the DOW using the data below.   (Note:  Data on one firm is missing because of a recent merger.)

The first analyst uses method one and takes the ratio of the weighted average of market caps for the 29 companies to the ratio of earnings for the 29 companies.

The second analyst uses method two and takes the weighted average of the PE ratios of the 29 stocks.

What is the correct way to calculate the PE ratio for this portfolio?

What are the ramifications of using the wrong method to calculate the PE ratio for this portfolio?

The Data:

Financial Information on Stocks In The Dow
Share of Dow Market Cap ($B) Trailing Earnings ($B) Trailing PE
MMM 0.0645 124.9 5.2 23.87
AXP 0.0278 79.8 4.3 18.5
AAPL 0.0474 793.6 45.5 17.44
BA 0.0781 149.7 6.7 22.18
CAT 0.0383 73.7 0.1 696.65
CVX 0.0361 222.6 5.8 38.1
CSCO 0.0103 166.5 9.4 17.7
KO 0.0138 192.0 4.0 47.5
DIS 0.0303 152.1 8.7 17.48
XOM 0.0252 347.4 11.7 29.6
GE 0.0074 209.4 7.1 29.45
GS 0.0729 92.1 7.4 12.44
HD 0.0503 192.8 8.2 23.5
IBM 0.0446 135.2 11.3 12
INTC 0.0117 178.9 12.3 14.5
JNJ 0.0400 348.9 15.9 22
JPM 0.0294 336.1 23.8 14.1
MCD 0.0482 126.9 4.9 25.7
MRK 0.0197 174.6 5.0 34.7
MSFT 0.0229 573.7 20.9 27.5
NKE 0.0159 85.1 4.1 20.7
PFE 0.0110 212.3 8.1 26.1
PG 0.0280 232.0 14.2 16.3
TRV 0.0377 33.8 2.8 12.2
UTX 0.0357 92.7 5.2 17.7
UNH 0.0602 189.4 8.1 23.5
VZ 0.0152 201.9 15.9 12.7
V 0.0323 240.7 6.2 39.1
WMT 0.0240 233.4 12.4 18.8

Methodological Note:  Assume the columns of your spreadsheet are – (1) Share of DOW in A, (2) Market Cap in B, Trailing Earnings in C, and Trailing PE in D.

Also assume there are 29 rows, 1 to 29 for each variable.

The formula for method one is =SUMPRODUCT(a1:a29,b1:b29)/SUMPRODUCT(a1:a29,c1:c29)

The formula for method two is

=SUMPRODUCT(a1:a29,d1:d29)

Analysis:   The DOW PE ratio for method one is 20.5, a pretty high number compared to the historic norm of PE ratios for this index.

The DOW PE ratio for method two is 46.7, a number that is implausible for the portfolio of DOW stocks

Market Cap Weighted Total 203.4
Earings Weighted Total 9.9
Dow PE Ratio Method One 20.5
DOW PE Ratio Method Two 46.7

The PE ratio of one company in the DOW, CAT is 696, an extreme outlier.  This outlier drives up the weighted average of the PE ratios by a lot.

It is inappropriate to take the average of PE ratios because often a PE ratio for a particular company is an outlier or is below zero.

PE ratios below zero are economically meaningless.    For a discussion of how to calculate the PE ratio of a portfolio when some stocks in the portfolio have negative earnings go to the following site.

PE Ratios When Some Firms Have Negative Earnings

http://www.dailymathproblem.com/2017/05/price-earning-ratios-for-portfolios.html

Many analysts deal with the issues of negative or outlier PE ratios by dropping firms from their analysis.     There is no need to drop firms when you calculate a portfolio PE ratio if you are using an appropriate method.

Evaluating Fund Performance

Evaluating Fund Performance

Investment funds, both ETFs and mutual funds, are usually compared on the basis of returns of arbitrarily selected holding periods.   Typically, the fund manager reports year-to-date returns and return for one, three, five, and ten years.  The discussion of fund risk is usually based on a subjective assessment of the risk of the assets in the fund.

The conventional approach to presenting statistics on fund performance is inadequate.   Funds can be purchased at any time, not just a few arbitrarily selected dates.   This post measures the mean and standard deviation of return for two popular funds when there are multiple possible purchase and sale dates for each fund.

Statistical tests are used to evaluate whether the observed difference in return and risk outcomes for two funds are statistically significant.

Question:   This post considers two of Vanguards most successful funds.  VFIAX is a fund that mimics the S&P 500 and VWELX a fund that is around 70% equity and 30% fixed income.

The 48 potential purchase dates for both of the two funds are the first day of each month starting in January 2002 and ending in December of 2005.

The 48 potential sale dates for the two funds are the first day of each month starting in January 2012 and ending in December of 2015.

  • Assume that each combination of purchase and sale dates is equally likely.
  • What are the expected return and the standard deviation of return for both funds?
  • What are the minimum and maximum returns for each fund?
  • Can we reject the hypothesis of identical variances for the two funds?
  • Can we reject the hypothesis the mean returns are identical?

Analysis:

There are 2304 (48 x 48) possible (purchase-sale) outcomes.  For each of these outcomes I calculate ln(AP2/AP1) where AP2 is the adjusted price in the 2012 to 2015 time period and AP1 is adjusted price in the 2002 to 2005 time period.

The mean standard deviation, minimum, and maximum for the two funds are presented below.

Returns from Two Funds
Fund Description Mean Standard Deviation Minimum Maximum
VWELX Stocks and bonds 0.763 0.177 0.341 1.14
VFIAX Stocks 0.692 0.226 0.178 1.21

Sample size 2304 based on 48 possible purchase dates between 2002 and 2005 and 48 possible sale dates 2012 and 2015.

Observations:

  • The mean return of the bond/stock fund is higher than the mean return of the stock-only fund by around 10 percent.
  • The standard deviation of returns for the bond/stock fund is lower than the standard deviation of returns for the stock-only fund by around 21 percent.
  • The maximum return is higher for the stock-only fund by around 92 percent.
  • The minimum return is lower for the stock-only fund by around 6 percent.

Comments:

Comment One:  The finding that the combined stock/bond fund has a larger mean return compared to the stock-only fund is extremely unusual because over long periods stocks tend to have higher returns than bonds.    However, the stock portfolios of the two funds differ.  The stock portfolio in VWELX is broadly diversified but does not track a specific index.   The stock portfolio in VFIAX tracks the S&P 500.  VWELX was able to get higher returns than VFIAX because its stock portfolio outperformed the S&P 500 while the bond portfolio lowered risk.   It also did not hurt that interest rates fell and bond prices rose in this time period.

Comment Two:  The stock-only portfolio was much more risky than the combined bond-stock portfolio.   This is evidenced both by the lower standard deviation and the higher minimum return.  The minimum return statistic measures the worst-outcome return.  The worst-outcome return for the combined stock-bond portfolio is around 92 percent higher than the worst-outcome return for the stock-only portfolio.

Tests of equal variances for returns:

A test of the hypothesis that the variances of return for the two portfolios are equal was conducted.   The F-statistic comparing the ratio of the two standard deviations was 1.63, which is significantly different from 1.0.    The hypothesis that the two variances are identical is rejected.

Tests of equal mean returns:

A test of the hypothesis that the mean returns for the two portfolios are equal was conducted.  The t-statistic for this hypothesis test was 12.9.   The hypothesis of identical means is rejected.

Technical Note:  I used STATA to make the calculations in this note.  Period one and period two data were placed in separate data sets.   The N to N merge provides the 2304 outcomes.

Concluding Thought:  The practice of presenting return numbers on investment funds for a few arbitrarily chosen holding periods is, in my view, not very useful.    The holding periods are arbitrary and subject to manipulation.   There is no measure of risk.

The technique presented here relies on many possible outcomes defined by different purchase and sale dates.   The multiple outcome approach allows for the presentation of risk measures.

The note shows that the performance of the VWELX fund was exceptional in this period.

A Note on Use of Geometric and Arithmetic Averages of Daily Stock Returns

Question:  The table below has price data and daily return data for Vanguard fund VB.   Calculate the arithmetic and geometric averages of the daily return data.   Show that the geometric average accurately reflects the relationship between the initial and final stock price and the arithmetic average does not accurately explain this relationship.

Daily Price and Returns For Vanguard 

Fund VB

Date Adjusted Close Daily Return
7/1/16 115.480674
7/5/16 113.99773 0.987158509
7/6/16 114.744179 1.006547929
7/7/16 114.913373 1.001474532
7/8/16 117.202487 1.019920345
7/11/16 118.128084 1.007897418
7/12/16 119.451781 1.011205608
7/13/16 119.10344 0.997083836
7/14/16 119.262686 1.001337039
7/15/16 119.402023 1.00116832
7/18/16 119.63093 1.001917112
7/19/16 119.202965 0.996422622
7/20/16 119.959369 1.006345513
7/21/16 119.481646 0.996017627
7/22/16 120.297763 1.00683048
7/25/16 120.019083 0.997683415
7/26/16 120.616248 1.004975584
7/27/16 120.347522 0.997772058
7/28/16 120.536625 1.001571308
7/29/16 120.894921 1.002972507
8/1/16 120.735675 0.998682773
8/2/16 119.12335 0.986645828

Analysis:   The table below presents calculation of the two averages and the count of return days.  The product of the initial value of the ETF, the pertinent average and the count of return days is the estimate of the final value.   Estimates of final ETF value are calculated for both the arithmetic average and the geometric average and these estimates are compared to the actual value of the stock on the final day in the period.

Understanding The Difference Between Arithmetic Mean and Geometric Mean Returns
Statistic Value Note
Arithmetic Average of Daily Stock Change Ratio 1.001506208 Average function
Geometric Average of Daily Stock Change Ratio 1.001479966 Geomean function
Count of Return Days 21 Count Function
Estimate of final value based on arithmetic average 119.1889153 Initial Value x Arithmetic Return Average x Count Days
Estimate of final value based on geometric average 119.12335 Initial Value X Geometric Return Average x Count Days
Ending Value 119.12335 Copy from data table

There is another way to show that the daily return should be modeled with the geometric mean rather than arithmetic mean.  The average daily return of the stock is (FV/IV)(1/n) – 1 where FV is final value and IV is initial value and n is the number of market days in the period, which for this problem is 21.

Using this formula we find the daily average holding period return is 0.001479966.  Note that 1 minus the geometric mean of the daily stock price ratio is also 0.001479966.

The geometric mean gives us the correct holding period return.

Expected Profit and Risk with Random Transaction Dates

Profit and risk when there are four random purchase dates and four random sale dates

Question:   In 2013 a person buys QQQ the high tech ETF) on one of four randomly selected dates determined by when the broker arranges a meeting.   I

The person who bought the QQQ shares in 2014 got fired in 2015.   As soon as the person was fired he realized he needed cash so he called his broker and said “SELL QQQ” The firing is a random event independent of the market and out of control of the person, which occurred on one of four dates.

The four potential purchase and four potential sales dates for the QQQ transactions are presented below.

Information on Potential Purchases and Sales of QQQ
Potential Purchase Date Purchase Price QQQ Quantity purchased $25,000/Price Potential Sale Date Sale Price
20-May-14 88.0 284.1 5-Jan-15 101.4
7-Jul-14 95.1 262.9 8-Aug-15 110.5
7-Aug-14 94.2 265.4 24-Aug-15 98.5
10-Sep-14 100.1 249.8 5-Nov-15 114.7

The person spends $25,000 on the purchase of QQQ in 2014 and sells all shares in 2015.

Assume no dividends are paid.

What are all possible profit outcomes from the purchase and sale of the QQQ securities?

What is the expected profit?

What is the variance of profit?

Analysis:  The number of share purchased is $25,000 divided by the purchase price; hence the purchase price determines the number of shares purchased.

Tabulation of Number of Shares Purchased
Potential Purchase Date Purchase Price QQQ Number of shares purchased
20-May-14 88.0 284.1
7-Jul-14 95.1 262.9
7-Aug-14 94.2 265.4
10-Sep-14 100.1 249.8

Revenue received after the sale is price at time of sale times the number of shares owned.

Profit after the sale is revenue minus the $25,000 initial investment.

There are four possible purchase dates and four possible sale dates.   The purchase and sale dates are independent so there are a total of 16 possible equally likely combinations of sale and purchase dates.   The probability of each purchase/sale combination is 0.0625 (0.25*0.25).

The profit calculation for the 16 purchase-sale combinations is presented in the table below.

Potential Profit Calculation for Four Purchase Dates and Four Sale Dates
Comb # Probability Purchase Date Sale Date Number of Shares Owned Sale Price Profit
1 0.0625 20-May-14 5-Jan-15 284.1 101.4 $3,807
2 0.0625 20-May-14 8-Aug-15 284.1 100.5 $3,552
3 0.0625 20-May-14 24-Aug-15 284.1 98.5 $2,984
4 0.0625 20-May-14 5-Nov-15 284.1 114.7 $7,586
5 0.0625 7-Jul-14 5-Jan-15 262.9 101.4 $1,656
6 0.0625 7-Jul-14 8-Aug-15 262.9 100.5 $1,420
7 0.0625 7-Jul-14 24-Aug-15 262.9 98.5 $894
8 0.0625 7-Jul-14 5-Nov-15 262.9 114.7 $5,152
9 0.0625 7-Aug-14 5-Jan-15 265.4 101.4 $1,911
10 0.0625 7-Aug-14 8-Aug-15 265.4 100.5 $1,672
11 0.0625 7-Aug-14 24-Aug-15 265.4 98.5 $1,141
12 0.0625 7-Aug-14 5-Nov-15 265.4 114.7 $5,441
13 0.0625 10-Sep-14 5-Jan-15 249.8 101.4 $325
14 0.0625 10-Sep-14 8-Aug-15 249.8 100.5 $100
15 0.0625 10-Sep-14 24-Aug-15 249.8 98.5 -$400
16 0.0625 10-Sep-14 5-Nov-15 249.8 114.7 $3,646
Min -$400
Max $7,586
Range $7,986

The minimum profit is -$400.   The maximum profit is $7,985.

The expected profit is obtained by taking the dot product or the sumproduct of the probability vector with the profit vector.   The variance was obtained from the computational formula.

Var (Profit) = E(profit2) – E(Profit)2

For a discussion of these calculations see the previous post.

http://dailymathproblem.blogspot.com/2015/11/expected-value-and-variance-of-share.html

The expected value and variance or profit from the purchase of QQQ on one of four dates in 2014 and the sale of QQQ on one of four dates in 2015 are presented below.

Expected Profit and Variance of Profit Calculations
E(PROFIT) 2555.4
E(PROFIT2) 11036765.0
E(PROFIT2)-E(PROFIT)2 4506556.2
E(PROFIT-E(PROFIT))2 4506556.2

Financial Discussion:

The purchaser of QQQ or any stock that buys randomly and is forced to sell because of random events unrelated to the market bears substantial risk compared to an investor with enough liquid assets who will not need to sell in an emergency.   Investors would be wise to consider the level of the market and their ability to hold through downturns prior to selling.  The experts say that stock market returns beat returns on other securities over the long haul.  But this investor was only able to hold for a year.

Outcomes could have been worse.   The broker put the investor in QQQ a relatively diversified ETF that focuses on tech stocks.  Had the broker put his client in one particular stock (say IBM) and the investor was forced to sell he would have realized a large loss.

Measuring Returns for Different Investment-Consumption Patterns

Measuring Returns for Different Investment-Consumption Patterns

Question:   An investment advisor tells his client to invest $1,000 per month in VFIAX (Vanguard S&P fund) for five years.   The person will then live off the proceeds in this fund for 36 consecutive months.

Calculate the return on assets from this investment/consumption plan for two different start dates – January 1, 2002 and January 1, 2003.

What is the NPV of investment returns from this investment strategy/ consumption plan on the same start dates?

What should investors who are planning to save for five years and spend for three years learn from this example?

Mutual funds and ETFs tend to advertise holding period returns based on specific purchase dates and specific sale dates.   These returns are based on the price of securities on two dates only.   What does the example presented here tell you about the usefulness of two-period return statistics reported by mutual funds?

Methodological Note:  The shares purchased each month are $1,000/PVFIAX where PVFIAX is the price of the ETF.   I sum over 60 months to get the total shares purchased, which I will denote TSHARES. The formula for cash inflow for the 36 months are (1/36)*TSHARE*PVFIAX.

The cash inflow/outflow column and the date column are inputted into the XIRR function in Excel to give the IRR of the inflows/outflows on these particular dates. The XNPV function gives net present value of the cash flows.

Analysis:

The value of VFIAX reached its pre financial crisis high in 10/2007 and reached its crisis trough in 02/2009.   Hindsight is 20/20 but it appears as though diversification prior to the downturn would have been beneficial.

What follows are return calculations for the two scenarios.

Results are in the table below.

Returns for Two Investment/Consumption Scenarios
Invest Period Consumption Period IRR NPV
2002/2006 2007/2009 12.04 $15,766
2003/2007 2008/2010 2.98*e-9 $801

Observations:

  • The person who stopped saving in December 2006 did fairly well despite the financial crisis.The IRR for this investor was 12.04 %.   The NPV of the investments was $15,766.   (NPV calculation assumes a5 percent cost of capital.) 
  • The person who stopped investing in December 2007 realized a return only slightly higher than 0 percent.The NPV of this person’s investment was around $800.

Discussion of Investment Strategy:

In my view, a 100 percent VFIAX strategy is unwise for an investor with this type of investment and consumption period.

How to fix this problem is a more difficult question.  It is important to note that the strategy of putting 100 percent of funds in VFIA for an investor with a start date of January 1 2009 or January 1, 2010 did quite well.

529 plans offer life-cycle funds that drift towards a more conservative investment as the person nears the date where he must spend money.   Lifecycle funds would have done reasonably well for both of the scenarios considered here.  However, the life-cycle approach creates miserable results when the market does poorly in the first few years of the investment period and then rebounds.

My view on how to solve this problem is evolving.  A 60/40 (stock/bond) portfolio would have done well in these time periods but I don’t believe that it will work in the next crash.  Interest rates are now very low and I expect in the next crisis bonds and stocks will crash together.   Perhaps allocating some resources into an inflation-indexed bond fund would help balance returns during the next crisis.

The trend in investment is toward investment in passively managed funds like the ones offered by Vanguard.    This is at best a partial solution.   Investors need help in allocating money across several passively managed funds.  This includes advice on initial allocations and reallocation over time.

I believe there is a need for an actively managed fund that invests exclusively in passively managed funds and reallocated assets across funds as market conditions change.

Note on traditional holding period statistics:  The value of VFIAX in January 2002 was 17.9.  In December of 2010 the value of VFIAX was 39.5.   The return for this 7.9 year  holding period was at 10.5%.

Holding Period Calculation
Jan-03 17.9
Dec-10 39.5
Holding Period in Years 7.92
ROR 10.5%

However a person who started investing in January 2003 and started spending in January 2008 earned squat!   

The mutual funds can legally and honestly report great eight-year or ten-year holding return but their clients aren’t doing particularly well.

Such a surprise!   

How much house can a student borrower qualify for?

How much house can a student borrower qualify for?

This answer depends on the maturity of the student loan.

Situation:

Consider a person with a $100,000 student debt.

  • The person can either pay the debt back over a 10-year period or a 20-year period.
  • The student loan is this person’s only consumer debt.
  •  The person earns $80,000 per year.
  • The student loan interest rate is 7.0 percent.
  • The mortgage interest rate is 4.0 percent.
  • The mortgage term is 30 years.

Questions:

  • How much mortgage can the person qualify for if the person keeps the student loan at 10 years?
  • How much mortgage can the person qualify for if the person changes the student loan term to 20 years?
  • What is the increased cost of the student loan payments involved by switching from a 10-year to 20-year student loan?

Answer:   I developed a spreadsheet that calculates the maximum allowable mortgage this person can qualify for.

In order to qualify for a mortgage two conditions must hold.

  • Monthly mortgage payments must be less than 28% of income.
  • Monthly mortgage and consumer loan payments must be less than 38% of income.

The procedure used to calculate the allowable mortgage is as follows:

  • First, I calculate the maximum allowable mortgage payment based on zero consumer debt.   This value is 28 percent of monthly income.
  • Second, I calculate the maximum allowable mortgage payment consistent with mortgage payments and consumer debt payments equal to 38 percent of income.   This is done by backing out the student loan and allocating the rest to mortgage debt.
  • Third, I insert mortgage interest rate, term and payment info into the PV functions to get the mortgage amount
  • Fourth, The allowable mortgage is the minimum of the mortgage totals consistent with the two constraints.

The calculations for the two situations presented in this problem are presented in the table below

Mortgage Qualification Example for Borrower with Student Debt
row # Student Loan Information Note
1 Student loan Amount $100,000 $100,000 Assumption
2 Interest Rate 0.07 0.07 Assumption
3 Number of Payments 120 240 Assumption
4 Student Loan Payment $1,161 $775 From  PMT Function
Mortgage Information
5 Rate 0.035 0.035
6 Term 360 360
Income Assumption
7 Income $80,000 $80,000 Assumption
8 Constraint One:  Maximum monthly mortgage payment consistent with this income assumption $1,867 $1,867 28% of monthly income
9 Constraint Two:  Maximum monthly consumer and mortgage payments consistent with income $2,533 $2,533 38% of monthly income
10 Maximum mortgage consistent with constraint one. $415,697 $415,697 pv of mortgage rate number of periods, and pmt where mortgage rate and payments are assumptions baed on the market and product chosen and payment is max allowable given   income
11 Allowable mortgage payment consistent with constraint two given required student debt $1,372 $1,758 Row 9 minus Row 7
12 Max mortgage consistent with borrowing contraint two. $305,593 $391,505 Use PV function with rate and term set by market and product and payment the amount of mortgage payment after required consumer payments
13 Allowable mortgage debt $305,593 $391,505 Minimum of Row 10 and Row 12

 

An increase in the term of the student loan from 10 to 20 years increases the size of a mortgage a household can qualify for from $305,000 to $391,000.

Getting the extra mortgage is not cheap.  The increased student loan term causes total student loan payments to go from $139.000 to $186,000.

Concluding thoughts:  Most people who have $100,000 in student debt will have to refinance the student loan if they are going to buy a house.

 

 

 

 

 

 

 

 

 

 

Student debt and qualifying for a mortgage

Student debt and qualifying for a mortgage  — post one

Excel Topics:  PMT function and Spreadsheet design

Question:  A person graduates from college and graduate school with $100,000 in student debt.   The interest rate on a 10-year student loan is 5% per year.   The person wants to buy a house that costs $300,000 with a 90% LTV loan. The home mortgage interest rate is 4.5% on a 30 year FRM.

Assume that in order to qualify for the house the person must meet two conditions.

Constraint One:  The ratio of mortgage interest to income must be less than 0.28.

Constraint Two: The ratio of total interest (mortgage and non-mortgage) interest must be less than 0.38.

How much income does this person need to qualify for a loan on this house?

Why might student debt have a smaller impact on the purchase of a $700,000 home than the purchase of a $300,000 home.

Analysis:   The analysis for the $300,000 home is laid out in the table below.

Mortgage Qualification Example for Borrower with Student Debt
Note
Student loan Amount $100,000 $0 Assumption
Interest Rate 0.05 0.05 Assumption
Number of Payments 120 120 Assumption
Student Loan Payment $1,061 $0 From  PMT Function
House Amount $300,000 $300,000 Assumption
LTV 0.9 0.9 Assumption
Loan Amount $270,000 $270,000 LTV * House Amount
Intrerest Rate 0.045 0.045 Assumption
Number of Payments 360 360 Assumption
Mortgage Payment $1,368 $1,368 From PMT Function
Total Loan Payments $2,429 $1,368 Sum Payments
Monthly Income Constraint One $4,886 $4,886 Student Loan Payment divided by 0.28
Monthly Income Constraint Two $6,391 $3,600 Mortgate Payment Divided by 0.38
Required Monthly Income $6,391 $4,886 Max of income over both constraints
Required Annual Income $76,696 $58,631 12* Max Income

Observations Pertaining to the $300,000 home for a person with and without student loans

A person with no student debt could qualify for this mortgage with an annual income of $58,630.

The person with the student debt needs an annual income of $71,585.

The impact of student debt on purchases of a larger home:   The allowable mortgage is determined by two constraints one involving mortgage debt only and the other involving the sum of mortgage and consumer debt.   When the mortgage debt is very large, constraint one (the mortgage debt constraint) will be the binding constraint.

Download the student debt and mortgage qualification spreadsheet by clicking below:
Continue reading “Student debt and qualifying for a mortgage”

Is FANG overvalued?

Is FANG Overvalued?

Question:  The hot stock combo right now is FANG (Facebook, Amazon, Netflix and Google.)   What is the PE ratio of a portfolio equally weighted in these four stocks?

Provide an opinion about whether future earnings of prospects for FANG justify this valuation?

Calculation of the FANG PE ratio:

All FANG stocks have positive earning and positive PE ratios so it is appropriate to average PE ratios of the four stocks to obtain the PE ratio for the portfolio.  (I use a weight of 0.250 for each stock and SUMPRODUCT weight vector with PE ratio vector)

FANG STATISTICS
PE ratio Dividend EPS Price
Facebook 38.29 0 3.93 150.58
Amazon 183.95 0 5.31 976.78
Netflix 195.38 0 0.77 151.03
Google 31.34 0 29.59 927.33
FANG 112.24

This gives me a PE ratio of 112.24 for FANG.

It is instructive to compare the recent growth of EPS and stock price for the FANG companies

Comparison of growth of EPS and stock price for FANG Stocks
6/29/16 3/30/17 % Change
FB EPS 0.97 1.04 7.2%
Price 114.28 142.05 24.3%
AMZN EPS 1.78 1.48 -16.9%
Price 725.68 886.54 22.2%
NFLIX EPS 0.09 0.4 344.4%
Price 96.67 147.8 52.9%
GOOG EPS 8.42 7.73 -8.2%
Price 699.2 829.56 18.6%

Some Observations:

In three of the four companies the growth of stock prices exceeded the growth of earnings per share.

In two of the four companies, earnings fell while the stock prices rose.

In Netflix, the one company where EPS growth exceeded stock price, the initial period stock price was near zero.   The initial low level of earnings is why the PE ratio of Netflix is going down.   When earnings are near zero the PE ratio can be astronomic.

Concluding thoughts:  Basically FANG stock price is rising because of expectations of future growth not concrete earnings growth.   The one exception Netflix is because of really low earnings in a base period.    Netflix has done a great job but it is in a competitive industry and unlike the other three companies has no monopoly power.

These four companies will face challenges going forward.   The PE of Amazon seems especially high because there is going to be a lot of competition in the cloud sector a lot of costs integrating Whole foods and low margins in the on-line grocery business.

Investors who own FANG should consider taking some profits.

Featured Policy Blog Post:

I have created a list of 11 pragmatic progressive education policies.   These posts will be part of a book on economically feasible policies that might improve the world.   Here is a link to this important work.

http://policymemos.blogspot.com/p/education-policy-list.html

 

 

 

Are Tech Stocks Overvalued?

Are Tech Stocks Overvalued?

Issue:   Professor Jeremy Siegel maintains that the stock market and tech stocks are still fairly value.

http://www.cnbc.com/2017/05/26/were-not-in-the-danger-zone-for-overheating-says-longtime-stock-bull-jeremy-siegel.html

He supports this argument with the observation that the PE ratio of Tech stocks in the S&P 500 is still under 20.

What are the limitations of using the PE ratio for a basket of stocks to measure the valuation of the portfolio when some stocks in the portfolio have negative earnings?

Does an analysis of the PE ratios of the stocks in the Vanguard Information Technology ETF support or contradict Professor Siegel’s view on the valuation of Tech stocks?

Is Professor Siegel correct in his assertion that tech stocks are valued correctly?

Discussion of ETF PE Ratios: 

Professor Siegel pointing to a PE ratio for a basket of tech stocks in the S&P 500 has argued that the sector is valued fairly.   My problem with this argument is that published statistics on ETF PE ratios often fail to accurately include information on firms with negative earnings.

Firms with negative earnings have negative PE ratios.  These firms often have a lot in common with high PE firms.   Often startups have negative or low earnings.   If earnings are negative the PE is negative.  If earnings are slightly positive the PE is large.

It would be incorrect to average negative PE firms with positive PE firm because the result would be to reduce the PE of the portfolio even though the negative PE firms have high valuations compared to their income.     Some web sites including yahoo finance report and include negative PE ratios.   Most analysts omit negative PE ratios from their calculation of the portfolio PE.   However, this procedure will also understate valuation relative to income because firms with negative PE ratios have high valuation compared to earnings.

PE ratios have no clear economic interpretation when earnings are negative.  When earnings are slightly below zero (a small loss) the PE ratio is a very large negative number.   When a company has a larger loss the PE ratio is a smaller negative number.

Why PE ratios make no sense for firms

 with negative earnings

Earnings per share

Price per share PE ratio

(0.10)

3.00 (30.00)
(5.50) 3.00

(0.55)

In short, PE ratios incorrectly rank firm valuation when earnings are negative.

It would be incorrect to average negative PE firms with positive PE firm because the result would be to reduce the PE of the portfolio even though the negative PE firms have high valuations compared to their income.     Some web sites including yahoo finance report and include negative PE ratios.

Most analysts omit negative PE ratios from their calculation of the portfolio PE.   However, this procedure will also understate valuation relative to income because the firm with a negative PE ratio has a high valuation compared to earnings.

I am not the first to write about the problem of measuring ETF PE ratios.  Here are some additional resources.

Why ETF Price/Earning Ratios Lie.

http://www.etf.com/sections/blog/23121-why-etf-priceearnings-ratios-lie.html?nopaging=1

Understanding Negative PE Ratios for ETFs

http://www.etf.com/sections/blog/21801-understanding-negative-pe-ratios-for-etfs.html/page/0/1?fullart=1&start=2

Data used in this study:

I obtained a list of stocks in the VGT mutual funds from Zachs guide at the link below.

https://www.zacks.com/funds/etf/VGT/holding

Vanguard Technology Fund VGT has a total of 356 firms.  This study examined the PE ratios of all firms where the equity investment was greater than or equal to 0.1 percent of the value of the VGT portfolio.     There were 109 such firms.

Results:   The frequency distribution of dollar share values invested and number of firms for five PE categories – less than zero, 0 to 15, 15 to 30, 30 to 40 and over 40 – are presented below.

 

   Shares of Firms in VGT by PE category

PE Category

Dollar Share Invested by PE Category

Percent of Companies

<0 6.31 17.43
0-15 5.74 6.42
15-30 36.19 34.86
30-40 31.16 12.84
40< 13.12 28.44
Total 92.52 100

Sample consists firms in VGT where the equity position was greater than or equal to 0.1 percent of the total value of the VGT portfolio.   There were 109 firms meeting this criterion.   These 109 firms represent 92.5 percent of the value of the VGT Portfolio.

Observations:

Around 6.3 percent of dollars invested in the 109 positions of VGT are in firms with negative earnings.  Around 17.4 percent of the 109 firms had negative earnings.

Over 13 percent of dollars invested in the 109 VGT positions had PE ratios over 40.   Over 128 percent of the firms in this group had a PE ratio over 40.

Analysis:

 What can we conclude about the question of whether tech stocks are overvalued after examining the distribution of stocks in VGT?

The large number of tech stocks with high PE ratios or worse yet negative earnings is consistent with a bubble.   Perhaps the bubble is in the early stages and some people can buy, sell, and make money before the crash.   However, there are a lot of overtly optimistic analysts and a lot of inaccurate or misleading information out there.

This is not going to end well.