Shelly Cashman Excel 365 | Module 4: End of Module Project 1 Partner Financial

GETTING STARTED

1. Save the file SC_EX365_EOM4-1_FirstLastName_1.xlsx as SC_EX365_EOM4-1_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_EOM4-1_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. Akio Chen is a lending officer for Partner Financial, a bank headquartered in Kansas City, Missouri. Akio is developing an Excel workbook to give to bank customers to help them perform typical banking calculations. He asks for your help in completing the workbook. Go to the Mortgage Calculator worksheet. Resize row 2 to a height of 33.00 to reduce the blank space at the top of the worksheet.

2. Akio wants you to assign names to cells to help customers interpret the mortgage calculations. In the range D6:D8, define names based on the values in the range C6:C8. In the range F6:F8, define names based on the values in the range E6:E8.

3. Akio asks you to create a set of sample mortgage calculations he can give to customers. In cell F6, enter a formula using the PMT function to calculate the monthly mortgage payment. Insert a negative sign (-) after the equal sign in the formula to display the result as a positive amount. Use defined names for the rate, nper, and pv arguments., Divide the Rate by 12 to use the monthly interest rate, multiply the Term by 12 to specify the number of months as the periods, and use the Loan_Amount as the present value of the loan.

4. The total interest is the total amount of payments minus the loan amount. In cell F7, enter a formula without using a function that multiplies 12 by the Term and the Monthly_Payment, and then subtracts the Loan_Amount from the result to determine the total interest.

5. In cell F8, enter a formula without using a function that adds the Loan_Amount to the Total_Interest to determine the total cost of the loan.

6. Akio wants you to compare monthly payments, total interest, and total cost for interest rates that vary from 6.725% to 7.075%. He has already entered formulas to insert the monthly payment in cell D13, the total interest in cell E13, and the total cost in cell F13. Based on the range C13:F28, create a one-variable data table that uses the rate in cell D7 as the column input cell to make the comparisons.

7. Cell D7 includes the rate Partner Financial currently gives first-time home buyers for a 15-year mortgage. In the list of interest rates (range C14:C28), create a Conditional Formatting Highlight Cells Rule to highlight the matching rate in Green Fill with Dark Green Text.

8. Assign the name Median to cell C21, which contains the median value in the list of interest rates.

9. Compare the current and median interest rates by using defined names to insert the current rate (Rate) in cell D30 and the median rate (Median) in cell D31.

10. Akio has set up the structure for an amortization schedule in the range H12:L28. Finish the amortization schedule by completing the formula in cell J13, which already contains an IF function that checks whether the year in column H is less than or equal to the term in cell D8. Between the commas in the formula in cell J13, enter another formula using the PV function. Use defined cell names for the rate, nper, and pmt arguments. Divide the Rate by 12 to use the monthly interest rate, subtract the year value in cell H13 from the Term, and then multiply the result by 12 to specify the number of months remaining to pay off the loan, and use the Monthly_Payment as a negative value to specify the payment amount per period.

11. Fill the range J14:J27 with the formula in cell J13 to complete the amortization schedule.

12. Go to the Budget worksheet. Akio is developing this worksheet for customers who want to maintain a monthly budget, but he hasn't completed it yet. Hide the Budget worksheet.

13. Go to the Investment Calculator worksheet, which compares details for three investment plans. The options show the amount customers would contribute to an investment such as a retirement plan per month for 15 years and the monthly rate of return. Akio asks you to determine the future value of the investments for each plan. In cell C9, insert a formula with the FV function that uses the monthly rate of return (cell C6), the number of payments (cell C8), and the monthly payment (cell C7) to calculate the future value of Plan 1. Fill the range D9:E9 with the formula in cell C9 to calculate the future value of Plans 2 and 3.

14. Akio also asks you to modify the border of the worksheet title to coordinate with the other borders in the worksheet. In cell B2, change the bottom border by applying a border of Teal, Accent 6 (10th column, 1st row in the Theme Colors palette) and a very thick style (2nd column, 6th style in the Style list) to the cell.

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: Investment Calculator Worksheet