Real Estate Finance questions. Home work must be done in EXCEL and functions must be embedded. Attached is the Homework Instructions. Also attached is an excel file (Chapter 16 Construction) that you will need to refer to for one of the questions. Chapter 16 #4. Please do not plagiarize for the first few questions where you are asked to give a written answer.

Chapter 16 Financing Proposed Projects with Construction Loan Spreadsheet Limitations: Twelve month draw period, where first % draw is only for 4 months. All yellow colored cell are data input cells. Set all data input cells that do not apply equal to zero (i.e. if there is constant growth, set the growth for "Yr2-Yr3" to zero. Property Appreciation Selling Costs Gross Building Area Gross Leasable Area Site Acq. & Closing On/Off Site Costs Hard Costs Soft Costs Leasing Comissions Rent Rent Growth Overage Percentage Overage on Excess over Average Sales Sales Growth Yr 2 - Yr 3 Sales Growth Tenant Reinmbursement Tenant Reinb. Growth Expenses Exp. Growth Yr 2 - Yr 3 Expenses Growth Vacancy Yr 2 Vacancy beginning Yr 3 Data Input Box: 6.00% per year Holding Pd. (after con) 2.00% of sale price Permanent Financing 120,000 Loan Fee 110,000 Loan Fee Amort 2,500,000 Loan Amortization 850,000 Loan Term 7,055,500 Interest Rate 1,576,786 Payments per Year 45,300 Construction Loan 15 per GLA Loan Amount 6.00% per year Term 5.00% on gross sales % drawn first 4 mos. 200.00 per GLA % drawn last months 200.27 per GLA Interest Rate 316% Loan Fee 4.00% per year Tax Considerations 8.39 per GLA, yr 2 Marginal Tax Rate 4.00% per year Capital Gains Rate 9.68 per GLA Depreciation 2.59% Capital Improvements 4.16% per year Capital Improvements 30.00% Tenant Improvements 5.00% Tenant Improvements Site Acq. & Close On/Off-Site Costs Hard Costs Soft Costs Total Cost Breakdown 2,500,000 Total Project Costs 850,000 Loan Amount 7,055,500 Equity Needed 1,576,786 Total Project Costs $11,982,286 Perm Loan Fee Amount 5 years 3.00% of loan 10 years 25 years 10 years 0.12 12 $ 8,309,000 12 months 75.00% 25.00% 12.00% 2.00% 28.00% 28.00% 31.5 years - S/L 90.00% of total 7 years - DDB 10.00% of total $ 11,982,286 8,741,760 3,240,526 $ 262,253 CONSTRUCTION LOAN DRAWS & REPAYMENT: End of Month 0 1 2 3 4 5 6 7 8 9 10 11 12 Total Project Costs $ $ 1,557,938 1,557,938 1,557,938 1,557,938 259,656 259,656 259,656 259,656 259,656 259,656 259,656 259,656 8,309,000 $ Interest 0 $ 15,579 31,315 47,207 63,259 66,488 69,749 73,043 76,370 79,730 83,124 86,552 692,416 Ending Balance 1,557,938 3,131,454 4,720,706 6,325,851 6,648,766 6,974,910 7,304,315 7,637,014 7,973,041 8,312,427 8,655,208 9,001,416 Payoff 0 0 0 0 0 0 0 0 0 0 0 8,741,760 Lender's Cash Flow $ 174,835 (1,557,938) (1,557,938) (1,557,938) (1,557,938) (259,656) (259,656) (259,656) (259,656) (259,656) (259,656) (259,656) 8,741,760 Yield to Lender SUMMARY OF PERMANENT LOAN 2 3 4 1,104,844 1,104,844 1,104,844 8,682,752 8,616,260 8,541,335 1,045,836 1,038,352 1,029,919 59,008 66,492 74,925 Year of Loan Payment Mortgage Balance Interest Principal 15.35% 5 1,104,844 8,456,908 1,020,417 84,427 6 1,104,844 8,361,773 1,009,709 95,135 SUMMARY OF DEPRECIABLE COSTS On/Off Site Improvements 850,000 Hard Costs 7,055,500 (Does not include leasing commissions, Soft Costs 1,094,398 Permanent loan fees or construction loan Total Depreciable Costs 8,999,898 fees) Year Site Acq. and Closing Site Improvements Hard Costs Soft Costs* 0 2,500,000 1 BEFORE AND AFTER-TAX CASH FLOWS: 2 3 850,000 7,055,500 447,282 *(less interest & loan fees) Permanent Loan Fee Construction Loan Fee Construction Interest Total Constr. Outflow Less: Total Draws Total Equity Needed 262,253 174,835 $ 2,937,088 0 2,937,088 $ 692,416 9,045,198 8,741,760 303,438 4 5 6 Year Rent Overage Tenant Reimbursements PGI Less: Vacancy EGI Total Expenses Net Operating Income Less: Debt Service Before-Tax Cash Flow Net Operating Income Less: Interest Depreciation Capital Imprvmnt Tenant Imprvmnt Amortization: Constr Loan Fee Perm Loan Fee Leasing comm. Taxable Income Taxes After-Tax Cash Flow 1 (303,438) OPERATING PERIOD CASH FLOWS 2 3 1,650,000 1,749,000 29,997 124,788 923,000 959,920 2,602,997 2,833,708 780,899 141,685 1,822,098 2,692,022 1,064,800 1,092,402 757,298 1,599,620 1,104,844 1,104,844 (347,546) 494,776 4 1,853,940 129,779 998,317 2,982,036 149,102 2,832,934 1,137,847 1,695,087 1,104,844 590,243 5 1,965,176 134,970 1,038,250 3,138,396 156,920 2,981,476 1,185,183 1,796,294 1,104,844 691,450 6 2,083,087 140,369 1,079,780 3,303,236 165,162 3,138,074 1,234,487 1,903,587 1,104,844 798,743 757,298 1,599,620 1,695,087 1,796,294 1,903,587 1,045,836 1,038,352 1,029,919 1,020,417 1,009,709 257,140 257,140 257,140 183,671 257,140 131,194 257,140 93,710 257,140 66,936 0 (174,835) (48,954) 26,225 0 (829,043) (232,132) 26,225 0 94,231 26,385 26,225 0 250,609 70,170 26,225 0 398,802 111,665 26,225 0 543,576 152,201 (254,484) (115,414) 468,391 520,073 579,785 646,541 BEFORE and AFTER-TAX CASH FLOW SUMMARY 1 2 3 (303,438) (347,546) 494,776 (254,484) (115,414) 468,391 4 590,243 520,073 174,835 AFTER-TAX CASH FLOW FROM SALE Sale Price (received by investor) 16,035,002 Sales costs 320,700 Mortgage Balance 8,361,773 Before-tax Cash Flow 7,352,528 Original Cost Basis Accum Dep & Amort Adjusted Basis 11,982,286 2,324,312 9,657,974 Capital Gain Tax from Sale After-Tax Cash Flow from Sale Year Before-Tax Cash Flow After-Tax Cash Flow BTCF IRR ATCF IRR 6,056,328 1,695,772 5,656,756 0 (2,937,088) (2,937,088) 20.95% 17.35% Note: The calculation of taxable income differs slightly from the book for the following reasons: 1) Leasing commissions were not amortized in the book as they should be as shown above. 2) The amount in the book for depreciation of tenant improvements in year 5 was wrong in the book. 5 691,450 579,785 6 8,151,271 6,303,298 BUSI 422 HOMEWORK 7 INSTRUCTIONS *Use a Microsoft Excel spreadsheet to calculate the solutions for the problems listed below. You must demonstrate your detailed calculations by embedding the formulas/functions in the cells, rather than displaying those details as you would in a word processing document!!!!!!!!! Chapter 16 Questions : 2, 4, 5, 10, 14, 16 2. What are some development strategies that many developers follow? Why do they follow such strategies? 4. Describe the process of financing the construction and operation of a typical real estate development. Indicate the order in which lenders who fund project development financing are sought and why this pattern is followed. 5. What contingencies are commonly found in permanent or take-out loan commitments? Why are they used? What happens if they are not met by the developer? 10. Why don't permanent lenders usually provide construction loans to developers? Do construction lenders ever provide permanent loans to developers? 14. What is sensitivity analysis? How might it be used in real estate development? 16. Why is the practice of \"holdbacks\" used? Who is involved in this practice? How does it affect construction lending? Chapter 16, Problem: 4 Use the attached "Chapter 16 Construction" Excel file to calculate your answers. Do NOT submit this spreadsheet. Instead, record your answers on the document with your responses to the questions in Part 1. 4. Excel. Refer to the \"Ch16 Const\" tab in the Excel Workbook provided on the Web site. a. What is the yield to the lender and the investor's after-tax IRR if 90 percent of the loan must be drawn during the first four months and 10 percent during the last eight months? b. Repeat (a) assuming 60 percent of the loan is drawn the first four months and 40 percent the last eight months. CHAPTER 19 Problems: BUSI 422 1. Two 25-year maturity mortgage-backed bonds are issued. The first bond has a par value of $10,000 and promises to pay a 10.5 percent annual coupon, while the second is a zero coupon bond that promises to pay $10,000 (par) after 25 years, including accrued interest at 10 percent. At issue, bond market investors require a 12 percent interest rate on both bonds. a. What is the initial price on each bond? b. Assume both bonds promise interest at 10.5 percent, compounded semiannually. What will be the initial price for each bond? c. If market interest rates fall to 9.5 percent at the end of five years, what will be the value of each bond, assuming annual payments as in (a) (state both as a percentage of par value and actual dollar value)? Chapter 19, Problem 2 (a & b only) Use MS Excel to set up a 10-year amortization schedule including the following columns: NPER (that is the number of period remaining to maturity), Beginning Balance, Interest Paid, Principal Paid, Principal Prepaid, and Ending Balance. Remember that interest paid does not reduce the loan balance. Also, the total mortgage payment amount will decrease each year due to the prepayments, so it may be helpful to create an extra column in which to calculate the total mortgage payment for each year. If your amortization schedule is set-up correctly the loan balance after ten years will be $0. 2. The Green S & L originated a pool containing 75 ten-year fixed interest rate mortgages with an average balance of $100,000 each. All mortgages in the pool carry a coupon of 12 percent. (For simplicity, assume all mortgage payments are made annually at 12 percent interest.) Green would now like to sell the pool to FNMA. a. Assuming a constant annual prepayment rate of 10 percent (for simplicity assume that prepayments are based on the pool balance at the end of the preceding year and begin at the end of year 1), what is the price that Green could obtain if market interest rates were (1) 11 percent? (2) 12 percent? (3) 9 percent? b. Assume that five years have passed since the date in (a). What will the BUSI 422