Tranquil Manor is a 33,000 square foot apartment complex. The location is fully built-up and well established with multi- family housing. The prevailing capitalization rate in this area for properties such as this is 11%. You are contemplating the purchase of this property. The building is brick, about 50 years old and has been well- maintained. There is no evidence of deferred maintenance or of the need to replace the roof or mechanicals at any time in the near future. 75% of the value of the property lies in the building and 25% in the land. The building has 48 apartments. Every apartment is occupied and all leases expire within a year or less. The owner has presented us with the following rent roll information: 10 studio apartments @ $700 each 30 1-bedroom apartments @ $950 each 8 2-bedroom apartments @ $1,150 each Your research shows that these rents are realistic in this market and also that rents have been increasing at about 2% per year. Although there are no vacancies now, you will estimate a 2% loss of revenue as a credit allowance (i.e., uncollectable rent). You estimate of first-year operating expenses are as follows: Accounting 2,500 Insurance (fire and liab.) 29,300 Lawn/Snow 7,400 Legal 6,200 Miscellaneous 3,200 Property Management 38,400 Repairs and Maintenance 29,300 Supplies 7,400 Real Estate Taxes 42,600 Trash Removal 18,600 Electricity 12,200 Sewer and Water 29,500 Telephone 800 You believe that each of these expenses will increase at 3% per year except insurance (5%) and real estate taxes (4%). The sellers asking price is $3 million. You believe you can obtain financing for 70% of the purchase price. The terms are 7% annual rate, fully amortizing, 20-year term, monthly pay. You expect to sell the property at the end of year 5 based on the NOI of year 6 capped at 11%. 1. Prepare a six year cash flow.

Please use excel and show formulas! Thank you!