I am not sure how to get the cash flows and can't figure the formulas.

FIN 371.01 - Spring 2017 Homework 2 - Use of Leverage 160 Spear Street - Q & A DUE: April 20, 2017 by 7PM (before start of class) Bring a hard copy of your HW2 "Q&A" page to class, and send your HW2 Excel file via email to: ngille@sfsu.edu jwarce@sfsu.edu NO LATE HOMEWORK WILL BE ACCEPTED!!! Name: Student ID: BACKGROUND: In Homework 1 you developed a 10-year proforma for 160 Spear Street (see attached). Assume the building was aquired by Tishman Speyer. You must now evaluate different financing ("leverage") alternatives for the investment and answer the questions below. 1) What is the value of the building as of Year 0, using the IRV calculation and a cap rate of 4.5%? Value: 2) Based on value in #1 above, what is the maximum 1st loan amount & resulting LTV, DCR and DYR assuming: 4.25% 55% 1.25 Interest only rate LTV (maximum) DCR (Year 1) Loan Amount: LTV: DCR: DYR: 3) Using the value in #1, and the above loan and loan payment, what is the investment IRR? Assume "exit" or residual sale price based on 5.5% cap rate. Also, what is Present Value and Net Present Value using a 6.25% discount rate 0 1 2 3 4 5 6 Investment and Cash Flow Loan and Payments Net Residual Total Net Cash Flow $ - $ - $ - $ - $ - $ - $ - $ IRR: 7 8 9 Outgoing cap rate 5.50% Net Residual 10 $ - PV: $ - $ - $ Exit Value Loan Balance - Net Residual - NPV: 4) What is the maximum second or mezzanine loan amount (in addition to the loan above) possible and the resulting overall LTV, DCR & DYR assuming: 8.50% 80% 1.35 Interest only rate LTV (maximum) DCR (Year 1) Loan Amount: LTV: DCR: DYR: 5) Assuming the 1st and 2nd loans are placed on the property, what is the investment IRR, PV and NPV (using 6.25% discount rate)? 0 Investment and Cash Flow Loan and Payments Net Residual Total Net Cash Flow 1 2 3 4 6 7 8 9 10 Net Residual $ $ - $ - $ - $ - $ - IRR: $ - $ - $ - PV: 6) What is the "baseline," unleveraged IRR on this investment? 0 Investment and Cash Flow Loan and Payments Net Residual Total Net Cash Flow Assume "exit" or residual sale price based on 5.5% cap rate. 5 $ - $ - $ - NPV: Assume "exit" or residual sale price based on 5.5% cap rate. 1 2 3 4 5 6 7 8 9 10 Net Residual $ $ - IRR: $ Exit Value Loan Balance - Net Residual - $ - $ - $ - $ PV: 7) What are at least 3 major variables that allow the IRR to be maximized? And how can you/Tishman Speyer enhance them? 1) 2) 3) 4) 5) - $ - $ - NPV: $ - $ - $ - Exit Value Loan Balance - Net Residual FIN371.01 - Spring 2017 Homework 2 - Use of Leverage 160 Spear Street - 10 Year Proforma Name: Student ID: Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 9,411,073 (485,703) (227,612) 8,697,758 4,108,748 169,197 14,316 12,990,019 9,787,516 (244,688) (136,000) 9,406,828 4,115,953 203,036 14,745 13,740,563 10,179,017 (254,475) 10,586,177 (264,654) 11,115,486 (277,887) 11,782,415 (294,560) 12,725,008 (318,125) 14,252,009 (356,300) 15,107,130 (377,678) 15,711,415 (392,785) 16,339,872 (408,497) 9,924,541 4,233,908 223,340 15,188 14,396,977 10,321,523 4,352,469 234,507 15,643 14,924,142 10,837,599 4,476,524 246,232 16,113 15,576,468 11,487,855 4,606,465 258,544 16,596 16,369,460 12,406,883 4,745,077 271,471 17,094 17,440,526 13,895,709 4,898,485 285,045 17,607 19,096,846 14,729,452 5,042,256 299,297 18,135 20,089,140 15,318,630 5,184,712 314,262 18,679 20,836,283 15,931,375 5,331,324 329,975 19,240 21,611,914 Operating Expenses (OEX) Utilities Cleaning Repairs & Maintenance Security & Life Safety Management Fees Administrative & General Insurance Real Estate Taxes Total Operating Expenses 842,470 682,282 598,179 351,716 195,700 362,593 330,273 1,127,916 4,491,129 867,744 702,750 616,124 362,267 211,654 373,471 340,181 1,150,474 4,624,666 893,776 723,833 634,608 373,136 223,302 384,675 350,387 1,173,484 4,757,201 920,590 745,548 653,646 384,330 232,234 396,215 360,898 1,196,953 4,890,415 948,207 767,914 673,256 395,859 243,846 408,102 371,725 1,220,893 5,029,802 976,654 790,952 693,453 407,735 258,477 420,345 382,877 1,245,310 5,175,803 1,005,953 814,680 714,257 419,967 279,155 432,955 394,363 1,270,217 5,331,548 1,036,132 839,121 735,685 432,566 312,653 445,944 406,194 1,295,621 5,503,916 1,067,216 864,294 757,755 445,543 331,413 459,322 418,380 1,321,533 5,665,457 1,099,232 890,223 780,488 458,910 344,669 473,102 430,931 1,347,964 5,825,519 1,132,209 916,930 803,903 472,677 358,456 487,295 443,859 1,374,923 5,990,252 Net Operating Income (NOI) 8,498,890 9,115,897 9,639,777 10,033,728 10,546,666 11,193,657 12,108,978 13,592,930 14,423,684 15,010,764 15,621,662 Capital Costs (CAPX) Tenant Improvements Leasing Commissions Reserves Total CAPX 800,656 424,897 58,285 1,283,838 377,000 167,000 60,034 604,034 1,046,000 356,000 61,835 1,463,835 150,000 60,000 63,690 273,690 210,000 210,000 65,600 485,600 123,050 84,350 67,568 274,968 452,000 176,000 69,595 697,595 311,500 110,500 71,683 493,683 157,500 82,500 73,834 313,834 175,000 85,000 76,049 336,049 578,300 206,300 78,330 862,930 Cash Flow 7,215,052 8,511,863 8,175,942 9,760,038 10,061,066 10,918,688 11,411,383 13,099,247 14,109,850 14,674,715 14,758,732 Potential Gross Revenue Base Rental Revenue Absorption & Turnover Vacancy Base Rent Abatements Scheduled Base Rental Revenue Expense Reimbursement Revenue Net Parking Income Miscellaneous Income Effective Gross Revenue CONFIDENTIAL FIN371_Homework 1 9/24/2015