Write an excel spreadsheet to amortize a general ARM. Make sure that your program can handle

Anonymous
timer Asked: May 8th, 2018
account_balance_wallet $60

Question Description

Different interest rate caps (periodic, lifetime and initial rate increase. This also means your program will be able to handle fixed rate mortgages) [1/4/2];

Payment caps (with neg am cap; assume once this is hit, the loan becomes amortizing according to 5 below) [10%, 120%];

Different reset periods. While the index should be recomputed monthly, the reset should be specified in years (see also 6 below) [1];

Different mortgage terms (up to 360 months) [180];

Different amortization terms [360];

Differing hybrid types (1/1, 3/1, 5/1, etc.), along with the rate for the fixed interest rate periods [3/1, 3.5%];

Differing initial index rate [Use index path in attached].

The inputs above (along with the margin [2.5%] and loan amount [100k]) must be taken from a separate input sheet. That is, if I change any of the terms above in one input cell on the input tab, the amortization schedule (CF sheet) should correctly re-compute without having to alter anything in the CF amortization sheet.

(For this part of the project) use the sample index path as given in the accompanying file “InterestRatePath2Use.xlsx.”

Unformatted Attachment Preview

Write an excel spreadsheet to amortize a general ARM. Make sure that your program can handle Different interest rate caps (periodic, lifetime and initial rate increase. This also means your program will be able to handle fixed rate mortgages) [1/4/2]; Payment caps (with neg am cap; assume once this is hit, the loan becomes amortizing according to 5 below) [10%, 120%]; Different reset periods. While the index should be recomputed monthly, the reset should be specified in years (see also 6 below) [1]; Different mortgage terms (up to 360 months) [180]; Different amortization terms [360]; Differing hybrid types (1/1, 3/1, 5/1, etc.), along with the rate for the fixed interest rate periods [3/1, 3.5%]; Differing initial index rate [Use index path in attached]. The inputs above (along with the margin [2.5%] and loan amount [100k]) must be taken from a separate input sheet. That is, if I change any of the terms above in one input cell on the input tab, the amortization schedule (CF sheet) should correctly re-compute without having to alter anything in the CF amortization sheet. (For this part of the project) use the sample index path as given in the accompanying file “InterestRatePath2Use.xlsx.” Interest Rate Model: NOTE: Dates are years. All monthly values=prior year date value. Example: below Custom (By Hand) Index; Input Annual Values here: Date (year) Value 0 3.00% <- This means that the index value at month date 0,1,2,3,…11 is 3% 1 3.50% <- This means that the index value at month date 12,13,14,15,…23 is 2 4.00% etc 3 5.00% 4 5.00% 5 3.00% 6 6.00% 7 6.00% 8 6.00% 9 7.00% 10 8.00% 11 9.00% 12 8.00% 13 7.00% 14 6.00% 15 7.00% 16 6.00% 17 6.00% 18 5.00% 19 4.00% 20 4.00% 21 3.00% 22 4.00% 23 4.00% 24 4.00% 25 4.00% 26 4.00% 27 4.00% 28 4.00% 29 4.00% 30 4.00% 31 4.00% 32 4.00% 33 4.00% 34 4.00% 35 4.00% 36 4.00% 37 4.00% 38 4.00% 39 4.00% 40 4.00% 41 4.00% 42 4.00% 43 4.00% 44 4.00% 45 4.00% 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% e at month date 12,13,14,15,…23 is 3.5% ...
Purchase answer to see full attachment

Tutor Answer

Chucks574
School: UIUC

Thank you so much

Loan
Fixed Rate
Term
Amort.Term
Margin
Initial Index Rate
ARM:
Fixed Period
Reset Period
Caps:
Periodic
lifetime
initial Reset
Initial Rate cap
PMT cap
Negative Amortization Cap

100,000.00
3.5
30 Years
30 years
2.50%
3%

3 years
1 year

1%
4%
2%
5.50%
10%
120%

Loan amount
Term (years)
Starting interest rate
Starting monthly payment

100,000.00
30
3.500%
449.04

interest rate
0
3.50%
37
5.50%
49
6.50%
61
7.50%

.
Rate remains fixed for
Months between adjustments

3
12

Est. Max rate
Est. Max monthly payment
Total payments
Total interest
Internal Rate of Return

7.50%
672.97
232,128.48
132,128.48
6.28%

Amortization Schedule
No.

Interest Rate Payment Due

Interest Principal

($100,000.00)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
© 2005 Vertex42 LLC

3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%
3.500%

449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04

[42]
Cumulativ Cumulativ
Balance e Interest e Principal

payment cap 10%

