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
Purchase this Solution
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 ...
Purchase this Solution
Free BrainMass Quizzes
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.
Know Your Statistical Concepts
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.
Terms and Definitions for Statistics
This quiz covers basic terms and definitions of statistics.