one finance question excel required 6 hours deadline

nolan34
Final_EB_excel_spreadsheets1.xlsx
Home>Business & Finance homework help>Accounting homework help>one finance question excel required 6 hours deadline

A1

a. Answer:
Show your work:
b. Answer:
Show your work:
c. Answer:
Show your work:
d. Answer:
Show your work:

A2

a. Answer:
Input Variables
Inflation expectation 2.00% Down payment 30%
Purchase price $250,000 Loan amount $175,000
Initial monthly rent $1,800 Interest rate (Fixed) 4.00%
Rental growth rate 2.00% Mortgage initiation fee $3,000
Property appreciation 2.00% Loan maturity (years) 30
Insurance $3,750 Mortgage Payment $835.48
Maintenance $2,500 Holding period (years) 5
Expense growth 2.00% Selling expenses 6.50%
Marginal tax rate 25.00% Invetments opportunity 8.50%
Property tax $4,375
Option 1: Buy the house
Year 0 1 2 3 4 5
Out flows
Down payment + Origination $78,000
Insurance & Maintenance $6,250 $6,375 $6,503 $6,633 $6,765
Property tax $4,375 $4,463 $4,552 $4,643 $4,736
Mortgage payments $10,026 $10,026 $10,026 $10,026 $10,026
Total: $78,000 $20,651 $20,863 $21,080 $21,301 $21,527
In flow
Tax benefits $0 $2,830 $2,820 $2,810 $2,799 $2,786
Overall "out of pocket" $78,000 $17,821 $18,043 $18,270 $18,502 $18,740
Other
Interest payment $6,944 $6,818 $6,688 $6,552 $6,410
Principal payments $3,082 $3,207 $3,338 $3,474 $3,616
House value $250,000 $255,000 $260,100 $265,302 $270,608 $276,020
Total equity $75,000 $83,082 $91,389 $99,929 $108,709 $117,737
Cash at time sold $99,796
Option 2: Rent the house
Rent expenses $21,600 $22,032 $22,473 $22,922 $23,381
Investments portfolio $78,000 $80,851 $83,734 $86,649 $89,595 $92,570

A3

b. Answer:
Acquisition Price $9,800,000 Down Payment $ 5,500,000
Expected 1st Year NOI $590,000 Mortgage Rate 5.50%
CAP 6.02% 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,300,000
Next buyer Required return 8.50% DCR 1.64
Mortgage Payment (Annual) $359,821 Miminum DCR satisfied? Yes
Mortgage Balance at Sale $3,101,125
Leveraged
End of year NOI PV_NOI REV PV_REV Total PV Total CF DS CF PV(CF)
0 -$9,800,000 -$5,500,000 -$5,500,000
1 $590,000 $546,296 $546,296 $590,000 $359,821 $230,179 $207,368
2 $607,700 $521,005 $521,005 $607,700 $359,821 $247,879 $201,184
3 $625,931 $496,884 $496,884 $625,931 $359,821 $266,110 $194,577
4 $644,709 $473,880 $473,880 $644,709 $359,821 $284,888 $187,664
5 $664,050 $451,941 $451,941 $664,050 $359,821 $304,229 $180,545
6 $683,972 $431,018 $431,018 $683,972 $359,821 $324,151 $173,304
7 $704,491 $411,064 $411,064 $704,491 $359,821 $344,670 $166,013
8 $725,626 $392,033 $12,396,104 $6,697,229 $7,089,262 $13,121,729 $3,460,946 $9,660,783 $4,192,070
Total PV: $10,421,351
NPV: $621,351 NPV:
IRR: 9.01% IRR:
Amortization schedule:
I P Ending balance
0 $4,300,000
1 $236,500 $123,321 $4,176,679
2 $229,717 $130,104 $4,046,575
3 $222,562 $137,259 $3,909,316
4 $215,012 $144,809 $3,764,507
5 $207,048 $152,773 $3,611,734
6 $198,645 $161,176 $3,450,558
7 $189,781 $170,040 $3,280,517
8 $180,428 $179,393 $3,101,125
9 $170,562 $189,259 $2,911,865
10 $160,153 $199,669 $2,712,197
11 $149,171 $210,650 $2,501,547
12 $137,585 $222,236 $2,279,311
13 $125,362 $234,459 $2,044,852
14 $112,467 $247,354 $1,797,497
15 $98,862 $260,959 $1,536,539
16 $84,510 $275,311 $1,261,227
17 $69,367 $290,454 $970,773
18 $53,393 $306,429 $664,345
19 $36,539 $323,282 $341,063
20 $18,758 $341,063 -$0

