Part 1 - Loan Worksheet - Calculate Payment

To add the Mortgage expense for the store we need to calculate the mortgage payment on the Loan Worksheet and then add a reference to the payment amount on the Overview worksheet.

This type of calculation was covered as a topic in Excel chapter 2.

Since this worksheet will have an area for user input and also an area where the calculated results will be displayed you need to design the worksheet appropriately and may want to refer to the Worksheet Design page to view example layouts.

1. Enter the text Loan Calculation in cell A1
2. Merge and center the text in cell A1 across columns A to E
3. Change cell A1 font size and background color to an appropriate combination for a title.
4. Input area - Starting in cell A3 create the following. Use the following for your input area text and values.

   Store Cost - 2201 Cuyamaca St.	722,100.00
   Down Payment	32,300.00
   Annual Percentage Rate	3.125%
   Loan Term - Years	30

5. Output area - select an appropriate area to enter formulas to calculate the following for your output area values.
   • Loan Amount is the difference between the cost of the store and the down payment
   • Monthly Payment - payments are at the end of the month and displayed as a positive value.
   • Total Cost of Loan which is the total of all payments
   • Total Interest which is the difference between the Loan Amount and Total Cost of Loan

6. Loan Amount
   Monthly Payment
   Total Interest
   Total Cost of Loan

7. Create a Range Name for the Workbook using the monthly payment amount with the name Loan_Payment.
8. Format the worksheet to make it look business like and professional.
   • Self check. Change the Loan Term to 15 years. You should see the Monthly Payment, Total Interest and Total Cost of Loan change. If any of them stay the same, then you have a problem.
   • When finished checking change the Loan Term back to 30 years.

Part 2 - Update Overview Worksheet

In the part of the assignment, you will add references to the Overview worksheet you already created in your workbook.

Income
Interest	319.03
Sales	64191.00
Total	formula

Expenses
Mortgage	Add 3D reference to Loan Payment
Payroll	3D reference to Payroll Total (from Lab 2)
Tax	formula
Insurance	1622.50
Phone	187.22
Internet	121.86
Utilities	418.24
Advertising	1218.37
Total	formula
Net Income	formula

1. Add a 3D cell reference to the Loan_Payment range in the Mortgage cell

Comment-Make excel worksheet please and show formulas.