please include cell references

PART 1: Mortgage Analysis (30 points) For this project you will have to contact a local bank and get information about their mortgage products. You would like to finance the purchase of a new house. The house is worth $390,000. And you want to finance it for 30 years (I know that the rates you get will depend on your credit score, but ask about the different products assuming you have outstanding credit. Do not give the banks any of your personal info...). You are putting down 25% of the house value. How does the payment compare to the one you calculated? Please Read all of the instructions before you begin. Resources 1. Watch the video on building "Loan Amortization Tables" posted in the Week 6 /Module 6 folder under Videos and Resources. 2. Review the Excel Worksheet on Chapter 5, specifically the tab on Amortization Table. These resources will show you how to create a Loan Amortization Table. For the bank: 1. Bank Name 2. Mortgage Rate 3. Closing Costs 4. Loan Amount: Assume that you will finance 75% of the purchase of the home plus closing costs. (This means that to your loan amount (75% Value of the house + Closing Costs) SCENARIO 1 Calculate the monthly rate that will be used to pay your loan. How does this compare to the amount given by the bank. Is it different or is it the same? Number of Periods Monthly Payment Construct a Loan Amortization Table (Considering Monthly payments) How much will you have to pay in interest payments throughout the life of the loan (total interest paid)? What is the effective Rate? What is the principal amount left to pay half way though the life of the loan (15 years)? Scenario Analysis In order to save time and effort, the easiest way to do this is to copy your entire worksheet onto another one of the tabs in the workbook. (Look at instructions on how to do this below) 1 Create an amortization table including each period that you need to make payments..... 2 (Make sure to watch the amortization table video and check out the spreadsheet posted) Home Value % Financed APR[monthly) No of Years 390,000 75% 4% No of Payments Monthly Interest Rate Payment Amount Home Loan Amount Payment No Beginning Balance Scheduled Payment Interest Principle Payment Ending Balance mo SUMMARY Scenario 1 Bank A Scenario 2 Bank A Scenarioa Bank A Scenario 4 Bank A . Type here to search PLEASE REFERENCE YOUR RESULTS FROM THE SCENARIO TABS ON TO THIS SUMMARY PAGE Total Points Bank A Bank of America Part 1 Total Points: 30 Scenario 1 (10points) Bank Name Closing Costs Home Value Amount Financed Loan Amount (Amount Financed + Closing Costs) APR No of Years No of Periods Effective Rate Monthly Rate Monthly Payment How does the payment you calculated compare to the one given by the bank? Total Interest Amount (S) Principal Outstanding half way throught the loan