## Using Excel to Calculate Mortgage Qualification Amounts

The attached spreadsheet shows how to use an Excel spreadsheet to calculate the amount of mortgage a person with student or consumer debt might qualify for.   One scenario estimates the impact of increasing maturity of student loan from 10 years to 20 years on increase in potential mortgage and cost to student borrower.

Examples of solving for mortgage qualification amounts in Excel.

qualification-for-house-by-student-borrower (3)

## Using contingency tables to measure systematic risk.

Contingency Tables Versus Beta

Question:  Below are two contingency tables describing the relationship between daily stock prices and daily changes in the S&P 500 for two companies.   One of the companies is a high-beta firm.   The other company a low-beta firm.

 Movements in Stock Price Versus S&P Company One Stock ClosePrior High S&P Close < Prior Low 12 18 4 S&P Between Prior Low & High 26 80 28 S&P > Prior High 5 31 48

 Movements in Stock Price Versus S&P Company Two Stock ClosePrior High S&P Close < Prior Low 10 21 3 S&P Between Prior Low & High 19 68 47 S&P > Prior High 14 48 22

The rows of the contingency table were formed by comparing the S&P close to the value of the low or high of the S&P on the previous market day.

The columns of the contingency table were formed by comparing the closing stock price to the low or high of the stock price on the previous day.

What table depicts the high beta stock?    What table depicts the low beta stock?  Defend your answer?

Use Stata to calculate Kendalls Tau B and Spearman’s rank correlation coefficient for the two firms.

How do these non-parametric statistics differ for these two firms?

Discuss implications of these results for future research.

Analysis:

The count of observations on the diagonal of the contingency tables starting from the top right cell to the bottom left cell of the table represent the number of instances where stock prices move in tandem with the market.

The proportion of observations on this diagonal is 55.6% for company one compared to 39.7% for company 2.

Beta is the most common measure of the movement of stock prices with the market so it is relatively clear that company one has a higher beta than company two.

In fact, company one is Apple and company two is Duke Power.   According to yahoo finance, the beta for Apple is 1.40 and the beta for Duke Power is 0.01.   The values of the proportions of observations on the diagonal of the contingency table appear to correspond to yahoo finance beta estimates.   (Sample size of two is admittedly very small.)

Kendall’s Tau and Spearman’s rho are two statistics commonly used to measure the association between row and column variables of a contingency table.    The table below lists these two statistics for these two companies

 Comparison of Non-Parametric Measures of Association Between Stock Price Movements and Market Movements Apple Duke Ratio Apple to Duke Kendall’s Tau 0.3672 0.0247 14.9 Spearman’s Rho 0.3980 0.0658 6.0

Concluding Thoughts:   Financial analysts use beta to measure the price of a stock.   The preliminary results presented here indicate that contingency tables and non-parametric statistics can be used to measure the association between movements company stock price and the overall market.   More research will be available on this topic shortly.

Authors Note:   I am planning the launch of a monthly financial and economics newsletter.  Please follow this blog to obtain advanced notice of this product.