Page of 4 O - ZOOM The landlord covers $4/SF of operating expenses, which increase by 4% each year (expense inflation). The roof has to be replaced in year 3. The total estimated cost is $400,000 with a reserve of $200,000 created in years 1 and 2. For simplicity, no other capital expenses are assumed, and no tenant improvements and leasing commissions are incurred for new or renewing tenants. Assume that the building is bought in January 2023 and sold in December 2027, i.e., the investor expects to hold the building for 5 years. The building is depreciated over 39 years (mid-month convention for first and last year) and the value of improvements (building) is considered to be 85% of the purchasing price. The new roof is depreciated over 39 years (mid-month convention). The going-out cap rate is 6.5% and the investor requires a return of 12%. Selling costs are 2.5% of the sales price. Assume an income tax of 35% and a capital gains tax of 15%. Make sure you tax the depreciation recapture and capital gain with the appropriate tax rates. Assume the investment is financed using Mortgage Option A (the fixed rate mortgage in Part 2) Q Search S. 1 80 prise Delete O F10 F11 F12 \\ Insert Fo F6 Backspace S % O 5 O P R F G H J K V B N M C& Individual Project 1, Part 1 M Excel SIMpath 3 (BA 325 Revision x + https://canvas.pdx.edu/courses/74420/assignments/769985?module_item_id=3542858 Project 1_Fall2023.pdf Download 2 of 4 0 ZOON The third tenant occupies the remaining space, and their current rent is $20/SF/year. The lease will expire at the end of year 6. Rental increases of $1.5 per SF will occur at the beginning of year 2, year 3, and year 4. There are no increases for years 5 and 6. Note: Assume the current contract rents as the basis for your year 1 potential gross income. V&C for the first two years is assumed to be 1%. From year three assume a V&C of 6% of the PGI each year, which will increase to 10% from year 4. The average market rent for office is currently $23/SF/year, and it is forecasted to decrease by 5% each year for two wears and then increase again at 3% each year for the subsequent years. Renewing tenants receive a $1/SF discount to the market rental rate. The landlord covers $4/SF of operating expenses, which increase by 4% each year (expense inflation). The roof has to be replaced in year 3. The total estimated cost is $400,000 with a reserve of $200,000 created in years 1 and 2. For simplicity, no other capital expenses are assumed, and no tenant improvements and leasing commissions are incurred for new or renewing tenants. Assume that the building is bought in January 2023 and sold in December 2027, i.e., the Q Search S. 18 prise F10 F12 Insert F3 F6 S 5 6 9 O 3 O P E R D F G H K C V B N MX M Excel SIMpath 3 (BA 325 Revisio X ourses/74420/assignments/769985?module_item_id=3542858 Page 3 ASSIGNMENTS Part 1: Assuming that the investor wants to hold the property for 5 years, create the proformas for cash flows from operations and equity reversion until the NOI and net selling price level respectively. Conduct a discounted cash flow analysis (DCF) to calculate the IRR and NPV based on NOI and net selling price for this investment. Is this investment worth undertaking? Why/why not? For this and other parts/sections, you can add your answers to my questions to the respective spreadsheet and do not need to prepare a separate file. Part 2: Mortgage Option A (Fixed rate mortgage): The investor received a lender's offer for a 25-year mortgage at 7% (compounded monthly) with a loan-to-value ratio (LTV) of 80%. No financing costs (e.g., origination fees) or discount points occur. . Calculate the after-tax NPV and IRR for this option. Is this investment on its own Q Search L& Individual Project 1, Part 1 * M Excel SIMpath 3 (BA 325 Revision x + C https://canvas.pdx.edu/courses/74420/assignments/769985?module_item_id=3542858 A 3 FIN457_Outline Proformas and DCF_Project1.xisx & Download iInfo - ZOOM + Pro-Forma Statement: Operating Cash Flows Potential Gross Income Project 1, part 1 Project 1, part 2 Vacancies & Collection losses Effective Gross Income Operating Expenses Net Operating Income (NOI) CAPEX Debt Service Before Tax Cash Flow Tax After Tax Cash Flow Tax liability NOI Depreciation Interest Taxable income Tax Pro-Forma: Equity reversion Sales price Capital Gains Tax Selling costs NSP Net Sales Proceeds (NSP) Adjusted basis Recaptured depreciation Mortgage balance Taxable income Capital gain BTER Taxes Taxes LATER 1 50'F Q Search J C H B LU O OX Heavy rain IS insert 18 prisc Delete F10 F11 F12 2 F5 F2 Backspace # $ Vo @ 9 3 A D Q W E K A S D F G H N M Z X C V B