Cuyamaca College Loan Calculation Worksheet

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 – 2130 Cuyamaca St. 723,100.00Down Payment32,300.00Annual Percentage Rate3.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 other worksheet you have created in your workbook. Below is an overview of the worksheet.  Income    Interest   319.03   Sales64191.00Totalenter formula  Expenses    Mortgage3D reference to Loan Payment   Payroll3D reference to Payroll Total   Taxenter formula   Insurance 1622.50   Phone 187.22   Internet   121.86   Utilities   418.24   Advertising 1218.37Totalenter formula  Net Incomeenter formula

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