Real Estate 306: Real Estate Process, Spring 2008 Assignment #3 Name: Jennifer Bishop Student ID#: 903 804 3601 ____________________________________________________________________________ This assignment is due by 5:00pm on Friday, December 4th uploaded to the course website. Please label your files Lastname_Firstname_Name of Your TA. You must show all work or assumptions used to receive full credit on the assignment. ______________________________________________________________________________ PART I: Pro forma statement with debt finance Your company is considering making an investment in an apartment building. You will finance the purchase with a mortgage loan of 75% LTV. The term of the loan is 30 years with an interest rate of 6.0% (fully amortizing with monthly compounding and payments). You will pay off the mortgage balance when you sell the property. The building has 18 two-bedroom apartments and 8 three-bedroom apartments. You assume that the monthly rent for two-bedroom units is $2,000 and the rent for three-bedroom apartments is $2,400. The building has 50 parking spaces. The charge for each of them is $150 per month. Additional assumptions about this investment are as follows: Annual inflation of rents and parking charge 4% Vacancy and collection loss as % of PGI 5% Parking Vacancy Allowance 6% Purchase price $5,100,000 Assessed Value $4,950,000 Land value as % of the total value 15% Annual growth of assessed value for years 2 through 5 3% Property tax mill rate 20 Other Operating Expenses (% of EGI) 27% Planned holding period 4 years Terminal cap rate (applied to 5th year’s NOI) 7.5% Selling cost as a % of Gross Sales Price 2% # of Years for calculating Depreciation 27.5 Capital gain (appreciation) tax rate 15% Depreciation Recapture tax rate 25% Income tax rate 36% Your after tax required rate of return 12% Question 1: Assume that you decide to take out the mortgage with the terms outlined on Page 1. What are each year’s beginning balance, annual debt service (total debt payment), annual interest payment, and ending balance during the holding period? Year 1 2 3 4 Beginning Balance 3,825,000 3,778,029 3,728,160 3,675,216 Annual Debt Service 275,194 275,194 275,194 275,194 Interest Expense 228,222 225,325 222,249 218,984 Annual Amortization (Principal) 46,971 49,869 52,944 56,210 Ending Balance 3,778,029 3,728,160 3,675,216 3,619,006 Question 2: Fill in the chart below assuming you take out an interest only loan with a 75% LTV requirement and 6.00% rate for 4 years. Year 1 2 3 4 Beginning Balance 3,825,000 3,825,000 3,825,000 3,825,000 Annual Debt Service 229,500 229,500 229,500 229,500 Interest Expense 229,500 229,500 229,500 229,500 Annual Amortization (Principal) 0 0 0 3,825,000 Ending Balance 3,825,000 3,825,000 3,825,000 0 Question 3: fill in the following proforma and assume that you are going to take out the regular mortgage described on PAGE 1 (i.e. 75% LTV, 30 years, interest rate of 6.0%, fully amortizing with monthly compounding and payments). Pro Forma Statement Yearly Cash Flows from Operations PRIVATE tc \l 1 "See Spreadsheet" PRIVATE Year 1 Year 2 Year 3 Year 4 Year 5 Potential Gross Income 662,400 688,896 716,452 745,110 774,914 (Less Vacancy & Coll.) (33,120) (34,445) (35,823) (37,256) (38,746) Potential Other Income 90,000 93,600 97,344 101,238 105,287 (Less Other Income Vacancy) (5,400) (5,616) (5,841) (6,074) (6,317) Effective Gross Income 713,880 742,435 772,132 803,018 835,138 Memo: Assessed Value 4,950,000 5,098,500 5,251,455 5,408,999 5,571,269 (Less Property Taxes) (99,000) (101,970) (105,029) (108,180) (111,425) (Less Other Operating Expenses) (192,748) (200,457) (208,476) (216,815) (225,487) Net Operating Income 422,132 440,008 458,627 478,023 498,226 (Less Depreciation) (157,636) (157,636) (157,636) (157,636) (Less Interest Expense) (228,222) (225,325) (222,249) (218,984) Memo: Taxable Income 239,121 57,047 78,742 101,403 Income Taxes 13,059 20,537 28,347 36,505 Net Operating Income 422,132 440,008 458,627 478,023 (Less Annual Debt Service) (275,194) (275,194) (275,194) (275,194) Before Tax Cash Flow 146,938 164,814 183,433 202,829 (Less Income Taxes) (13,059) (20,537) (28,347) (36,505) After Tax Cash Flow 133,879 144,277 155,086 166,324 Reversion Cash Flow PRIVATE Gross Sales Price 6,643,013 (Less Selling Cost) (132,860) Net Sales Price 6,510,153 (Less Outstanding Mortgage Balance) (3,619,006) Before Tax Cash Flow (BTCF) from Reversion 2,891,147 PRIVATE Net Sales Price 6,510,153 (Less Original Purchase Price) (5,100,000) Capital Gain from Appreciation 1,410,153 Capital Gain Tax on Appreciation 211,523 Total Accumulated Depreciation (Recapture) 630,544 Recapture Tax 157,636 Total Capital Gain Tax 369,159 BTCF from Reversion 2,891,147 (Less Total Capital Gain Tax) (369,159) ATCF from Reversion 2,521,988 Total Cash Flows PRIVATE Time 0 Year 1 Year 2 Year 3 Year 4 Total After Tax Cash Flows -1,275,000 133,879 144,277 155,086 2,688,312 Required Rate of Return 12% Net Present Value (NPV) $778,410 Internal Rate of Return (IRR) 27.8% Question 4: Should you invest in this property under the terms and assumptions outlined? Why or why not? Yes. The NPV is positive and the IRR is above the required rate of return of 12%. Both facts indicate a profit from this investment. Question 5: Suppose the After Tax IRR of this project without using any debt financing is 11.00%. This would mean that the IRR on total capital invested is 11.00%. How does this compare with the IRR you calculated that does include debt financing? Does this represent positive or negative financial leverage? This IRR is much lower than the debt financing IRR – it’s one percent below the required rate of return. This is negative financial leverage since using debt decreases the investor’s return. Question 6: If you choose to take out an interest only loan with 6.0% interest rate, what is the after tax effective interest rate? Assume a 36% income tax rate. ATEIR = Interest Rate x (1 – income tax rate) = 6% x (0.64) = 4% PART II: Financing Constraints *****NOTE: ASSUME MONTHLY PAYMENTS - 12 PER YEAR***** Question 7: You are considering investing in a property that has a first year NOI of $950,000. Market cap rates for this type of property are at 6.75%. What do you expect to pay for the building? Value = NOI / cap rate = $950,000 / 6.75% = $14,074,074.07 Question 8: The seller is asking $15,000,000 for the building. You approach the bank about arranging a loan, and the bank's appraiser also concludes that the value of the property is $15,000,000. The following are the bank’s standard terms for commercial mortgages: LTV 75% DSCR 1.1 Term 7 years Amort 30 years Rate 7.25% (monthly compounding) Will the bank be willing to loan you the entire $15,000,000 for the property? What is the most obvious fact supporting your conclusion? (Use assumptions from above) No. The LTV only allows for the bank to lend 75% of the amount requested ($15 million). Question 9: What is the most that the bank will be willing to loan you? Why? (Use assumptions from above) $15,000,000 x 75% LTV = $11,250,000 loan NOI = $950,000 / 12 = $79,167 average monthly NOI DSCR = monthly NOI / monthly DS = $79,167 / 1.1 = $71,970 monthly DS PMT = -$71,970 N = 30 x 12 = 360 IR = 7.25% P/Y = 12 FV = 0 So PV = $10,550,05 The bank will not be willing to lend more because your DSCR indicates your NOI is only slightly more than your annual debt payments. The lender probably wants a bigger NOI cushion to protect against default, and a 1.1 DSCR isn’t enough to lend you even the full amount you could be given with a 75% LTV. Question 10: What will the balloon payment be at the end of the loan term? (Use assumptions from above) Term – 7 years PV = $10,550,059 IR = 6% FV = 0 N = 30 x 12 = 360 P/Y = 12 So PMT/mo = $63,253 N = 7 x 12 = 84 So FV = $9,457,008 balloon payment. Question 11: Ignoring the LTV constraint, what would the annual NOI have to be to justify a $12,000,000 loan? (Use assumptions from above) PV = $12,000 FV = 0 IR = 6% P/Y = 12 N = 360 PMT/mo = $71.95 ADS = $71.95 x 12 = $863.35 Annual NOI = DSCR x ADS = 1.1 x $863.35 = $949.69 NOI PAGE 5