A4

Month Beginning balance Interest Principal Ending Balance Interest rate 4.25%
1 Original balance $ 320,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

A5

Year Q Miami Houston Inflation
1975 1 52.7 Time Period Miami Nominal Annual Price Appreciation Rate Houston Nominal Annual Price Appreciation Rate Miami 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 34.85 51.59 55.9 Q1 2002 - Q1 2006 (4 years)
1976 2 36.04 58.32 56.8 Q2 1986 - Q2 1995 (9 years)
1976 3 36.44 58 57.6 Q2 1978 - Q2 2018 (40 years)
1976 4 37.99 57.8 58.2
1977 1 37.12 59.94 59.5
1977 2 39.2 65.9 60.7
1977 3 40.28 64.23 61.4
1977 4 41.91 67.66 62.1
1978 1 39.72 69.45 63.4
1978 2 43.05 74.66 65.2
1978 3 42.34 73.44 66.5
1978 4 45.72 76.89 67.7
1979 1 48.42 78.71 69.8
1979 2 48.39 83.32 72.3
1979 3 53.62 84.67 74.6
1979 4 56.79 85.55 76.7
1980 1 58.6 88.07 80.1
1980 2 57.43 91.92 82.7
1980 3 63.32 90.89 84.0
1980 4 64.88 92.8 86.3
1981 1 67.13 89.81 88.5
1981 2 66.99 96.01 90.6
1981 3 55.28 97.58 93.2
1981 4 66.65 100.37 94.0
1982 1 69.89 106.2 94.5
1982 2 70.22 107.95 97.0
1982 3 67.47 105.65 97.9
1982 4 70.26 107.81 97.6
1983 1 68.96 107.81 97.9
1983 2 68.45 108.4 99.5
1983 3 68.88 107.62 100.7
1983 4 69.11 106.86 101.3
1984 1 67.73 103.45 102.6
1984 2 69.56 103.37 103.7
1984 3 67.03 103.95 105.0
1984 4 68.46 101.38 105.3
1985 1 68.91 97.99 106.4
1985 2 67.76 95.83 107.6
1985 3 66.98 94.32 108.3
1985 4 66.99 92.13 109.3
1986 1 68.6 93.16 108.8
1986 2 70.2 94.4 109.5
1986 3 71.17 92.61 110.2
1986 4 71.01 88.22 110.5
1987 1 68.3 88.1 112.1
1987 2 72.51 86.56 113.5
1987 3 73.64 83.78 115.0
1987 4 74.43 81.55 115.4
1988 1 74.52 81.87 116.5
1988 2 76.5 84.32 118.0
1988 3 77.69 85.05 119.8
1988 4 79.09 85.59 120.5
1989 1 79.33 86.37 122.3
1989 2 80.13 88.45 124.1
1989 3 81.66 90.37 125.0
1989 4 81.51 90.89 126.1
1990 1 83.21 91.48 128.7
1990 2 82.82 92.53 129.9
1990 3 83.65 93.74 132.7
1990 4 83.36 93.56 133.8
1991 1 84.58 94.46 135.0
1991 2 84.73 95.65 136.0
1991 3 85.38 96.21 137.2
1991 4 86.96 97.19 137.9
1992 1 87.99 98.89 139.3
1992 2 87.73 98.42 140.2
1992 3 89.04 100.18 141.3
1992 4 89.15 100.07 141.9
1993 1 90.45 100.38 143.6
1993 2 92.76 100.87 144.4
1993 3 95.61 101.68 145.1
1993 4 96.95 102.25 145.8
1994 1 97.65 102.25 147.2
1994 2 98.55 101.71 148.0
1994 3 98.84 101.67 149.4
1994 4 98.97 100.26 149.7
1995 1 100 100 151.4
1995 2 101.5 101.67 152.5
1995 3 103.64 102.16 153.2
1995 4 105.29 102.74 153.5
1996 1 106.61 104.24 155.7
1996 2 106.01 103.74 156.7
1996 3 105.67 103.99 157.8
1996 4 107.19 104.63 158.6
1997 1 108.04 104.47 160.0
1997 2 107.68 106.25 160.3
1997 3 108.08 107.3 161.2
1997 4 110.14 108.45 161.3
1998 1 113.35 110.87 162.2
1998 2 113.35 111.99 163.0
1998 3 113.73 114.31 163.6
1998 4 115.91 115.72 163.9
1999 1 116.32 117.28 165.0
1999 2 115.6 120.24 166.2
1999 3 116.9 123.21 167.9
1999 4 118.65 125.78 168.3
2000 1 121.32 127.26 171.2
2000 2 123.09 129.24 172.4
2000 3 125.16 130.99 173.7
2000 4 128.15 132.03 174.0
2001 1 132.2 136.29 176.2
2001 2 136.56 137.76 178.0
2001 3 140.36 139.61 178.3
2001 4 144.98 140.99 176.7
2002 1 149.12 141.7 178.8
2002 2 154.71 143.45 179.9
2002 3 161.09 145.22 181.0
2002 4 166.01 147.36 180.9
2003 1 170.76 148.43 184.2
2003 2 176.21 149.34 183.7
2003 3 180.8 150.23 185.2
2003 4 190.87 151.72 184.3
2004 1 197.66 153.28 187.4
2004 2 206.91 154.66 189.7
2004 3 220.75 156.69 189.9
2004 4 230.2 157.68 190.3
2005 1 242.23 160.22 193.3
2005 2 258.75 161.23 194.5
2005 3 276.27 163.58 198.8
2005 4 294.66 166.15 196.8
2006 1 309.5 168.72 199.8
2006 2 321.9 171.22 202.9
2006 3 331.11 173.67 202.9
2006 4 337.48 175.05 201.8
2007 1 342.38 177.33 205.352
2007 2 343.25 180.2 208.352
2007 3 337.97 180.99 208.490
2007 4 331.22 182.13 210.036
2008 1 315.56 184.22 213.528
2008 2 291.89 184.8 218.815
2008 3 256.05 185.97 218.783
2008 4 236.14 185.6 210.228
2009 1 226.16 188.27 212.709
2009 2 213.32 186.76 215.693
2009 3 199.05 185.45 215.969
2009 4 200.55 185.04 215.949
2010 1 195.61 183.8 217.631
2010 2 193.88 184.21 217.965
2010 3 196.88 184.76 218.439
2010 4 194.03 184.45 219.179
2011 1 184.56 181.18 223.467
2011 2 180.05 180.76 225.722
2011 3 181.61 182.19 226.889
2011 4 184.29 182.73 225.672
2012 1 180.57 183.84 229.392
2012 2 181.79 186.3 229.478
2012 3 186.15 187.69 231.407
2012 4 193.27 189.83 229.601
2013 1 196 191.83 232.773
2013 2 205.2 195.45 233.504
2013 3 210.22 200.46 234.149
2013 4 217.64 205.1 233.049
2014 1 222.3 209.17 236.293
2014 2 228.56 217 238.343
2014 3 236.95 224.51 238.031
2014 4 242.48 227.68 234.812
2015 1 244.42 230.27 236.119
2015 2 251.76 237.35 238.638
2015 3 260.12 240.63 237.945
2015 4 265.12 241.86 236.525
2016 1 272.37 242.67 239.261
2016 2 277.33 247.59 241.038
2016 3 283.61 250.61 241.428
2016 4 289 253.22 241.432
2017 1 295.67 254.5 243.524
2017 2 302.09 262.22 244.737
2017 3 306 262.74 245.708
2017 4 313.69 267.44 246.524
2018 1 319.81 269.76 249.554
2018 2 327.43 277.07 251.989

A6

a.
b.

A7

Current share price $ 68.00 per share
Number of shares outstanding 120 million
Expected next year revenue $ 750 Million
Expected next year total expenses (including interest and depreciation) $ 360 Million
Expected next year depreciation $ 90 Million
Expected next year interest payment $ 70 Million
Total Debt $ 1.8 Billion
Current range of CAP for office space in the US (depending on quality and location) 4.50% to 6.00%
Required unlevered rate of return 8.00%
Expectet NOI graowth rate 2.50%
a. Answer:
Show your work:
72
b. Answer:
Show your work:

A8

a.
b.
c.

A9

a.
b.
c.
d.
e.
f.
g.

A10

a. Answer:
Show your work:
b. Answer:
Show your work:

A11

a.
b.
c.

A12

a.
b.
c.

A13

a. Answer:
Show your work:
b.
c.

A14

a.
b.
c.

A15

Spring 18