Can someone help me to solve question4 and 5 with excel? please thanks a lot Sub
ID: 2342389 • Letter: C
Question
Can someone help me to solve question4 and 5 with excel? please thanks a lot
Submit an Excel sheet with live formulas.
1 Determine the Monthly Mortgage Payment for a $1,000,000 loan at 3.00% and a 5-year amortization schedule.
2How much Principal remains after 2 years?
3What is the Interest and Principal portion of the 8th Monthly Payment?
4If Year 1’s NOI is $400,000, what is the DSCR?
5Lenders are willing to finance small, multifamily properties at a 70% LTV, and are extending loans at 4.00% on a 30-year amortization. Equity investors are seeking a yield of around 11.00%. Derive the cap rate using the Band of Investment/Ellwood Method.
Explanation / Answer
Balance of principal
1000000
Rate
3%
Year
5 year
Monthly payment
No of payment (12 month*5)
60
Interest rate per month (3%/12)
0.25%
Month
Discounting factor @ 0.25%
1
0.997506
2
0.995019
3
0.992537
4
0.990062
5
0.987593
6
0.985130
7
0.982674
8
0.980223
9
0.977779
10
0.975340
11
0.972908
12
0.970482
13
0.968062
14
0.965648
15
0.963239
16
0.960837
17
0.958441
18
0.956051
19
0.953667
20
0.951289
21
0.948916
22
0.946550
23
0.944190
24
0.941835
25
0.939486
26
0.937143
27
0.934806
28
0.932475
29
0.930150
30
0.927830
31
0.925517
32
0.923209
33
0.920906
34
0.918610
35
0.916319
36
0.914034
37
0.911754
38
0.909481
39
0.907213
40
0.904950
41
0.902694
42
0.900443
43
0.898197
44
0.895957
45
0.893723
46
0.891494
47
0.889271
48
0.887053
49
0.884841
50
0.882635
51
0.880433
52
0.878238
53
0.876048
54
0.873863
55
0.871684
56
0.869510
57
0.867342
58
0.865179
59
0.863021
60
0.860869
Total
55.652358
Monthly Mortgage Payment (1000000/55.652358)
17968.69
Mortgage amortization table
Month
Beginning balance of mortgage payable
Installment
Interest expense (beginning balance of mortgage payable*0.25%)
Reduction in principal (installment - interest expense)
Ending balance of mortgage payable (beginning balance of mortgage payable - reduction in principal)
1
1000000.00
17968.69
2500.00
15468.69
984531.31
2
984531.31
17968.69
2461.33
15507.36
969023.95
3
969023.95
17968.69
2422.56
15546.13
953477.82
4
953477.82
17968.69
2383.69
15585.00
937892.82
5
937892.82
17968.69
2344.73
15623.96
922268.86
6
922268.86
17968.69
2305.67
15663.02
906605.84
7
906605.84
17968.69
2266.51
15702.18
890903.67
8
890903.67
17968.69
2227.26
15741.43
875162.24
9
875162.24
17968.69
2187.91
15780.78
859381.45
10
859381.45
17968.69
2148.45
15820.24
843561.21
11
843561.21
17968.69
2108.90
15859.79
827701.43
12
827701.43
17968.69
2069.25
15899.44
811801.99
13
811801.99
17968.69
2029.50
15939.19
795862.80
14
795862.80
17968.69
1989.66
15979.03
779883.77
15
779883.77
17968.69
1949.71
16018.98
763864.79
16
763864.79
17968.69
1909.66
16059.03
747805.76
17
747805.76
17968.69
1869.51
16099.18
731706.58
18
731706.58
17968.69
1829.27
16139.42
715567.16
19
715567.16
17968.69
1788.92
16179.77
699387.39
20
699387.39
17968.69
1748.47
16220.22
683167.17
21
683167.17
17968.69
1707.92
16260.77
666906.39
22
666906.39
17968.69
1667.27
16301.42
650604.97
23
650604.97
17968.69
1626.51
16342.18
634262.79
24
634262.79
17968.69
1585.66
16383.03
617879.76
25
617879.76
17968.69
1544.70
16423.99
601455.77
26
601455.77
17968.69
1503.64
16465.05
584990.71
27
584990.71
17968.69
1462.48
16506.21
568484.50
28
568484.50
17968.69
1421.21
16547.48
551937.02
29
551937.02
17968.69
1379.84
16588.85
535348.17
30
535348.17
17968.69
1338.37
16630.32
518717.85
31
518717.85
17968.69
1296.79
16671.90
502045.96
32
502045.96
17968.69
1255.11
16713.58
485332.38
33
485332.38
17968.69
1213.33
16755.36
468577.02
34
468577.02
17968.69
1171.44
16797.25
451779.77
35
451779.77
17968.69
1129.45
16839.24
434940.53
36
434940.53
17968.69
1087.35
16881.34
418059.19
37
418059.19
17968.69
1045.15
16923.54
401135.65
38
401135.65
17968.69
1002.84
16965.85
384169.80
39
384169.80
17968.69
960.42
17008.27
367161.53
40
367161.53
17968.69
917.90
17050.79
350110.75
41
350110.75
17968.69
875.28
17093.41
333017.33
42
333017.33
17968.69
832.54
17136.15
315881.19
43
315881.19
17968.69
789.70
17178.99
298702.20
44
298702.20
17968.69
746.76
17221.94
281480.26
45
281480.26
17968.69
703.70
17264.99
264215.27
46
264215.27
17968.69
660.54
17308.15
246907.12
47
246907.12
17968.69
617.27
17351.42
229555.70
48
229555.70
17968.69
573.89
17394.80
212160.90
49
212160.90
17968.69
530.40
17438.29
194722.61
50
194722.61
17968.69
486.81
17481.88
177240.72
51
177240.72
17968.69
443.10
17525.59
159715.14
52
159715.14
17968.69
399.29
17569.40
142145.73
53
142145.73
17968.69
355.36
17613.33
124532.41
54
124532.41
17968.69
311.33
17657.36
106875.05
55
106875.05
17968.69
267.19
17701.50
89173.54
56
89173.54
17968.69
222.93
17745.76
71427.79
57
71427.79
17968.69
178.57
17790.12
53637.67
58
53637.67
17968.69
134.09
17834.60
35803.07
59
35803.07
17968.69
89.51
17879.18
17923.89
60
17923.89
17968.69
44.81
17923.88
0.00
Principal remains after 2 years = ending balance of mortgage payable on 24 months (2 years = 24 month)
617879.76
Interest portion of the 8th Monthly Payment
2227.26
Principal portion of the 8th Monthly Payment
15741.43
If Year 1’s NOI is $400,000, what is the DSCR?
Total interest for year 1 (sum of interest expense for first 12 month)
27426.28
DSCR (debt service coverage ratio = net operating income / interest ) (400000/27426.28)
14.5846
LTV
70%
Loan ratio =LTV
70%
Equity ratio (100% - 70%)
30%
Cap rate = (Loan ratio x annual constant) + (Equity ratio x equity yield rate )
Cap rate = (0.70 x 4%) + (0.30 x 11% )
Cap rate = (2.80%) + (3.30% )
Cap rate = 6.10%
Balance of principal
1000000
Rate
3%
Year
5 year
Monthly payment
No of payment (12 month*5)
60
Interest rate per month (3%/12)
0.25%
Month
Discounting factor @ 0.25%
1
0.997506
2
0.995019
3
0.992537
4
0.990062
5
0.987593
6
0.985130
7
0.982674
8
0.980223
9
0.977779
10
0.975340
11
0.972908
12
0.970482
13
0.968062
14
0.965648
15
0.963239
16
0.960837
17
0.958441
18
0.956051
19
0.953667
20
0.951289
21
0.948916
22
0.946550
23
0.944190
24
0.941835
25
0.939486
26
0.937143
27
0.934806
28
0.932475
29
0.930150
30
0.927830
31
0.925517
32
0.923209
33
0.920906
34
0.918610
35
0.916319
36
0.914034
37
0.911754
38
0.909481
39
0.907213
40
0.904950
41
0.902694
42
0.900443
43
0.898197
44
0.895957
45
0.893723
46
0.891494
47
0.889271
48
0.887053
49
0.884841
50
0.882635
51
0.880433
52
0.878238
53
0.876048
54
0.873863
55
0.871684
56
0.869510
57
0.867342
58
0.865179
59
0.863021
60
0.860869
Total
55.652358
Monthly Mortgage Payment (1000000/55.652358)
17968.69
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.