I need help with all three questions... 1. Excel Problem: ve the opportunity to

Question

I need help with all three questions...

1. Excel Problem: ve the opportunity to invest in Gopher Gardens, a residential high-rise real estate property in You ha downtown Minneapolis. You expect Gopher Gardens to generate $4.5M in rent one year from now and for rent to increase at 8% per year for the following four years. Five years from now, you believe you will be able to sell the property for $30 million. Assume the interest rate is 6% per year. a. How much should you be willing to pay today for Gopher Gardens? b. If you can buy the property for $43 million, what is the NPV of this opportunity? c. Acknowledging that the dollar values above are in the future (and therefore are necessarily qi 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-O%, 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.

Explanation / Answer

$35,264,364

$        247,422

Present Value (PV) of Cash Flow: (Cash Flow)/((1+i)^N) i=Discount Rate=Interest rate=6%=0.06 N=Year of Cash Flow Growth rate of Rent=8%=0.08 Rent in Year 1= $4,500,000 Rent in Year 2= $4,860,000 (4500000*1.08) Rent in Year (N+1)=1.08*(Rent in year(N)) Terminalcash flow in year 5= $30,000,000 N Year 0 1 2 3 4 5 R Cash Flow from rent $4,500,000 $4,860,000 $5,248,800 $5,668,704 $6,122,200 S Terminalcash flow from selling property $30,000,000 CF Total Cash Flow $4,500,000 $4,860,000 $5,248,800 $5,668,704 $36,122,200 SUM PV(CF)=CF/(1.06^N) Present Value(PV) of Cah Flow 0 $        4,245,283 $      4,325,383 $ 4,406,994 $ 4,490,145 $ 26,992,609 $ 44,460,413 SUM OF PV OF CASH FLOWS $          44,460,413 Amount willing to pay maximum= $          44,460,413 b NET PRESENT VALUE (NPV)IF BOUGHT FOR $43 MILLION 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) Present Value(PV) of Cah 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) Net Present Value(NPV) $            1,460,413 c NPV WITH DIFFERENT GROWTH RATES Growth Rate N Year 0 1 2 3 4 5 0% R0 Cash Flow from rent with growth =0% $4,500,000 $4,500,000 $4,500,000 $4,500,000 $4,500,000 1% R1 Cash Flow from rent with growth =1% $4,500,000 $4,545,000 $4,590,450 $4,636,355 $4,682,718 2% R2 Cash Flow from rent with growth =2% $4,500,000 $      4,590,000 $ 4,681,800 $ 4,775,436 $    4,870,945 3% R3 Cash Flow from rent with growth =3% $4,500,000 $4,635,000 $4,774,050 $4,917,272 $5,064,790

0% CF0 TotalCash Flow with growth =0% ($43,000,000) $4,500,000 $4,500,000 $4,500,000 $4,500,000 $34,500,000 1% CF1 Total CashFlow with growth =1% ($43,000,000) $4,500,000 $4,545,000 $4,590,450 $4,636,355 $34,682,718 2% CF2 Total Cashflow with growth =2% ($43,000,000) $4,500,000 $4,590,000 $4,681,800 $4,775,436 $34,870,945 3% CF3 Total Cashflow with growth =3% ($43,000,000) $4,500,000 $4,635,000 $4,774,050 $4,917,272 $35,064,790 4% CF4 Total Cashflow with growth =4% ($43,000,000) $4,500,000 $4,680,000 $4,867,200 $5,061,888

$35,264,364

SUM OF PV of Cash Flows 0% PV0=CF0/(1.06^N) Present Value of Cash Flow growth=0% $       (43,000,000) $        4,245,283 $      4,004,984 $ 3,778,287 $ 3,564,421 $ 25,780,407 $ (1,626,618) 1% PV1=CF1/(1.06^N) Present Value of Cash Flow ,growth =1% $       (43,000,000) $        4,245,283 $      4,045,034 $ 3,854,230 $ 3,672,427 $ 25,916,945 $ (1,266,081) 2% PV2=CF2/(1.06^N) Present Value of Cash Flow , growth=2% $       (43,000,000) $        4,245,283 $      4,085,084 $ 3,930,930 $ 3,782,593 $ 26,057,598 $     (898,513) 3% PV3=CF3/(1.06^N) Present Value of Cash Flow , growth=3% $       (43,000,000) $        4,245,283 $      4,125,133 $ 4,008,384 $ 3,894,940 $ 26,202,451 $     (523,809) 4% PV4=CF4/(1.06^N) Present Value of Cash Flow , growth =4% $       (43,000,000) $        4,245,283 $      4,165,183 $ 4,086,595 $ 4,009,489 $ 26,351,584 $     (141,865) 5% PV5=CF5/(1.06^N) Present Value of Cash Flow ,growth =5% $       (43,000,000) $        4,245,283 $      4,205,233 $ 4,165,561 $ 4,126,263 $ 26,505,082

$        247,422

Growth (g) NPV=SUM OF PV OF CASH FLOWS 0% $                                                              (1,626,618) 1% $                                                              (1,266,081) 2% $                                                                  (898,513) 3% $                                                                  (523,809) 4% $                                                                  (141,865) 5% $                                                                    247,422 6% $                                                                    644,160 7% $                                                                1,048,455 8% $                                                                1,460,413 9% $                                                                1,880,144 10% $                                                                2,307,756

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.