Explore BrainMass

Calculating Regression using Excel for a Mortgage Department

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 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 ...

Solution Summary

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