Part 1 – Loan Worksheet – Calculate PaymentTo add the Mortgage expense for the s

Part 1 – Loan Worksheet – Calculate PaymentTo 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.Enter the text Loan Calculation in cell A1
Merge and center the text in cell A1 across columns A to E
Change cell A1 font size and background color to an appropriate combination for a title.
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
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
Loan Amount Monthly Payment Total Interest Total Cost of Loan
Create a range name for the Workbook using the monthly payment amount with the name Loan_Payment.
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 WorksheetIn 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 formulaAdd a 3D cell reference to the Loan_Payment range in the Mortgage cell
Add a 3D cell reference to the Payroll_Total range in the Payroll cell
Requirements: finish the excel sheet

Categorized as Excel

Leave a comment

Your email address will not be published. Required fields are marked *