Shelly Cashman Excel 2019 Module 4: SAM Project 1b Ramos Family Home Purchase CREATE A LOAN ANALYSIS   GETTING STARTED Open the file SC_EX19_4b_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

Shelly Cashman Excel 2019 Module 4: SAM Project 1b

Ramos Family Home Purchase

CREATE A LOAN ANALYSIS

 

GETTING STARTED

• Open the file SC_EX19_4b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_4b_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX19_4b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Ricardo and Miranda Ramos are considering whether to buy their first home, and have spoken to three lenders about taking out a mortgage for the house purchase. They now pay $1,500 per month in rent and can pay up to $1,600 per month for a mortgage. Miranda has created an Excel spreadsheet to compare the terms of the mortgage. She asks you to help her complete the analysis of their loan options.
   
   Go to the Mortgage Calculator worksheet. The cells in the range B5:B7 have defined names, but one is incomplete and could be confusing. Cell A2 also has a defined name, which is unnecessary for a cell that will not be used in a formula.
   
   Update the defined names in the worksheet as follows:
   1. Delete the Loan_Payment_Calculator defined name.
   2. For cell B7, edit the defined name to use Loan_Amt as the name. [Mac Hint: Delete the existing defined name "Loan_Am" and add the new defined name.]
2. In cell B7, calculate the loan amount by entering a formula without using a function that subtracts the Down_Payment from the Price.
3. Miranda also wants to use defined names in other calculations to help her interpret the formulas.
   
   In the range D3:D7, create defined names based on the values in the range C3:C7.
4. Miranda wants to calculate the monthly payment for a loan to purchase the Beecher Street house at the most favorable interest rate she found online. Calculate the payment as follows:
   1. In cell D5, start to enter a formula using the PMT function.
   2. For the rate argument, divide the Rate (cell D3) by 12 to use the monthly interest rate.
   3. For the nper argument, use the Term_in_Months (cell D4) to specify the number of periods.
   4. For the pv argument, use the Loan_Amt (cell B7) to include the present value.
   5. Insert a negative sign (-) after the equal sign in the formula to display the result as a positive amount.
5. In cell D6, enter a formula without using a function that multiples the Monthly_Payment (cell D5) by the Term_in_Months (cell D4), and then subtracts the Loan_Amt (cell B7) from the result to determine the total interest on the mortgage.
6. In cell D7, enter a formula without using a function that adds the Price (cell B5) to the Total_Interest (cell D6) to determine the total cost of the house.
7. Miranda wants to compare monthly payments for interest rates that vary from 4.00 to 4.96 percent and for terms of 180, 240, and 360 months. She has already set up the structure for a data table in the range A11:D24.
   
   Create a two-variable data table as follows to provide the comparison that Miranda requests:
   1. In cell A11, enter a formula without using a function that references the Monthly_Payment amount (cell D5) because Miranda wants to compare the monthly payments.
   2. Based on the range A11:D24, create a two-variable data table that uses the term in months (cell D4) as the row input cell and the rate (cell D3) as the column input cell.
8. In the list of interest rates (range A12:A24), create a Conditional Formatting Highlight Cells Rule to highlight the listed rate that matches the rate for the Beecher Street house (cell D3) in Light Red Fill with Dark Red Text.
9. Change the color of the left, right, and bottom borders of the range A9:D24 to Tan, Accent 4, to match the other outside borders in the worksheet.
10. Miranda and Ricardo talked to three local banks about securing a mortgage for the Beecher Street house. With the first bank, they could borrow $315,000 at 4.40 percent annual interest and pay back the loan in 30 years. She wants to determine the monthly payment with the first bank.
    
    In cell G9, enter a formula using the PMT function and the monthly interest rate (cell G5), the loan period in months (cell G7), and the loan amount (cell G3) to calculate the monthly payment with Bank 1.
11. With the second bank, Miranda and Ricardo could reduce their monthly payments to $1,500 but pay a higher annual interest rate (4.56 percent) to pay back the loan in 30 years. Miranda wants to know how much she can borrow with those conditions.
    
    In cell H3, insert a formula using the PV function and the monthly interest rate (cell H5), the loan period in months (cell H7), and the monthly payment (cell H9) to calculate the loan amount for Bank 2.
12. The third bank will allow Miranda and Ricardo to borrow $337,500 at an annual interest rate of 4.56 percent and a monthly payment of $1,500. Miranda wants to know the amount remaining on the loan after 20 years, or the future value of the loan, so that she and Ricardo can start to pay it off more quickly at that point.
    
    In cell I10, insert a formula using the FV function and the monthly interest rate (cell I5), the loan period in months (cell I7), the monthly payment (cell I9), and the loan amount (cell I3) to calculate the future value of the loan with Bank 3.
13. Miranda plans to print parts of the Mortgage Calculator workbook. Prepare for printing as follows:
    1. Set row 2 as the print titles for the worksheet.
    2. Set the range F2:I13 as the print area.
14. Hide the Listings worksheet, which contains data Miranda wants to keep private.
15. Go to the Car Loan worksheet, which contains details about a loan Miranda and Ricardo took out for a new car.
    The worksheet contains two errors. Make sure Excel is set to check all types of errors, and then resolve the ones on the Car Loan worksheet as follows:
    1. Display the possible errors and solutions for cell B2, and then ignore the error.
    2. Trace the precedents to the formula in cell H7, which should multiply the optional extra payments by the scheduled number of payments. Correct the error.
16. Draw attention to the optional extra payments in the range B10:D10 by adding a thick outside border using the Tan, Accent 4 shape outline color.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Mortgage Calculator Worksheet

Final Figure 2: Car Loan Worksheet