Excel HW final

profilecolombiano305
final_exam_ree6045_-_excel_file2-2.xlsx
Home>Business & Finance homework help>Accounting homework help>Excel HW final

Q1

Input Variables
Inflation expectation 2.00% Down payment 30%
Purchase price $300,000 Loan amount $210,000
Initial monthly rent $2,200 Interest rate (Fixed) 4.00%
Rental growth rate 2.00% Mortgage initiation fee $3,000
Property appreciation 2.00% Loan maturity (years) 30
Insurance $4,500 Mortgage Payment $1,002.57
Maintenance $3,000 Holding period (years) 5
Expense growth 2.00% Selling expenses 6.50%
Marginal tax rate 25.00% Invetments opportunity 8.50%
Property tax $5,250
Option 1: Buy the house
Year 0 1 2 3 4 5
Out flows
Down payment + Origination $93,000
Insurance & Maintenance $7,500 $7,650 $7,803 $7,959 $8,118
Property tax $5,250 $5,355 $5,462 $5,571 $5,683
Mortgage payments $12,031 $12,031 $12,031 $12,031 $12,031
Total: $93,000 $24,781 $25,036 $25,296 $25,561 $25,832
In flow
Tax benefits $0 $3,396 $3,384 $3,372 $3,358 $3,344
Overall "out of pocket" $93,000 $21,385 $21,652 $21,924 $22,203 $22,488
Other
Interest payment $8,333 $8,182 $8,025 $7,862 $7,692
Principal payments $3,698 $3,849 $4,006 $4,169 $4,339
House value $300,000 $306,000 $312,120 $318,362 $324,730 $331,224
Total equity $90,000 $99,698 $109,667 $119,915 $130,451 $141,284
Cash at time sold $119,755
Option 2: Rent the house
Rent expenses $26,400 $26,928 $27,467 $28,016 $28,576
Investments portfolio $93,000 $95,890 $98,764 $101,617 $104,442 $107,231

Q2

Acquisition Price $10,200,000 Down Payment $ 6,000,000
Expected 1st Year NOI $540,000 Mortgage Rate 5.50%
CAP 5.29% Manutiry 20
Required Rate of Return 8.00% Levered Required Rate 11.00%
Expected NOI Growth 3.00% Minumum Required DCR 1.25
Terminal NOI Growth 2.50%
Next Investor Req Premium 0.50%
Terminal CAP 6.00% Loan Amount $4,200,000
Next buyer Required return 8.50% DCR 1.54
Mortgage Payment (Annual) $351,453 Miminum DCR satisfied? Yes
Mortgage Balance at Sale $3,029,006
Leveraged
End of year NOI PV_NOI REV PV_REV Total PV Total CF DS CF PV(CF)
0 -$10,200,000 -$6,000,000 -$6,000,000
1 $540,000 $500,000 $500,000 $540,000 $351,453 $188,547 $169,862
2 $556,200 $476,852 $476,852 $556,200 $351,453 $204,747 $166,177
3 $572,886 $454,775 $454,775 $572,886 $351,453 $221,433 $161,910
4 $590,073 $433,721 $433,721 $590,073 $351,453 $238,619 $157,186
5 $607,775 $413,641 $413,641 $607,775 $351,453 $256,322 $152,114
6 $626,008 $394,491 $394,491 $626,008 $351,453 $274,555 $146,788
7 $644,788 $376,228 $376,228 $644,788 $351,453 $293,335 $141,287
8 $664,132 $358,810 $11,345,586 $6,129,667 $6,488,477 $12,009,718 $3,380,459 $8,629,260 $3,744,464
Total PV: $9,538,186
NPV: -$661,814 NPV:
IRR: 6.92% IRR:
Amortization schedule:
I P Ending balance
0 $4,200,000
1 $231,000 $120,453 $4,079,547
2 $224,375 $127,078 $3,952,469
3 $217,386 $134,067 $3,818,401
4 $210,012 $141,441 $3,676,960
5 $202,233 $149,220 $3,527,740
6 $194,026 $157,427 $3,370,312
7 $185,367 $166,086 $3,204,226
8 $176,232 $175,221 $3,029,006
9 $166,595 $184,858 $2,844,148
10 $156,428 $195,025 $2,649,123
11 $145,702 $205,751 $2,443,371
12 $134,385 $217,068 $2,226,303
13 $122,447 $229,006 $1,997,297
14 $109,851 $241,602 $1,755,695
15 $96,563 $254,890 $1,500,805
16 $82,544 $268,909 $1,231,896
17 $67,754 $283,699 $948,197
18 $52,151 $299,302 $648,895
19 $35,689 $315,764 $333,131
20 $18,322 $333,131 -$0

