you will have to go or contact to 2 local banks and get information about their mortgage products. You would like to finance the purchase of a new house. The house is worth $300,000. And you want to finance it for 30years (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 15% of the house value Please Read all of the instructions before you begin. Resources 1. Watch the video on building Loan Amortization Tables posted in the Week 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 each bank: 1. Bank Name 2. Mortgage Rate 3. Closing Costs 4. Loan Amount: Assume that you will finance 85% of the purchase of the home plus closing costs. (This means that to your loan amount (85% Value of the house + Closing Costs) On an excel worksheet calculate the following: SCENARIO 1 5. Calculate the monthly rate 6. Number of Periods 7. Monthly Payment 8. Construct a Loan Amortization Table (Considering Monthly payments) 9. How much will you have to pay in interest payments throughout the life of the loan (total interest paid)? 10. What is the effective Rate? 11. 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) SCENARIO 2 12. What happens to your effective rate, monthly payment and total interest throughout the life of the loan if a) your rate (APR) drops by 0.40%? b) what happens if it increases by 0.4% ? SCENARIO 3 13. What happens if you pay an additional $200 per month? 13.1 How much sooner do you finish paying your loan? (in years & months) 13.2 How much do you save on interest? [First add a cell labeled Additional payment. Amount. Add a column to your loan amortization table. The column should be labeled additional payment. The additional payment amount in the table should be referenced to the Additional Payment Cell and should be the same for every period. The additional payment should affect the ending principal balance for each period. If you would like you can add this column from the beginning and for the first scenario, where the additional amount = 0) SCENARIO 4 14. How much is your monthly payment if you finance the house for 15 years? Assume additional Payment = 0. 15. Based on 14, how much is the total interest amount if at the end of the 15 years, what is your effective rate? 16. The first sheet of your workbook should be a summary table. (See file attached on Learn). Analyze and conclude (Use the scenario analysis to help you conclude? what effect does paying more have on the life o the loan, what is the effect on the change of interest rate. What bank, is better? Why? How to Copy a Worksheet The easiest way to do this is as follows: There is a small box above row 1 and before column A ( look at picture below) Click on this box and the whole sheet will highlight. Go to EDIT select COPY and then click on the tab, where you want to copy the worksheet to. Click on cell A1 then go to EDIT and Select Paste. Other ways to do this are: a) Highlight the part of your work sheet that has your assumptions and calculations, go to EDIT Copy, and then go to a tab and got to EDIT PASTE where you want to paste the info b) Go to EDIT Copy/move sheet and create a copy and rename the tab Bank A Bank B Scenario 1 (20 points) 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 Total Interest Amount ($) Principal Outstanding half way throught the loan (12 years 6 months) Scenario 2 - Cheaper & Higher Interest Rate (7.5 points) Lower APR Effective Rate Monthly Rate Monthly Payment Total Interest Amount ($) Higher APR Effective Rate Monthly Rate Monthly Payment Total Interest Amount ($) Scenario 3- Additional Payment/month (7.5 points) No of Years to Payoff the Loan Total Interest Amount ($) Savings Scenario 4 - 15 year loan (7.5 points) No of Periods Effective Rate Monthly Payments Total Interest Amount ($) CONCLUSION (7.5 points)