Description

Assume you are planning on buying a house and need to obtain a loan in the amount of $175,000. The lender provided you the following options:

A 30-year mortgage with an annualized interest rate of 9.74%

A 15-year mortgage with an interest rate year of 9.46%.

Assume a 30-year mortgage for $442,264 as in the option 1, what would happen if the interest rate / year dropped from 9.21% to 7.95%. How much of each year’s payment goes to paying interest and how much goes to reducing the principal under the two interest rates?

You Must use excel formula for all calculations. No credit will be given for not using excel formulas. You are required to calculate the followings:

For Option 1:

What is the annual payment required by the two alternative mortgages?

How much of each year’s payment goes to paying interest and how much to reducing the principal balance?

For Option 2:

What is the annual payment required by the two alternative mortgages?

How much of each year’s payment goes to paying interest and how much to reducing the principal balance?

Between option 1 and option 2, which mortgage would you prefer?

Instructions:

Use the Assignment 1.1 -Loan Amortization Template to complete the Assignment 1.1.

Populate the necessary inputs in the cells B 4 to B 6.

For Beg. Principal Balance Insert the formulas from the cells B 19 to AF

For Payment, Interest Component and Principal Component Insert the formulas from the cells B 20, 21 and 22 to AE, respectively.LOAN AMORTIZATION

Basics and Sensitivity Analysis

Inputs

$50.000

Present value

$170.000

17

Payment

Loan Amortization

Interest Component

$45.000

Principal Component

$40.000

Interest rate / year

9,00%

9

30

30

$35.000

$30.000

Number of years

$25.000

$20.000

$15.000

$10.000

$5.000

$0

0

5

10

15

Year

3

4

5

6

7

8

9

10

11

12

Data Table: Sensitivity of the Interest Component to the Interest Rate / Year

Input Values for

Output Formula: Interest Component

Interest rate / year

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

Data Table: Sensitivity of the Principal Component to the Interest Rate / Year

Input Values for

Output Formula: Principal Component

Interest rate / year

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

Outputs

Year

Beg. Principal Balance

Payment

Interest Component

Principal Component

1

2

d Interest Components

Principal And Interest Payments Over Time

$25.000

8% Interest Component

$20.000

$15.000

7% Interest Component

20

25

30

Principal And Interest C

$10.000

7% Principal Component

$5.000

8% Principal Component

$0

0

5

10

15

Time (Years)

20

25

30

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

28

29

30

$0

$0

$0

$0

$0

$0

31

LOAN AMORTIZATION

Basics and Sensitivity Analysis

Inputs

$50.000

Present value

$170.000

17

Payment

Loan Amortization

Interest Component

$45.000

Principal Component

$40.000

Interest rate / year

9,00%

9

15

15

$35.000

$30.000

Number of years

$25.000

$20.000

$15.000

$10.000

$5.000

$0

0

5

10

15

Year

3

4

5

6

7

8

9

10

11

12

Data Table: Sensitivity of the Interest Component to the Interest Rate / Year

Input Values for

Output Formula: Interest Component

Interest rate / year

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

Data Table: Sensitivity of the Principal Component to the Interest Rate / Year

Input Values for

Output Formula: Principal Component

Interest rate / year

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

Outputs

Year

Beg. Principal Balance

Payment

Interest Component

Principal Component

1

2

l And Interest Components

Principal And Interest Payments Over Time

$25.000

8% Interest Component

$20.000

$15.000

$10.000

7% Interest Component

20

25

30

Principal And Intere

$10.000

7% Principal Component

$5.000

8% Principal Component

$0

0

5

10

15

Time (Years)

20

25

30

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

28

29

30

$0

$0

$0

$0

$0

$0

31

LOAN AMORTIZATION

Basics and Sensitivity Analysis

Inputs

$50.000

Present value

$442.264

44

Payment

Loan Amortization

Interest Component

$45.000

Principal Component

$40.000

Interest rate / year

9,21%

9

30

30

$35.000

$30.000

Number of years

$25.000

$20.000

$15.000

$10.000

$5.000

$0

Outputs

Year

Beg. Principal Balance

Payment

Interest Component

Principal Component

1

2

0

5

10

15

Year

3

4

5

6

Data Table: Sensitivity of the Interest Component to the Interest Rate / Year

Input Values for

Output Formula: Interest Component

Interest rate / year

Data Table: Sensitivity of the Principal Component to the Interest Rate / Year

Input Values for

Output Formula: Principal Component

Interest rate / year

d Interest Components

Principal And Interest Payments Over Time

$25.000

8% Interest Component

$20.000

$15.000

7% Interest Component

20

25

7

30

8

9

10

11

12

Principal And Interest C

$10.000

7% Principal Component

$5.000

8% Principal Component

$0

0

5

10

15

Time (Years)

20

25

30

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

Purchase answer to see full

attachment