Q3

Month Beginning balance Interest Principal Ending Balance Interest rate 3.50%
1 Original balance $ 340,000
2
3 Monthly Payment
4
5 Remaining balance after months:
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
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180

Q4

Year Q Las Vegas Houston Inflation
1975 1 52.7 Time Period Las Vegas Nominal Annual Price Appreciation Rate Houston Nominal Annual Price Appreciation Rate Las Vegas Real Annual Price Appreciation Rate Houston Real Annual Price Appreciation Rate
1975 2 53.6
1975 3 54.6
1975 4 55.5 Q2 2007 - Q4 2009 (2.5 years)
1976 1 55.9 Q1 2003 - Q1 2006 (3 years)
1976 2 58.44 56.8 Q2 1986 - Q2 1995 (9 years)
1976 3 58.03 57.6 Q2 1978 - Q3 2014 (36.25 years)
1976 4 58.01 58.2
1977 1 60.05 59.5
1977 2 65.95 60.7
1977 3 64.06 61.4
1977 4 67.65 62.1
1978 1 69.61 63.4
1978 2 48.8 74.96 65.2
1978 3 51.65 73.49 66.5
1978 4 53.3 77.17 67.7
1979 1 57.69 78.81 69.8
1979 2 57.73 83.19 72.3
1979 3 62.36 84.61 74.6
1979 4 60.66 85.34 76.7
1980 1 60.68 88.06 80.1
1980 2 63.18 91.83 82.7
1980 3 68.37 90.75 84.0
1980 4 66.24 93.02 86.3
1981 1 63.08 89.71 88.5
1981 2 77.84 95.93 90.6
1981 3 77.27 97.5 93.2
1981 4 71.62 100.3 94.0
1982 1 77.89 106.19 94.5
1982 2 78.3 107.76 97.0
1982 3 79.12 105.55 97.9
1982 4 84.6 107.6 97.6
1983 1 76.83 107.67 97.9
1983 2 75.08 108.33 99.5
1983 3 74.91 107.41 100.7
1983 4 70.14 107.02 101.3
1984 1 74 103.43 102.6
1984 2 75.1 103.32 103.7
1984 3 70.57 104.06 105.0
1984 4 70.38 101.27 105.3
1985 1 73.25 97.9 106.4
1985 2 72.53 95.8 107.6
1985 3 73.11 94.17 108.3
1985 4 74.82 92.02 109.3
1986 1 75.1 93.13 108.8
1986 2 77.67 94.35 109.5
1986 3 77.49 92.54 110.2
1986 4 76.37 88.24 110.5
1987 1 78.18 88.05 112.1
1987 2 76.57 86.55 113.5
1987 3 77 83.75 115.0
1987 4 76.55 81.53 115.4
1988 1 77.3 81.81 116.5
1988 2 78.32 84.27 118.0
1988 3 79.28 85.07 119.8
1988 4 80.14 85.57 120.5
1989 1 80.68 86.35 122.3
1989 2 82.23 88.42 124.1
1989 3 84.7 90.3 125.0
1989 4 85.74 90.83 126.1
1990 1 87.89 91.41 128.7
1990 2 89.49 92.45 129.9
1990 3 91.96 93.7 132.7
1990 4 92.91 93.49 133.8
1991 1 94.6 94.42 135.0
1991 2 95.69 95.61 136.0
1991 3 95.78 96.19 137.2
1991 4 98.16 97.05 137.9
1992 1 98.62 98.84 139.3
1992 2 98.25 98.35 140.2
1992 3 99.9 100.12 141.3
1992 4 99.94 99.99 141.9
1993 1 99.57 100.31 143.6
1993 2 100.56 100.8 144.4
1993 3 100.64 101.61 145.1
1993 4 101 102.21 145.8
1994 1 100.64 102.2 147.2
1994 2 100.02 101.71 148.0
1994 3 99.93 101.55 149.4
1994 4 99.49 100.19 149.7
1995 1 100 100 151.4
1995 2 102.03 101.66 152.5
1995 3 103.45 102.12 153.2
1995 4 104.38 102.69 153.5
1996 1 105.61 104.19 155.7
1996 2 104.85 103.69 156.7
1996 3 104.67 103.95 157.8
1996 4 105.76 104.58 158.6
1997 1 106.29 104.34 160.0
1997 2 106.21 106.22 160.3
1997 3 107.76 107.24 161.2
1997 4 109.04 108.37 161.3
1998 1 110.25 110.8 162.2
1998 2 110.67 111.94 163.0
1998 3 111.12 114.21 163.6
1998 4 112.03 115.64 163.9
1999 1 112.47 117.15 165.0
1999 2 112.27 120.19 166.2
1999 3 113.13 123.15 167.9
1999 4 113.3 125.71 168.3
2000 1 114.58 127.15 171.2
2000 2 116.02 129.1 172.4
2000 3 117.46 130.81 173.7
2000 4 119.37 131.95 174.0
2001 1 121.93 136.13 176.2
2001 2 123.74 137.58 178.0
2001 3 125.88 139.37 178.3
2001 4 127.54 140.78 176.7
2002 1 129.08 141.51 178.8
2002 2 130.95 143.23 179.9
2002 3 133.66 145.07 181.0
2002 4 134.61 147.18 180.9
2003 1 136.79 148.23 184.2
2003 2 139.4 149.1 183.7
2003 3 143.28 150.03 185.2
2003 4 152.63 151.48 184.3
2004 1 162.11 153.02 187.4
2004 2 179.96 154.45 189.7
2004 3 206.05 156.5 189.9
2004 4 212.14 157.42 190.3
2005 1 218.81 159.97 193.3
2005 2 228.34 160.92 194.5
2005 3 234.54 163.27 198.8
2005 4 244.18 165.85 196.8
2006 1 251.67 168.2 199.8
2006 2 253.67 170.92 202.9
2006 3 254.93 173.19 202.9
2006 4 255.99 174.72 201.8
2007 1 254.15 176.9 205.352
2007 2 249.94 179.77 208.352
2007 3 241.97 180.67 208.490
2007 4 234.66 181.96 210.036
2008 1 217.5 184.17 213.528
2008 2 194.94 184.96 218.815
2008 3 168.75 186.04 218.783
2008 4 152.65 185.82 210.228
2009 1 151.39 188.59 212.709
2009 2 140.81 186.81 215.693
2009 3 128.31 185.76 215.969
2009 4 122.87 185.26 215.949
2010 1 121.39 184.31 217.631
2010 2 119.8 184.66 217.965
2010 3 118.71 184.92 218.439
2010 4 115.31 184.81 219.179
2011 1 109.23 181.38 223.467
2011 2 102.99 180.9 225.722
2011 3 101.95 182.76 226.889
2011 4 101.3 183.35 225.672
2012 1 100.83 184.73 229.392
2012 2 100.88 187.3 229.478
2012 3 103.33 188.31 231.407
2012 4 106.88 190.97 229.601
2013 1 111.19 191.97 232.773
2013 2 119.59 196.27 233.504
2013 3 127.93 200.86 234.149
2013 4 134.87 205.27 233.049
2014 1 139.33 210.06 236.293
2014 2 144.55 218.19 238.343
2014 3 149.53 224.48 238.031