1.a) How much should you be willing to pay today for Gopher Gardens?
Present Value (PV) of Cash Flow:
(Cash Flow)/((1+i)^N)
i=Discount Rate=Interest rate=6%=0.06
N=Year of Cash Flow
Rate of Rent growth =8%=0.08
Year 1 Rent= $4,500,000
Year 2 Rent= 4,500,000*1.08=$4,860,000
Year (N+1) Rent=1.08*(Rent in year(N))
Year 5 Terminal cash flow=$30,000,000
b) If you can buy the property for $43 million, what is the NPV of this opportunity?
Cash flow in Year 0 ($43,000,000)
N Year 0 1 2 3 4 5
CF Total Cash Flow ($43,000,000) $4,500,000 $4,860,000 $5,248,800 $5,668,704 $36,122,200 SUM
PV(CF)=CF/(1.06^N) PV of Cash Flow ($43,000,000) $ 4,245,283 $ 4,325,383 $ 4,406,994 $ 4,490,145 $ 26,992,609 $ 1,460,413
NPV=SUM of PV(CF)= $ 1,460,413
c) Acknowledging that the dollar values above are in the future (and therefore are necessarily estimates) explore what the NPV would be if your assumptions about growth are wrong. Specifically calculate the NPV for values of g between 0% and 10% (NPV @ g=0%, NPV @ g=1% etc.) You should use the Data Table utility, found in Data tab>What-if analysis>Data Table to do this. Plot your results in a connected (the dots are connected) scatter plot.
NPV $1.46 0% 1% 2% 3% 4% 5% 6% 7%
Growth rate of the rent g 8% $13.40 $11.13 $8.98 $6.95 $5.02 $3.19 $1.46 $0.19
NPV 9% 10%
Growth rate of the rent g $3.24 $4.65
d) At what value of g is the NPV = 0? (hint: use Goal Seek in the same place as Data Table)
Year 0 1 2 3 4 5
Purchase price of the property $43.00
Rental income $4.50 $4.86 $5.25 $5.67 $6.12
Sale Price of property $30.00
Total Cash Flow $43.00 $4.50 $4.86 $5.25 $5.67 $36.12
Present value of cash flow $43.00 $4.21 $4.25 $4.30 $4.34 $25.89
NPV $0.00
Growth rate of rent g 8%
Interest rate r 7%
=6.8846%
e) Repeat your analysis from the last two questions, but now change the discount/interest rate. Evaluate the NPV for all values of r between 0% and 10%. Plot the results in a connected scatter plot. This is called an NPV profile
Interest rate of r
NPV $1.46 0 % 1 % 2 % 3 % 4 % 5% 6 % 7% 8 % 9 % 10%
0 9.50 7.38 5.38 3.49 1.69 0.01 1.63 3.16 4.62 6.00 7.31
1 9.95 7.82 5.80 3.89 2.08 0.36 1.27 2.81 4.28 5.68 7.00
2 10.42 8.27 6.23 4.30 2.48 0.74 0.90 2.46 3.94 5.35 6.68
3 10.89 8.72 6.67 4.72 2.88 1.13 0.52 2.10 3.59 5.01 6.36
4 11.37 9.18 7.11 5.15 3.29 1.53 0.14 1.73 3.24 4.67 6.03
growth 5 11.87 9.66 7.57 5.59 3.71 1.93 0.25 1.35 2.87 4.32 5.69
of rent 6 12.37 10.14 8.03 6.03 4.14 2.35 0.64 0.97 2.51 3.97 5.35
g 7 12.88 10.63 8.50 6.49 4.58 2.77 1.05 0.58 2.13 3.60 5.00
8 13.40 11.13 8.98 6.95 5.02 3.19 1.46 0.19 1.75 3.24 4.65
9 13.93 11.64 9.47 7.42 5.48 3.63 1.88 0.22 1.36 2.86 4.29
10 14.47 12.16 9.97 7.90 5.94 4.07 2.31 0.63 0.96 2.48 3.92
f) At what value of r is the NPV = 0? This is called the Internal Rate of Return (IRR).
Year 0 1 2 3 4 5
Purchase price of the property $43.00
Rental income $4.50 $4.70 $4.90 $5.12 $5.34
Sale Price of property $30.00
Total Cash Flow $43.00 $4.50 $4.70 $4.90 $5.12 $35.34
Present value of cash flow $43.00 $4.25 $4.18 $4.12 $4.02 $26.41
NPV $0.00
Growth rate of rent g 4.3670%
Interest rate r 6%
=4.3670%
g) Now use a Data Table to simultaneously evaluate NPV at all values of g between 0% and 10% AND all values of r between 0% and 10%. (you should have a 10×10 matrix). Highlight cells where NPV is negative (hint: try using conditional formatting on the Home tab).
Interest rate of r
NPV $1.46 0 % 1 % 2 % 3 % 4 % 5% 6 % 7% 8 % 9 % 10%
0 9.50 7.38 5.38 3.49 1.69 0.01 1.63 3.16 4.62 6.00 7.31
1 9.95 7.82 5.80 3.89 2.08 0.36 1.27 2.81 4.28 5.68 7.00
2 10.42 8.27 6.23 4.30 2.48 0.74 0.90 2.46 3.94 5.35 6.68
3 10.89 8.72 6.67 4.72 2.88 1.13 0.52 2.10 3.59 5.01 6.36
4 11.37 9.18 7.11 5.15 3.29 1.53 0.14 1.73 3.24 4.67 6.03
growth 5 11.87 9.66 7.57 5.59 3.71 1.93 0.25 1.35 2.87 4.32 5.69
of rent 6 12.37 10.14 8.03 6.03 4.14 2.35 0.64 0.97 2.51 3.97 5.35
g 7 12.88 10.63 8.50 6.49 4.58 2.77 1.05 0.58 2.13 3.60 5.00
8 13.40 11.13 8.98 6.95 5.02 3.19 1.46 0.19 1.75 3.24 4.65
9 13.93 11.64 9.47 7.42 5.48 3.63 1.88 0.22 1.36 2.86 4.29
10 14.47 12.16 9.97 7.90 5.94 4.07 2.31 0.63 0.96 2.48 3.92
2.a) Assuming that college costs continue to increase an average of 6% per year and that all her college savings are invested in an account paying 8% interest, then what is the amount of money she will need to have available at age 18 to pay for all four years of her undergraduate education?
FV = PV (1 + i) N = $20,000(1.06)18 = $381,600
PV=C*1/r-g(1-(1+g/(1+r)) ^N) (1+r)
=$381,600*1/0.08-0.06(1-(1+0.06/1+0.8) ^4)(1+0.08)=$1,484,521.03
b) How much does the couple need to save every year until their child’s 18th birthday to achieve their goal, assuming they make their first savings payment on their child’s first birthday, the last one on her 18th birthday? Assume they save the same amount every year.
FV = PV(1 + i)N = $20,000(1.06)18 = $381,600
3.a) If the first tuition payment is due one year after the scholarship is endowed, and you would like the scholarship to pay all tuition for one student per year for the twenty years following the creation of the endowment, how much money do you need to endow the scholarship?
Annual rate tuition fee =1000
Rate of Inflation=3%
Interest rate=7%
Real rate of return= ((1+B3)/(1+B2))-1=3.88%
Years=20
The future value of endorsement in 20 years=(FV)(B4, B5,-B1)=$2,94,100.31
b) If you would like the scholarship to pay for tuition for one student per year forever, how much money do you need to endow the scholarship?
c) You plan to start saving for the endowment starting your first year after graduation (meaning first savings is one year after graduation). You plan to increase the amount you save each year by 5%, because you expect to have more income per year as time goes on. How much money do you need to save in the first year, so that you will have enough to endow the scholarship from part b (that pays tuition forever)?
(1+B15)/(1+B14)-1=6.80%
d) HOW would your answer to part c. change if your savings earned 10% per year instead of 7% per year.
B16+5%=11.800%
4. a) What is the replicating portfolio for security B? (the number of shares of the other assets, ASSUME YOU CAN BUY/SHORT FRACTIONS OF EACH SECURITY)
b) What is the no-arbitrage price of asset B?
c) If security B is observed to be trading for $45 but all the other prices stayed the same, how would take advantage of this arbitrage opportunity and how much arbitrage profit would you make for every share of security B you trade in?
