Instruction for Assignment: Fill out worksheet on tab 2 from assumptions in tab 3 Complete the assignment in Excel to let the spreadsheet do the math Show me the loan amortization table like we did in class exercises Use excel to calculate ratios Pro Forma Income Statement Year 3 1 2 4 5 PGI (Potential Gross Income) -VC (Vacancy/Collection Loss) EGI (Effective Gross Income) OE (Ope. Expenses) Real Estate Tax Insurance Utilities Repair & Maint Grounds & Security -OE (Total Ope. Expenses) NOI (Net Operating Income) DS (Debt Service) Interest Payments Principal Payments -DS (Total Debt Service) CAPX (Capital Exp.) Tenant Improvements Leasing Commissions Roof Repair -CAPX (Total Cap Exp.) BTCF (Before Tax Cash Flow) -TAX ATCF (After Tax Cash Flow) Ratio Analysis Year 1 Capitalization Rate: NOI Acquisition Price Equity Dividend Rate: BTCF Equity Investment Net Income Multiplier: Acquisition Price NOI Gross Income Multiplier: Acquisition Price EGI Year 1 Year 2 Year 3 Year 4 Year 5 Operating Expense Ratio: OE EGI Loan-to-Value Ratio: Mortgage Balance Property Value Debt Coverage Ratio: NOI Debt Service NPV = IRR = 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 Utilities are expected to be 9% of EGI each year Repairs and Maintenance costs are expected to be 7% of EGI each year Grounds and Security costs are expected to by 6% of EGI each year The market value of the investment is expected to increase 6% each year Selling expenses will be 5% The holding period is 5 years 80% of the purchase price can be borrowed on a 30-year, monthly payment mortgage The annual interest rate on the loan will be 8%. Loan origination fees will be 1% of the loan amount (paid in the year the loan is taken out - Year 0) There are no prepayment penalties if you pay the loan early. Tenant Improvements are expected to be $3,000/year Leasing Commissions are expected to be $1,000/year A roof repair totaling $15,000 will be completed in year 3 The required rate of return for the investor is 12%. Assume taxes are 30% of BTCF. Assignment: Fill out the income statement. Calculate the monthly mortgage payment to find debt service. What is the IRR and NPV of the property? (CFO = equity investment + loan origination fees) Calculate the ratios for one or five years as indicated on the worksheet To find the mortgage balance, principal and interest payments: 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 Instruction for Assignment: Fill out worksheet on tab 2 from assumptions in tab 3 Complete the assignment in Excel to let the spreadsheet do the math Show me the loan amortization table like we did in class exercises Use excel to calculate ratios Pro Forma Income Statement Year 3 1 2 4 5 PGI (Potential Gross Income) -VC (Vacancy/Collection Loss) EGI (Effective Gross Income) OE (Ope. Expenses) Real Estate Tax Insurance Utilities Repair & Maint Grounds & Security -OE (Total Ope. Expenses) NOI (Net Operating Income) DS (Debt Service) Interest Payments Principal Payments -DS (Total Debt Service) CAPX (Capital Exp.) Tenant Improvements Leasing Commissions Roof Repair -CAPX (Total Cap Exp.) BTCF (Before Tax Cash Flow) -TAX ATCF (After Tax Cash Flow) Ratio Analysis Year 1 Capitalization Rate: NOI Acquisition Price Equity Dividend Rate: BTCF Equity Investment Net Income Multiplier: Acquisition Price NOI Gross Income Multiplier: Acquisition Price EGI Year 1 Year 2 Year 3 Year 4 Year 5 Operating Expense Ratio: OE EGI Loan-to-Value Ratio: Mortgage Balance Property Value Debt Coverage Ratio: NOI Debt Service NPV = IRR = 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 Utilities are expected to be 9% of EGI each year Repairs and Maintenance costs are expected to be 7% of EGI each year Grounds and Security costs are expected to by 6% of EGI each year The market value of the investment is expected to increase 6% each year Selling expenses will be 5% The holding period is 5 years 80% of the purchase price can be borrowed on a 30-year, monthly payment mortgage The annual interest rate on the loan will be 8%. Loan origination fees will be 1% of the loan amount (paid in the year the loan is taken out - Year 0) There are no prepayment penalties if you pay the loan early. Tenant Improvements are expected to be $3,000/year Leasing Commissions are expected to be $1,000/year A roof repair totaling $15,000 will be completed in year 3 The required rate of return for the investor is 12%. Assume taxes are 30% of BTCF. Assignment: Fill out the income statement. Calculate the monthly mortgage payment to find debt service. What is the IRR and NPV of the property? (CFO = equity investment + loan origination fees) Calculate the ratios for one or five years as indicated on the worksheet To find the mortgage balance, principal and interest payments: 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