You are considering the purchase of an apartment complex. The following assumptions are made: The purchase price is $2,000,000 There are 30 units and the market rent is $850/month Market rents are expected to increase 4% per year Vacancy and collection loss is 10% Real Estate Taxes are expected to be $20,000 in year 1 and increase 5% per year Insurance is expected to be $10,000 in year 1 and increase 7% per year 1 Utilities are expected to be 9% of EGI each year 2 Repairs and Maintenance costs are expected to be 7% of EGI each year 3 Grounds and Security costs are expected to by 6% of EGI each year 4 The market value of the investment is expected to increase 6% each year 5 Selling expenses will be 5% 6 The holding period is 5 years 7 80% of the purchase price can be borrowed on a 30-year, monthly payment mortgage 8 The annual interest rate on the loan will be 8%. 9 Loan origination fees will be 1% of the loan amount (paid in the year the loan is taken out - Year 0) 20 There are no prepayment penalties if you pay the loan early. 21 Tenant Improvements are expected to be $3,000/year 22 Leasing Commissions are expected to be $1,000/year 23 A roof repair totaling $15,000 will be completed in year 3 24 The required rate of retum for the investor is 12%. 25 Assume taxes are 30% of BTCF. 26 27 Assignment: 28 Fill out the income statement. 29 Calculate the monthly mortgage payment to find debt service. 30 What is the IRR and NPV of the property? (CFO = equity investment + loan origination fees) 31 Calculate the ratios for one or five years as indicated on the worksheet 32 To find the mortgage balance, principal and interest payments: 33 34 33 35 36 37 38 39 40 41 42 43 44 224 06 Enter key strokes to find payment on the loan Enter 2nd Amort P1 1 enter, down arrow P2 = 12 enter, down arrow Balance of loan, principal and interest is displayed For year 2, P1-13 enter, down arrow P2 24 enter, down arrow Balance of loan, principal and interest is displayed 4 2 Pro Forma Income Statement 5 PGI (Potential Gross Income) 6 VC (Vacancy/Collection Loss) 7 EGI (Effective Gross Income) OE (Ope. Expenses) B 9 Real Estate Tax Insurance 0 1 Utilities 12 Repair & Maint 13 Grounds & Security 4-OE (Total Ope. Expenses) 5 NOI (Net Operating Income) DS (Debt Service) Interest Payments Principal Payments 16 17 18 19 20 21 22 23 DS (Total Debt Service) CAPX (Capital Exp.) Tenant Improvements Leasing Commissions Roof Repair 24 CAPX (Total Cap Exp.) 25 BTCF (Before Tax Cash Flow) 26 -TAX 27 ATCF (After Tax Cash Flow) 28 Year 2 3 4 5