Shelly Cashman Excel 365 | Module 4: SAM Project B Choi Family

GETTING STARTED

1. Save the file SC_EX365_4B_FirstLastName_1.xlsx as SC_EX365_4B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_EX365_4B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Yun Hee and Minho Choi are considering whether to purchase a new home in Nashville, Tennessee. They have identified nine properties that meet their criteria for living space and neighborhood. Yun Hee asks for your help in creating a mortgage analysis that summarizes information about the loans to purchase one of the properties. Go to the Mortgage Calculator worksheet. Most of the cells in the range C8:C15 have defined names, but cell C10 needs one so it can be used in formulas. Add Loan_Amount as the defined name for cell C10.

2. In cell C10, calculate the loan amount by entering a formula without using a function that subtracts the Down_Payment from the Price.

3. Yun Hee also wants to use defined names in other calculations to help her interpret the formulas. In the range C12:C15, create defined names based on the values in the range B12:B15.

4. Yun Hee needs to calculate the monthly payment for a loan to purchase the Maxwell Avenue property. Calculate the payment as follows:

a. In cell C13, start to enter a formula using the PMT function.

b. For the rate argument, divide the Rate by 12 to use the monthly interest rate.

c. For the nper argument, use the Term_in_Months to specify the number of periods.

d. For the pv argument, use the Loan_Amount to include the present value.

e. Insert a negative sign (-) after the equal sign in the formula to display the result as a positive amount.

5. In cell C14, enter a formula without using a function that multiplies the Monthly_Payment by the Term_in_Months and then subtracts the Loan_Amount from the result to determine the total interest.

6. In cell C15, enter a formula without using a function that adds the Price to the Total_Interest to determine the total cost.

7. Yun Hee wants to compare monthly payments for interest rates that vary from 6.25% to 6.75% and for terms of 180, 240, and 360 months. She has already set up the structure for a data table in the range B17:E29. Create a two-variable data table as follows to provide the comparison that Yun Hee requests:

a. In cell B18, enter a formula without using a function that references the Monthly_Payment amount because Yun Hee wants to compare the monthly payments.

b. Based on the range B18:E29, create a two-variable data table that uses the term in months (cell C12) as the row input cell and the rate (cell C11) as the column input cell.

8. In the list of interest rates (range B19:B29), create a Conditional Formatting Highlight Cells Rule to highlight the listed rate that matches the rate for the Maxwell Avenue property (cell C11) in Green Fill with Dark Green Text.

9. Change the color of the left, right, and bottom borders of the range B18:E29 to Orange, Accent 1, Lighter 60% (5th column, 3rd row in the Theme Colors palette) to coordinate with the other outside borders in the worksheet.

10. After Yun Hee and Minho purchase the Maxwell Avenue building, they want to renovate the property. Yun Hee has created three options for funding the renovation. In the first option, she could borrow additional funds for a basic renovation and pay for the full amount of the loan over 10 years. She wants to determine the monthly payment for the first scenario. In cell E12, insert a formula using the PMT function using the monthly interest rate (cell E8), the loan period in months (cell E10), and the loan amount (cell E6) to calculate the monthly payment for the first renovation option.

11. In the second option, Yun Hee could borrow money for a more extensive renovation and pay back the loan in 15 years instead of 10. She could also reduce her monthly payments to $900 with an annual interest rate of 7.05%. She wants to know the loan amount if she requests those conditions. In cell F6, insert a formula using the PV function and the monthly interest rate (cell F8), the loan period in months (cell F10), and the monthly payment (cell F12) to calculate the loan amount for the 15-year option.

12. In the third option, Yun Hee could fund a major renovation by paying back the loan over five years with a monthly payment of $1,000 and then renegotiating better terms. She wants to know the amount remaining on the loan after five years or the future value of the loan. In cell G13, insert a formula using the FV function and the rate (cell G8), the number of periods (cell G10), the monthly payment (cell G12), and the loan amount (cell G6) to calculate the future value of the loan for the five-year scenario.

13. Yun Hee plans to print parts of the Mortgage Calculator worksheet. Prepare for printing as follows:

a. Set row 5 as the print titles for the worksheet.

b. Set the range B5:G15 as the print area.

14. Hide the Properties worksheet, which contains data Yun Hee wants to keep private.

15. Go to the Car Loan worksheet, which contains details about a loan the Chois are considering for a new car. The worksheet contains one error. Make sure Excel is set to check all types of errors, and then trace the precedents to the formula in cell H11, which should multiply the scheduled payment amount by the number of scheduled payments. Correct the error.

16. Draw attention to the optional extra payments in cell E11 by adding a thick outside border using the Orange, Accent 1 outline color (5th column, 1st row of the Theme Colors palette).

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 website to submit your completed project.

Final Figure 1: Mortgage Calculator Worksheet

Final Figure 2: Car Loan Worksheet