Explore BrainMass

Explore BrainMass

    Calculating Regression using Excel for a Mortgage Department

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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

    © BrainMass Inc. brainmass.com June 3, 2020, 4:48 pm ad1c9bdddf
    https://brainmass.com/statistics/regression-analysis/calculating-regression-excel-mortgage-department-4415

    Attachments

    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.

    $2.19

    ADVERTISEMENT