Please answer the following question, showing all work accordingly on ALL sections. Thank you in advance.

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 $400,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? 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) On an excel worksheet calculate the following: 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)? \begin{tabular}{|l|} \hline Part 1 \\ \hline Scenario 1 (10points) \\ \hline Bank Name \\ \hline Closing Costs \\ \hline Home Value \\ \hline Amount Financed \\ \hline Loan Amount (Amount Financed + Closing Costs) \\ \hline APR \\ \hline No of Years \\ \hline No of Periods \\ \hline Effective Rate \\ \hline Monthly Rate \\ \hline Monthly Payment \\ \hline How does the payment you calculated compare to the one given by the bank? \\ \hline Total Interest Amount (\$) \\ \hline Principal Outstanding half way throught the loan \\ \hline \end{tabular} Based on Scenario 1, 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.6% ? and, b) what happens if it increases by 0.6% ? Scenario 2 - Cheaper \& Higher Interest Rate (5 points) \begin{tabular}{|l|} \hline \% Lower \\ \hline APR \\ \hline Effective Rate \\ \hline Monthly Rate \\ \hline Monthly Payment \\ \hline Total Interest Amount (\$) \\ \hline \% Higher \\ \hline APR \\ \hline Effective Rate \\ \hline Monthly Rate \\ \hline Monthly Payment \\ \hline Total Interest Amount (\$) \\ \hline \end{tabular} Based on Scenario 1, what happens if you pay an additional $300 per month? 1. How much is your new payment? 2. How much sooner do you finish paying your loan? (in years \& months) 3. How much do you save on interest? Scenario 3- Additional Payment/month (5points) New Payment No of Years to Payoff the Loan Total Interest Amount (\$) Savings How much is your monthly payment if you finance the house for only 15 years? Assume additional Payment =0. Based on the above, how much is the total interest amount at the end of the 15 years, what is your effective rate? 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... How is it best to manage your loan?.... Scenario 4 - 15 year loan ( 5 points) No of Periods Effective Rate Monthly Payments Total Interest Amount (\$) CONCLUSION (5 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 $400,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? 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) On an excel worksheet calculate the following: 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)? \begin{tabular}{|l|} \hline Part 1 \\ \hline Scenario 1 (10points) \\ \hline Bank Name \\ \hline Closing Costs \\ \hline Home Value \\ \hline Amount Financed \\ \hline Loan Amount (Amount Financed + Closing Costs) \\ \hline APR \\ \hline No of Years \\ \hline No of Periods \\ \hline Effective Rate \\ \hline Monthly Rate \\ \hline Monthly Payment \\ \hline How does the payment you calculated compare to the one given by the bank? \\ \hline Total Interest Amount (\$) \\ \hline Principal Outstanding half way throught the loan \\ \hline \end{tabular} Based on Scenario 1, 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.6% ? and, b) what happens if it increases by 0.6% ? Scenario 2 - Cheaper \& Higher Interest Rate (5 points) \begin{tabular}{|l|} \hline \% Lower \\ \hline APR \\ \hline Effective Rate \\ \hline Monthly Rate \\ \hline Monthly Payment \\ \hline Total Interest Amount (\$) \\ \hline \% Higher \\ \hline APR \\ \hline Effective Rate \\ \hline Monthly Rate \\ \hline Monthly Payment \\ \hline Total Interest Amount (\$) \\ \hline \end{tabular} Based on Scenario 1, what happens if you pay an additional $300 per month? 1. How much is your new payment? 2. How much sooner do you finish paying your loan? (in years \& months) 3. How much do you save on interest? Scenario 3- Additional Payment/month (5points) New Payment No of Years to Payoff the Loan Total Interest Amount (\$) Savings How much is your monthly payment if you finance the house for only 15 years? Assume additional Payment =0. Based on the above, how much is the total interest amount at the end of the 15 years, what is your effective rate? 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... How is it best to manage your loan?.... Scenario 4 - 15 year loan ( 5 points) No of Periods Effective Rate Monthly Payments Total Interest Amount (\$) CONCLUSION (5 points)