Exp22_Excel_Ch06_HOE - Mortgage | Exp22 Excel Ch06 HOE Mortgage | @Cengage HomeWork

Описание: Exp22_Excel_Ch06_HOE - Mortgage | Exp22 Excel Ch06 HOE Mortgage | @Cengage HomeWork


Contact us:

WhatsApp : +92 3260782026

WhatsApp Link:
https://bit.ly/3pIuXPl

Gmail: [email protected]

We can handle all online courses, Business and Management, Business and Finance, Business and Accounting, Statistics, Computer Science and Many more We will help you in weekly quizzes, assignments, exams, thesis writing, dissertation writing, MylTLab all Courses

Project Description:
After several years of living with friends after college, you have decided to purchase your first home. After doing some preliminary research on prices, you developed a spreadsheet to calculate your monthly mortgage payment, the total amount to repay the loan, and the total amount of interest you will pay. Your total budget for the home is $150,000 including taxes, closing costs, and other miscellaneous fees. You plan to take $10,000 out of your savings account for a down payment. You are currently investigating loan interest rates at various banks and credit unions. You realize that you may need to find a less expensive home or increase your down payment to reach a monthly payment you can afford. Although you know a larger down payment will change the monthly payment

Download and open the file named Exp19_Excel_C

@Cengage HomeWork
@Cengage HomeWork
@Cengage HomeWork

Create the range name PurchasePrice for cell B2 and DownPayment for cell B3.
You have decided to utilize an alternative method of creating range names for the remainder of the variables.

Create range names for the range A9:B13 using the create from selection method.
You would like to edit the previously created named ranges. To do so, you will use the Name Manager.

Delete the Monthly_Payment named range. Edit the DownPayment named range so it appears as Down_Payment and edit PurchasePrice to appear as Purchase_Price.
You would like to apply the newly created named ranges to the existing formulas in the worksheet.

Apply defined names to the existing formulas on the Home Loan worksheet. Once completed, named ranges should appear in the cells B12 and B14.
You would like to document the named ranges by inserting them as a list on a new worksheet.

Create a new worksheet named Range Names. Insert the previously created range names on the worksheet starting in cell A2. Set the width of columns A:B to AutoFit.
As you continue your analysis, you would like to create a one-variable data table to explore the impact of various interest rates on the payment, total interest, and total amount repaid. To complete the table, you will first create substitution values.

On the Home Loan sheet, beginning in cell D4, enter substitution values from 4% to 6% increasing in .25% increments, completing the range D4:D12.
To continue the construction of the one-variable data table, you will add formulas to the table structure.

Add a reference to the cell the contains the monthly payment (B12) in cell E3. Add a reference to the cell that contains the total to repay loan in cell F3 (B13), and add a reference to the cell that contains total interest paid (B14) in cell G3.
Complete the one-variable data table using the APR in cell B4 as the column input.
You would like to format the one variable data table to add clear labels that are easy to understand.

@Cengage HomeWork
@Cengage HomeWork
@Cengage HomeWork

Apply Accounting Number Format to the range E4:G12. Type APR in cell D3. Next, create the following custom formats for the range E3:G3.

Cell E3 custom format = Payment
Cell F3 custom format = Total Repaid
Cell G3 custom format = Total Interest
You would like to create a two-variable data table to explore the impact of changing interest rates and home costs on the monthly payment. To begin creating the structure of the table, you will enter substitution values for the home cost and interest rate.

Enter 150000, 175000, and 200000 in the range J3:L3. Format these values with Accounting Number Format with two decimals. Expand the column width as needed if pound signs (#) appear.

Start in cell I4. Complete the series of substitution values ranging from 4% to 8% in increments of .25% vertically down, stopping in cell I20.
Enter a reference to the monthly payment (B12) in cell I3 and complete the two-variable data table using the cost of the home (cell B2) as the Row Input and the APR (cell B4) as the Column Input.
As your last step, you would like to create a custom number format for cell I3.

In cell I3, apply a Custom number format to display APR. Center and bold the contents in cell I3.
To continue your analysis, you will use Goal seek to determine the optimal purchase price to obtain the desired monthly payment of $600 without changing the down payment or APR.

Use Goal Seek to set the monthly payment (cell B12) to $600 by changing the purchase price (cell B2).