For each bank:

Bank Name

Mortgage Rate

Closing Costs

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

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)

SCENARIO 2

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.50%? b) what happens if it increases by 0.5% ?

SCENARIO 3

What happens if you pay an additional $150 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

How much is your monthly payment if you finance the house for 15 years? Assume additional Payment = 0.

Based on 14, how much is the total interest amount if 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. What bank, is better? Why?

Bank A: Allegacy Bank B: Trulient

The House is worth $400,000 The house is worth $400,000

Finance it for 30 years Finance it for 30 years

Putting down 25% of the house value. Putting down 25% of the house value

Rate: 3.875% Rate: 4.000%

APR: 3.990% APR: 4.164

Closing costs: $4,000 Closing Costs: $3,000

APR Bank A Bank B Cena 1 (20 ints Bank Name Closing Costs Home value Amount Financed NAND Loan Amount (Amount Financed + Closing Costs) 10 11 No of Years 12 No of Periods Effective Rate 14 Monthly Rate NAMMANNIN 15 Monthly payment 16 Total Interest amount (S) 17 Principal Outstanding half way throught the loan (15 years ) 18 19 Scenario 2-Cheaper & Higher interest Rate (7.5 points) a 20 Lower 21 APRI Effective RateCODILLACILITI 23 Monthly Rate ALLELL 24 Monthly Payment LLLLLLLLLLLLLLLLLION 25 Total Interest amount (S) 26 Higher 27 APR 28 Effective Rate 29 Monthly RateMNIAN WINE Monthly payment 31 Total Interest amount (S) 32 33 Scenario 3. Additional Payment/month (7.5 points) No of Years to Payoff the Loan 35 Total Interest amount (S) Savings d 22 40 Scenario 4-15 year loan (17.5 points) No of Periods Effective Rate Monthly payments Total Interest amount (S) SUMMARYScenario 1 Bank A Scenario 1 Banks A scenario 2 Bank A scenario 2 Bank B Scenario3 Bank A Scenario 3 Bas .. 41 42