Excel sheets needed to be provided for answering this case study assignment
Attached are the case study instruction and two reference documents from another case study( may applied)
Year-One BTCF YEAR ONE INCOME AND EXPENSE STATEMENT OFFICE BUILDING DOWNTOWN JACKSONVILLE MARKET Income: Office Rental Income 18,000 sf @ $20.00 psf$360,000 Parking Income 120 spaces @ $90/month/space$10,800 Potential Gross Income:$370,800 Less: Vacancy and Collection Allowance (10%)($37,080) Effective Gross Income:$333,720 Expenses: Management Fees (5% of EGI)$16,686 Real Estate Taxes$9,000 Hazard Insurance$3,000 Maintenance/Repairs$11,000 Supplies$4,000 Capital Repair Allowance$8,000 Administration Costs$4,500 Garage Operating Costs$6,500 TOTAL EXPENSES$62,686 Net Operating Income$271,034 Debt Service($115,974) Before Tax Cash Flow$155,060 Forecasted BTCF FORECASTED INCOME AND Expense STATEMENT OFFICE BUILDING DOWNTOWN JACKSONVILLE MARKET Income:Year 1Year 2Year 3Year 4Year 5Year 6 Office Rental Income 18,000 sf @ $20.00 psff$360,000$370,800$381,924$393,382$405,183$417,339 Parking Income 120 spaces @ $90/month/space$10,800$11,124$11,458$11,801$12,155$12,520 Potential Gross Income:$370,800$381,924$393,382$405,183$417,339$429,859 Less: Vacancy and Collection Allowance (10%)($37,080)($38,192)($39,338)($40,518)($41,734)($42,986) Effective Gross Income:$333,720$343,732$354,044$364,665$375,605$386,873 Expenses: Management Fees (5% of EGI)$16,686$17,187$17,702$18,233$18,780$19,344 Real Estate Taxes$9,000$9,270$9,548$9,835$10,130$10,433 Hazard Insurance$3,000$3,060$3,121$3,184$3,247$3,312 Maintenance/Repairs$11,000$11,220$11,444$11,673$11,907$12,145 Supplies$4,000$4,080$4,162$4,245$4,330$4,416 Capital Repair Allowance$8,000$8,160$8,323$8,490$8,659$8,833 Administration Costs$4,500$4,590$4,682$4,775$4,871$4,968 Garage Operating Costs$6,500$6,630$6,763$6,898$7,036$7,177 TOTAL EXPENSES$62,686$64,197$65,745$67,332$68,960$70,628 Net Operating Income$271,034$279,535$288,298$297,332$306,645$316,245 Debt Service($115,974)($115,974)($115,974)($115,974)($115,974)($115,974) Before Tax Cash Flow$155,060$163,561$172,324$181,358$190,671$200,271 BTER CALCULATION OF BEFORE-TAX EQUITY REVERSION OFFICE BUILDING DOWNTOWN JACKSONVILLE MARKET Estimated Future Property Value NOI: Year 6$316,245 Div. by Cap. Rate:12% Estimated Value$2,635,374 Selling/Closing Costs:10.00% Remaining Loan Balance (end of Year 5):$1,348,981 Appreciated Value$2,635,374 Less: Selling/Closing Costs($263,537) Net Proceeds From Sale$2,371,836 Less: Unpaid Mortgage Balance($1,348,981) Before-Tax Equity Reversion (BTER)$1,022,855 DCF Analysis DISCOUNTED CASH FLOW ANALYSIS OFFICE BUILDING DOWNTOWN JACKSONVILLE MARKET Discount Factors (10% rate of return): YearDiscount Factor 10.9091 20.8265 3 0.7513 40.6830 50.6209 60.5645 YearBTCFBTERTotal CF'sDiscount FactorPV of the CF's 1$155,060$155,0600.9091$140,965 2$163,561$163,5610.8265$135,183 3$172,324$172,3240.7513$129,467 4$181,358$181,3580.6830$123,868 5$190,671$190,6710.6209$118,388 6$1,022,855$1,022,8550.5645$577,402 Present Value of the CF's and Reversion:$1,225,273 Present Value of Property$1,225,273 Less: Initial Equity Invested($2,000,000) Net Present Value($774,727) Trimester II 2019 Tutorial 8 Solutions Question 1 You have the opportunity to invest in an office building in Samabula, in which you would purchase as a 5-year holding investment with a 10% required rate of return (discount rate). The building offers a total rentable area of 20,000 square feet, and 100 garage-parking spaces. You are provided the following information on the property from the current owner: 1. The owner is asking $3,500,000 for the building. 2. The owner has annual contracts from tenants on the building for 18,000 square feet of the total space at $20.00 psf, gross. Additionally, 90 parking spaces are leased by annual contract for $120.00 per month, per space. You expect this same office vacancy to occur into perpetuity, and expect parking vacancy to equally correlate with the office vacancy. These lease terms are found to be in line with the downtown office market. 3. Current annual operating expenses for the building follow: a. Management Fees: 5% of EGI b. Annual Real Estate Taxes: $9,000 c. Hazard Insurance: $3,000 d. Maintenance/Repairs: $11,000 e. Supplies $4,000 f. Capital Replacement Allowance: $8,000 g. Administrative Costs: $4,500 h. Operating Costs of the Garage: $6,500 Your mortgage lender has committed to you a loan to purchase the office building (should you decide to partake in the investment), which would offer the following terms: Loan-to-Value: hard equity $1,500,000 Mtg; $2,000,000 Interest Rate: 6% FRM Loan Duration: 25 years, fully amortizing A study of the office building market in Samabula indicates the following trended increases in incomes and expenses, which can safely be assumed in analysis forecasts: a. Office rents: 3% per annum b. Parking rents: 3% per annum c. Real Estate Taxes: 3% per annum d. Other Operating Expenses: 2% per annum https://www.coursehero.com/file/45345425/Tutorial-8-Solutionspdf/ Th is stu dy re so ur ce w as sh are d v ia Co ur se He ro .co m https://www.coursehero.com/file/45345425/Tutorial-8-Solutionspdf/ Required: Based on the above information, prepare a reconstructed year-one income and expense statement, and a reconstructed income and expense statement forecasting returns for the 5- year holding period (six years analysis required). For both statements, calculate through Before Tax Cash Flows. Question 2 The Suva Point Apartment Building is located at the intersection of Laucala Bay and Fletcher Road in Suva. The location is very appealing and is amongst the most preferred locations in Suva. Mr. Sandeep Narayan the property Manager provides you with the following information: Total acquisition price: $1,150,000 Property consists of 8apartment units, 4 on the first floor and 4 on the second floor Contract rents: FJD1,500 per month (all 2 bedroom units, fully furnished) Annual market rent increases at 2.5% per year Vacancy and collection loss: 1% per year Operating expenses: 18% of EGI Capital Expenditure: 4% of EGI Holding period: 5 years Capitalized at 8% Selling expense in year 5: $85,900 First mortgage loan: LTV ratio 1:8 Annual Mortgage interest rate: 6.99% Loan term: 30 years Total upfront financial cost: 2.5% of loan amount and commission of $5,520. Present Value Factor at 10.15% Equity Discount Rate: 16% Required: a) Using the information provided, make a 5 year Pro-Forma of the cash flow and estimate the selling price using the cap rate provided. b) Calculate the Harbor Point Mortgage financing and before tax equity reversion. c) Calculate the Net Present Value (NPV) and Internal Rate of Return (IRR), also justify whether we should accept or reject the investment. https://www.coursehero.com/file/45345425/Tutorial-8-Solutionspdf/ Th is stu dy re so ur ce w as sh are d v ia Co ur se He ro .co m https://www.coursehero.com/file/45345425/Tutorial-8-Solutionspdf/ Question 2 Solutions Year 1 2 3 4 5 6 PGI 144,000 147,600 151,290 155,072 158,949 162,923 VC 1,440 1,476 1,513 1,551 1,589 1,629 EGI 142,560 146,124 149,777 153,522 157,360 161,294 OE 25,661 26,302 26,960 27,634 28,325 29,033 CAPX 5,702 5,845 5,991 6,141 6,294 6,452 NOI 111,197 113,977 116,826 119,747 122,740 125,809 ADS 73,375 73,375 73,375 73,375 73,375 BTCF 37,822 40,601 43,451 46,372 49,365 1,573,000 Less Selling Expenses 85,900 1,487,100 Year NOI NSP TCF PV @ 10.15% PV 1 111,196.80 111,196.80 0.907853 100,950.34 2 113,976.72 113,976.72 0.824197 93,939.26 3 116,826.14 116,826.14 0.748250 87,415.11