You are the developer of a new manufacturing space in Cougarville, USA. This is a build-to-suit project for a creditworthy manufacturer. You have signed a 10-year lease on the following basic terms: 1. Lease is Net Net Net to the landlord meaning that tenant will pay, in addition to its base rent, all expenses and basic maintenance on the building and capital improvements for the term of the lease. The tenant will pay directly for expenses such as utilities, landscaping, maintenance, etc.; however, they will reimburse the landlord for real estate taxes and property insurance. As it is a new building, no capital improvements are expected. 2. Tenant has agreed to pay a base rent (to be determined) that will allow the Landlord to earn a return on total initial costs of 8.25% based on agreed project costs and a 5% market vacancy that includes base rent and reimbursable expenses. 3. Tenant will accept the building at completion at which time they will commence their specific upfit at their cost. They have agreed to pay 50% of base rent and 100% of reimbursable expenses in year one (2022) and 100% of base rent and reimbursable expenses thereafter. Base rent rate will be flat for the first two years and then will increase at 75% of the previous year's CPI each year thereafter. The expected CPI increase will be 3% per year. 4. As a side note, the building is a generic structure that is readily adaptable for other users now or in the future. The tenant will be responsible for constructing and funding all its specific manufacturing requirements as well as for removing them when they vacate. Building attributes are as follows: 1. 200,000 gross/rentable building square feet (GBA) 2. Site size: 15.0 acres 3. The building will occupy approximately 31% of the site. The balance of the site is occupied by parking lots, truck maneuvering areas, outside equipment areas, landscape areas, and stormwater detention areas which are included in the hard costs. Development costs are as follows: 1. Construction period: One year (2021) 2. Land cost: $75,000 per acre 3. Hard construction costs: $60.00/SF of GBA 4. Soft costs: 15% of land and hard costs. Includes all soft costs including construction period interest and construction loan fees. All hard and soft costs will be capitalized into the value of the building for cost recovery (depreciation) 5. The Developer has agreed to pay a one-time leasing commission of $675,000 which will be funded 50% at lease signing (via equity up-front) and 50% via equity during the year of construction. Operating conditions: 1. Building is 100% preleased prior to occupancy as indicated above 2. Initial rent to achieve 8.25% return on initial cost in stabilization year (2023). Must do some iteration on Excel or via calculator to establish the starting rent. 3. Annual increase in rental income: 75% of previous year's CPI. CPI to use for years 2024-2027 is 3% per year. 4. Expenses and reserves: a. Real estate taxes of $1/ SF of GBA reimbursable from tenant commencing in 2022 with 2% annual increases. b. Property insurance of $0.35/SF of GBA reimbursable from tenant commencing in 2022 with 3.0% annual increases. c. A management fee of 2% of Gross Effective Income will be charged but which will not be reimbursable from the tenant. Sales assumptions: 1. Assume sale near the end of the 5th operating year (2026). 2. Sales price is based on 2027 expected NOI capitalized at 7.5% rounded down to the nearest $1,000. 3. Selling costs are expected to be 4% of the sales price Lending environment: 1. Construction/mini-perm loan: a. Amount: 70% of all development costs including land, hard costs, and soft costs exclusive of leasing commissions b. 4.5% interest-only through the second operating year (refinance 12/31/2023). Interest during the construction period is included in the building soft costs. Interest-only payment during the first two operating years is cash flow and the interest is deductible from taxable income. c. Close on construction loan: 12/31/2020. Initial equity goes in at that time which will pay for the land and one half of the leasing commissions with the balance held by the bank until drawn for development and construction. You must also fund as equity any expected before tax cash flow shortfall in the first operating year (when base rent will only be at 50% of the building) (Time=0). d. Construction is during 2021. The construction is funded by construction draws that are paid from the advanced equity first and then draws on the loan. Show treatment of cash flows. e. Construction Loan Maturity: End of 2023 at which time it will be paid off with the permanent loan proceeds. If the permanent loan proceeds are insufficient to pay off the construction loan and fund the permanent loan fees, then additional equity may be required. 2. Permanent loan: a. Close at the end of 2023. This will pay off the Construction loan assuming sufficient proceeds. b. Loan amortization: 25 years c. Monthly principal and interest payments but assumed at the end of the year. d. Loan Term: 10 years e. 3.5% interest rate per annum f. Loan costs: 0.75% of loan amount (treat as loan points for taxable income calculation). Must be paid from cash flow. g. Loan amount: 75% of appraised amount. Appraised amount is the lesser (rounded down to the nearest $1000 ) of: i. Stabilized year (2024) NOI capitalized at 7.75% ii. $80/SF of GBA h. Minimum Debt Coverage ratio is 1.25 in the first year of the permanent loan. i. There will be a 2% prepayment penalty based on any early loan payoff. This will be added to the selling expenses for tax treatment. Taxation conditions: 1. Depreciation is on all costs less land, leasing commissions, and permanent loan fee and is depreciated over 39 years since industrial building. 2. Depreciation commences in the first year of operation. Disregard mid-month convention. 3. Permanent loan costs are amortized over the term of the loan. Any unamortized costs may be used to reduce ordinary income in the year of sale. 4. Leasing commissions are amortized over the ten-year lease. Any unamortized costs may be used to reduce ordinary income in the year of sale. 5. Tax rates: a. Ordinary income taxed at 28%. b. Depreciation recapture taxed at 25% c. Long term capital gains taxed at 20% d. Assume that losses, if any, can be used to offset other passive income. Assignment: Prepare a detailed Excel-based Pro Forma for presentation to the investment committee (your instructor). If you intend to submit in an alternate spreadsheet format, please notify me in advance. Format neatly like it would be for a report. Proforma must show: a. All assumptions b. Prepare a timeline c. Prepare a statement of all development costs showing units costs per gross and rentable square foot