+1(978)310-4246 credencewriters@gmail.com
  

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

error: Content is protected !!