Purchase Solution

# Calculating Regression using Excel for a Mortgage Department

Not what you're looking for?

A mortgage department of a large bank is studying its recent loans. Of particular interest is how such factors as the value of home (in thousands of dollars), education level of the head of household, current monthly mortgage payment (in dollars), and sex of the head of the household (male = 1, female = 2) relate to the family income. Are these variables effective predictors of the income of the household?

A random sample of 25 recent loans is obtained (attached).

a) Determine the regression equation
b) What is value of R2(squared)? Comment on the value.
c) Conduct a global hypothesis test to determine whether any of the independent variables are different from zero.
d) Conduct individual hypothesis tests to determine whether any of the independent variables can be dropped.
e) If variables can be dropped, recompute the regression equation and R2(squared).

Solve using Excel

##### Solution Summary

This posting will show you step by step how to calculate regressions using Excel.

##### Solution Preview

Hi there, I am attaching the excel sheet with the calculations

First, we assume that the Y is the income, and the other variables are the X. In this case, sex is a dummy variable, since it has a value of 0 or 1.

The model of the regression is as such:

y = alpha + b1x1(value) + b2x2 (years) + b3x3(age) + b4x4(mortgage)+v1x5(sex) + error

When I ran the problem on excel, we can then get the coefficients.

Intercept 28.24245398
X Variable 1 0.028669482
X Variable 2 0.649669027
X Variable 3 -0.048950332
X Variable 4 -0.000404882
X Variable 5 0.722659849

Hence, the regression equation is as such:

y = 28.242 + 0.0286x1 + 0.649x2 + -0.0489x3 + -.0004x4 + 0.7336x5

b) The R ...

##### Free BrainMass Quizzes

Each question is a choice-summary multiple choice question that presents you with a statistical concept and then 4 numbered statements. You must decide which (if any) of the numbered statements is/are true as they relate to the statistical concept.

##### Measures of Central Tendency

Tests knowledge of the three main measures of central tendency, including some simple calculation questions.

##### Measures of Central Tendency

This quiz evaluates the students understanding of the measures of central tendency seen in statistics. This quiz is specifically designed to incorporate the measures of central tendency as they relate to psychological research.

##### Terms and Definitions for Statistics

This quiz covers basic terms and definitions of statistics.