solve using the excel

Problem 02 (60 Points) You are considering the purchase of an apartment complex in Fairfax County. Your plan is to hold the apartment complex for 5 years and sale it at the end of the fifth year Income and vacancy rates The apartment complex has the following unite mix: .40 units 2 bedroom /2 bath. First year monthly rent is expected to be $2,100 60 units 1 bedroom 1bath. First year monthly rent is expected to be $1,200 .40 studio. First year monthly rent is expected to be $650 first year and 2% increase o over the next Your broker is forecasting a 2% rent growth over the next 5 years. He also believes that vacancy rate will be 9% at first year, but it will steadily decline to 5% over the next 5 years. Operating expenses: Historical expenses of the property and projected increases are as follows: Amount $146,5000 $15,000 12% of PGI $90,000 $20,000 S12,000 $65,000 $25,000 $250 Annual Increase Expenses 1% 1% Real Estate Taxes Insurance Management Fee Repairs and Maintenance Office Expense Advertisin Utilities Miscellaneous Reserves 2% 1% 1% 1% Per unit Financing: The property is offered for sale at $16,500,000 The lender has agreed to give you a loan of $12,250,000 at 6% interest rate amortized over 20 years. Closing costs $50,000 Property sale: You are planning to sell the property at the end of the fifth year. The value of the property is expected to grow 3% over the 5 years, 6% cost of sale is forecasted into the net sales proceeds, Discount rate: Your unlevered required rate of retune in 12% Your levered required rate of retune in 15% Based on the information's and assumption above calculate the following 1. 2. 3. Calculate net operating income (NOI) for each of the five years. Calculate the net sale proceeds from the sale of the property Calculate the net present value of this investment, assuming no mortgage debt. Should you purchase? Why? 4. Calculate the internal rate of return of this investment, assuming no debt. Shoulod you purchase? Why? Calculate the monthly mortgage payment. What is the total per year? Calculate the loan balance at the end of year 5 (Note: the unpaid mortgage balance at any time is equal to the present value of the remaining payments, discounted at the contract rate of interest.) Calculate the amount of principal reduction achieved during each of the five years Calculate the total interest paid during each of the four years. (Note: Remember that debt service equals principal plus interest.) 5. 6. 7. 8. 9. Calculate the levered required initial equity investment. 10. Calculate the before-tax cash flow (BTCF) for each of the five years. 11. Calculate the before-tax equity reversion (BTER) from the sale of the property 12. Calculate the levered net present value of this investment. Should you purchase? Why? purchase? Why? equity dividend rate, (3) gross income multiplier, (4) debt coverage ratio. 13. Calculate the levered internal rate of return of this investment should you 14. Calculate, for the first year of operations, the: (1) overall (cap) rate of return, (2) Problem 02 (60 Points) You are considering the purchase of an apartment complex in Fairfax County. Your plan is to hold the apartment complex for 5 years and sale it at the end of the fifth year Income and vacancy rates The apartment complex has the following unite mix: .40 units 2 bedroom /2 bath. First year monthly rent is expected to be $2,100 60 units 1 bedroom 1bath. First year monthly rent is expected to be $1,200 .40 studio. First year monthly rent is expected to be $650 first year and 2% increase o over the next Your broker is forecasting a 2% rent growth over the next 5 years. He also believes that vacancy rate will be 9% at first year, but it will steadily decline to 5% over the next 5 years. Operating expenses: Historical expenses of the property and projected increases are as follows: Amount $146,5000 $15,000 12% of PGI $90,000 $20,000 S12,000 $65,000 $25,000 $250 Annual Increase Expenses 1% 1% Real Estate Taxes Insurance Management Fee Repairs and Maintenance Office Expense Advertisin Utilities Miscellaneous Reserves 2% 1% 1% 1% Per unit Financing: The property is offered for sale at $16,500,000 The lender has agreed to give you a loan of $12,250,000 at 6% interest rate amortized over 20 years. Closing costs $50,000 Property sale: You are planning to sell the property at the end of the fifth year. The value of the property is expected to grow 3% over the 5 years, 6% cost of sale is forecasted into the net sales proceeds, Discount rate: Your unlevered required rate of retune in 12% Your levered required rate of retune in 15% Based on the information's and assumption above calculate the following 1. 2. 3. Calculate net operating income (NOI) for each of the five years. Calculate the net sale proceeds from the sale of the property Calculate the net present value of this investment, assuming no mortgage debt. Should you purchase? Why? 4. Calculate the internal rate of return of this investment, assuming no debt. Shoulod you purchase? Why? Calculate the monthly mortgage payment. What is the total per year? Calculate the loan balance at the end of year 5 (Note: the unpaid mortgage balance at any time is equal to the present value of the remaining payments, discounted at the contract rate of interest.) Calculate the amount of principal reduction achieved during each of the five years Calculate the total interest paid during each of the four years. (Note: Remember that debt service equals principal plus interest.) 5. 6. 7. 8. 9. Calculate the levered required initial equity investment. 10. Calculate the before-tax cash flow (BTCF) for each of the five years. 11. Calculate the before-tax equity reversion (BTER) from the sale of the property 12. Calculate the levered net present value of this investment. Should you purchase? Why? purchase? Why? equity dividend rate, (3) gross income multiplier, (4) debt coverage ratio. 13. Calculate the levered internal rate of return of this investment should you 14. Calculate, for the first year of operations, the: (1) overall (cap) rate of return, (2)