Building Information (Scenario A) Land Capital Improvments Developers Fee Year 1 Year 2 Year 3 Year + Year 5 Rent Limit Open fired) Capital Reservent Rent Growth Year 2 Vacancy Year 1.5 Vacancy Terminal Cap Rate Years Scenario A Pro Forms Year Poteatail Grecs Income Vacancy Effective Gree: Income Total Uses of Punca Ops Net Operating Income First Mortgage Second Mortgage Capital Reserve Before Tax Cash Flow Sources First Mortege Interest Rate Payment Sole: Price Outstanding Principal 1st Mrte Outstandige Principal 2st Mrte Net Sales Revesses Second Mortgage Amount Term Interest Rate Payment Year Year 1 Year 2 Year 3 Year 4 Year 5 Investor Reture Erity Developer Fee BTCT Debt Remaining Sales Proceeds Net Retur TRR Building Information (Scenario B) Year 1 Year 2 Year 3 Year 4 Year 5 Rent Oplnit (fixed) Capital Reservent Rent Growth Year 2 Vacancy Year 1 Vacancy Year 4.5 Vacancy Terminal Cap Rate Years Scenario B Pro Forms Year Potentsil Gross Income VACACY Effective Gross Lacome OpEx Net Operating Income First Mortgage Second Mortgage Capital Reserve Before Tax Cables Sales Price OutstaadisPriecpollst Mrte Outstandis Priscipl 2st Mrta Natale Revenues Year Year 1 Year 1 Year 3 Year 9 Year 10 Investor Retar Equity Developer Fee BTCF Sales Proceeds Net Retor IRR Suppose you're considering developing new apartments near Iowa State. You've planned a 65 unit building, which will cost a total of $17.8 million dollars to develop ($2.5 million for land: $15 million for construction, and $300,000 to pay yourself in development fees). You've plan to finance the project with a combination of debt and your own equity. You've arranged two mortgages, a $10 million 20-year amortizing mortgage offered at an annual interest rate of 5% and a 30-year amortizing mortgage at an annual interest rate of 7%. For both loans, payments are due annually starting in year 1. Given the current uncertainty in the market, you expect two different possible outcomes for your project once it's completed at the start of year 2): 65 65 Building Information (Scenario A) Size Rent/Unit $24,000 OpEx/Unit (fixed) $1,200 Capital Reserve/unit $200 Rent Growth 6% Year 2 Vacancy 50% Year 3-5 Vacancy 10% Terminal Cap Rate Year 5 7.0% Building Information (Scenario B) Size Rent/Unit $21,600 OpEx/Unit (fixed) $1,200 Capital Reserve unit $200 Rent Growth 5% Year 2 Vacancy 50% Year 3 Vacancy 40% Year 4-5 Vacancy 10% Terminal Cap Rate Year 5 8.0% Under Scenario A, you expect base rent of $24,000 unit/year, with fixed operating expenses of $1,200 unit/year. Under this scenario, you expect rental income to grow at 6% a year, with 50% vacancy in year 2, dropping to a stabilized vacancy rate of 10% for years 3-5. You plan to sell the building in year 5 at cap rate of 7%. Under the more pessimistic Scenario B, you expect base rent of $21,600 unit/year, with fixed operating expenses of $1,200 unit/year. Rent in this scenario will only grow at 5% per year. You expect 50% vacancy in year 2, 40% in year 3, and 10% in years 4-5. Finally, under this scenario, you expect a terminal cap rate of 8% Using the blank template I've provided determine the IRR for each project. You should submit both the completed excel pro formas and a brief written summary of your analysis (in word). Building Information (Scenario A) Land Capital Improvments Developers Fee Year 1 Year 2 Year 3 Year + Year 5 Rent Limit Open fired) Capital Reservent Rent Growth Year 2 Vacancy Year 1.5 Vacancy Terminal Cap Rate Years Scenario A Pro Forms Year Poteatail Grecs Income Vacancy Effective Gree: Income Total Uses of Punca Ops Net Operating Income First Mortgage Second Mortgage Capital Reserve Before Tax Cash Flow Sources First Mortege Interest Rate Payment Sole: Price Outstanding Principal 1st Mrte Outstandige Principal 2st Mrte Net Sales Revesses Second Mortgage Amount Term Interest Rate Payment Year Year 1 Year 2 Year 3 Year 4 Year 5 Investor Reture Erity Developer Fee BTCT Debt Remaining Sales Proceeds Net Retur TRR Building Information (Scenario B) Year 1 Year 2 Year 3 Year 4 Year 5 Rent Oplnit (fixed) Capital Reservent Rent Growth Year 2 Vacancy Year 1 Vacancy Year 4.5 Vacancy Terminal Cap Rate Years Scenario B Pro Forms Year Potentsil Gross Income VACACY Effective Gross Lacome OpEx Net Operating Income First Mortgage Second Mortgage Capital Reserve Before Tax Cables Sales Price OutstaadisPriecpollst Mrte Outstandis Priscipl 2st Mrta Natale Revenues Year Year 1 Year 1 Year 3 Year 9 Year 10 Investor Retar Equity Developer Fee BTCF Sales Proceeds Net Retor IRR Suppose you're considering developing new apartments near Iowa State. You've planned a 65 unit building, which will cost a total of $17.8 million dollars to develop ($2.5 million for land: $15 million for construction, and $300,000 to pay yourself in development fees). You've plan to finance the project with a combination of debt and your own equity. You've arranged two mortgages, a $10 million 20-year amortizing mortgage offered at an annual interest rate of 5% and a 30-year amortizing mortgage at an annual interest rate of 7%. For both loans, payments are due annually starting in year 1. Given the current uncertainty in the market, you expect two different possible outcomes for your project once it's completed at the start of year 2): 65 65 Building Information (Scenario A) Size Rent/Unit $24,000 OpEx/Unit (fixed) $1,200 Capital Reserve/unit $200 Rent Growth 6% Year 2 Vacancy 50% Year 3-5 Vacancy 10% Terminal Cap Rate Year 5 7.0% Building Information (Scenario B) Size Rent/Unit $21,600 OpEx/Unit (fixed) $1,200 Capital Reserve unit $200 Rent Growth 5% Year 2 Vacancy 50% Year 3 Vacancy 40% Year 4-5 Vacancy 10% Terminal Cap Rate Year 5 8.0% Under Scenario A, you expect base rent of $24,000 unit/year, with fixed operating expenses of $1,200 unit/year. Under this scenario, you expect rental income to grow at 6% a year, with 50% vacancy in year 2, dropping to a stabilized vacancy rate of 10% for years 3-5. You plan to sell the building in year 5 at cap rate of 7%. Under the more pessimistic Scenario B, you expect base rent of $21,600 unit/year, with fixed operating expenses of $1,200 unit/year. Rent in this scenario will only grow at 5% per year. You expect 50% vacancy in year 2, 40% in year 3, and 10% in years 4-5. Finally, under this scenario, you expect a terminal cap rate of 8% Using the blank template I've provided determine the IRR for each project. You should submit both the completed excel pro formas and a brief written summary of your analysis (in word)