$ 100,000
291.67
291.21
290.75
290.29
289.82
289.36
288.89
288.43
287.96
287.49
287.02
286.54
286.07
285.59
285.12
284.64
284.16
283.68
283.20
282.71
282.23
281.74
281.25
280.76
280.27
279.78
279.29
278.79
278.30
277.80
277.30
276.80
276.29
275.79
275.29

157.38
157.84
158.30
158.76
159.22
159.69
160.15
160.62
161.09
161.56
162.03
162.50
162.98
163.45
163.93
164.41
164.89
165.37
165.85
166.33
166.82
167.30
167.79
168.28
168.77
169.26
169.76
170.25
170.75
171.25
171.75
172.25
172.75
173.25
173.76

99,842.62
99,684.78
99,526.49
99,367.73
99,208.51
99,048.82
98,888.67
98,728.05
98,566.96
98,405.40
98,243.37
98,080.87
97,917.90
97,754.45
97,590.52
97,426.11
97,261.23
97,095.86
96,930.01
96,763.68
96,596.86
96,429.56
96,261.77
96,093.49
95,924.71
95,755.45
95,585.69
95,415.44
95,244.69
95,073.44
94,901.69
94,729.45
94,556.70
94,383.44
94,209.68

291.67
582.87
873.62
1,163.91
1,453.73
1,743.09
2,031.98
2,320.41
2,608.36
2,895.85
3,182.87
3,469.41
3,755.48
4,041.07
4,326.19
4,610.83
4,894.99
5,178.67
5,461.86
5,744.57
6,026.80
6,308.54
6,589.80
6,870.56
7,150.83
7,430.61
7,709.90
7,988.69
8,266.99
8,544.78
8,822.08
9,098.88
9,375.17
9,650.96
9,926.25

https://www.vertex42.com/ExcelTemplates/arm-calculator.html

157.38
315.22
473.51
632.27
791.49
951.18
1,111.33
1,271.95
1,433.04
1,594.60
1,756.63
1,919.13
2,082.10
2,245.55
2,409.48
2,573.89
2,738.77
2,904.14
3,069.99
3,236.32
3,403.14
3,570.44
3,738.23
3,906.51
4,075.29
4,244.55
4,414.31
4,584.56
4,755.31
4,926.56
5,098.31
5,270.55
5,443.30
5,616.56
5,790.32

449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
449.04
2 of 10

1
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
© 2005 Vertex42 LLC

3.500%
3.500%
5.500%
5.500%
5.500%
5.500%
5.500%
5.500%
5.500%
5.500%
5.500%
5.500%
5.500%
5.500%
6.500%
6.500%
6.500%
6.500%
6.500%
6.500%
6.500%
6.500%
6.500%
6.500%
6.500%
6.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%
7.500%

449.04
449.04
557.76
557.76
557.76
557.76
557.76
557.76
557.76
557.76
557.76
557.76
557.76
557.76
614.88
614.88
614.88
614.88
614.88
614.88
614.88
614.88
614.88
614.88
614.88
614.88
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97
672.97

291.67
274.78
431.00
430.41
429.83
429.24
428.66
428.06
427.47
426.87
426.27
425.67
425.06
424.46
500.91
500.29
499.67
499.05
498.42
497.79
497.15
496.52
495.87
495.23
494.58
493.93
569.16
568.51
567.86
567.20
566.54
565.88
565.21
564.54
563.86
563.18
562.49
561.80
561.10
560.41
559.70
558.99
558.28
557.56
556.84
556.12
555.39
554.65
553.91
553.17
552.42
551.67
550.91
550.15
549.38

157.38
174.27
126.76
127.34
127.93
128.51
129.10
129.69
130.29
130.89
131.49
132.09
132.69
133.30
113.98
114.59
115.21
115.84
116.47
117.10
117.73
118.37
119.01
119.66
120.30
120.95
103.81
104.46
105.11
105.77
106.43
107.09
107.76
108.43
109.11
109.79
110.48
111.17
111.87
112.57
113.27
113.98
114.69
115.41
116.13
116.85
117.58
118.32
119.06
119.80
120.55
121.30
122.06
122.83
123.59

99,842.62
94,035.42
93,908.65
93,781.31
93,653.38
93,524.87
93,395.76
93,266.07
93,135.78
93,004.89
92,873.41
92,741.32
92,608.62
92,475.32
92,361.35
92,246.75
92,131.54
92,015.70
91,899.23
91,782.13
91,664.40
91,546.03
91,427.02
91,307.37
91,187.06
91,066.11
90,962.30
90,857.84
90,752.74
90,646.97
90,540.54
90,433.45
90,325.69
90,217.25
90,108.14
89,998.35
89,887.87
89,776.69
89,664.83
89,552.26
89,438.99
89,325.02
89,210.33
89,094.92
88,978.80
88,861.94
88,74...

flag Report DMCA
Review

Anonymous
awesome work thanks

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors