This is an assignment for a Real Estate class. Please let me know if you can help out. I have attached 2 files. the PDF file is the assignment instructions. Pages 1, 2 and 3 are examples. The actual assignment is on page 4.

The excel file is a template to be used for the assignment.

Thank you in advance.

Project 3 - Investment Decisions RE 3010, Excel Project Assigned: Due date: Friday, Nov 4, 2016 Friday, Nov 11, 2016 by 9:00 AM Submit on iCollege, upload completed Excel file under Assessments, Dropbox, Project 3. Late projects penalized 20 points per day, starting at 9:01 AM on November 11. Objective - Create a pro forma & conduct sensitivity analysis for financial leverage Each student should submit their response based on the Actual project assumptions. A complete project will include the following, based on Actual project assumptions: Five-Year Operating Pro Forma (similar to Exhibits 19-2, 19-3, 19-4, and 19-5) Levered Cash Flows (similar to Exhibit 19-6) Net Present Value of Levered Cash Flows (similar to Exhibit 19-7) The Effects of Leverage on NPVs and IRRs (similar to Exhibit 19-8) The Effects of Leverage on Cash Flows, and IRRs, and Risk (similar to Exhibit 19-9) An Example project with similar mechanics and its solution is provided below for guidance. Each student is required to create their own Excel file. Example: Consider the following loan information. 1 Solution to Example: Five-Year Operating Pro Forma (similar to Exhibits 19-2, 19-3, 19-4, and 19-5) Year Potential gross income (PGI) - Vacancy & collection losses (VC) = Effective gross income (EGI) - Operating expenses (OE) - Capital expenditures (CAPX) = Net operating income (NOI) - Debt service (DS) = Before-tax cash flow (BTCF) Assumption 3% inflation 10% of PGI 40% of EGI 5% of EGI 1 $180,000 2 $185,400 3 $190,962 4 $196,691 5 $202,592 6 $208,669 18,000 162,000 64,800 8,100 $89,100 60,072 $29,028 18,540 166,860 66,744 8,343 $91,773 60,072 $31,701 19,096 171,866 68,746 8,593 $94,526 60,072 $34,454 19,669 177,022 70,809 8,851 $97,362 60,072 $37,290 20,259 182,332 72,933 9,117 $100,283 60,072 $40,211 20,867 187,802 75,121 9,390 $103,291 Sale price (SP) - Selling expenses (SE) = Net sale proceeds (NSP) - Remaining mortgage balance (RMB) = Before-tax equity reversion (BTER) $1,180,472 47,219 1,133,253 741,399 391,854 Levered Cash Flows (similar to Exhibit 19-6) Year 0 1 2 3 4 5 Annual BTCF ($287,760) 29,028 31,701 34,454 37,290 $40,211 BTER $391,854 Total CF ($287,760) 29,028 31,701 34,454 37,290 $432,065 Present value = PV @ 14% ($287,760) 25,463 24,393 23,256 22,079 $224,401 $319,592 2 Net Present Value of Levered Cash Flows (similar to Exhibit 19-7) Required Internal Rate of Return (ye) 10.00% 12.00% 14.00% 16.00% 16.87% 18.00% 20.00% Net Present Value $84,463 56,818 31,832 9,204 0 (11,329) (29,995) The Effects of Leverage on NPVs and IRRs (similar to Exhibit 19-8) Mortgage as a % of Acquisition Price 0% 60% 70% 75% 80% 85% 90% NPV: ye = 14% ($145,117) (3,558) 20,036 31,832 43,629 55,426 $67,222 IRR 10.1% 13.8% 15.6% 16.9% 18.7% 21.3% 25.7% The Effects of Leverage on Cash Flows, and IRRs, and Risk (similar to Exhibit 19-9) Initial loan amount $0 Initial loan-to-value ratio 0% NOI in year 1 $89,100 - Annual debt service 0 = BTCF $89,100 Initial equity $1,056,000 BTCF/initial equity 8.4% Growth rate in PGI: IRR -1% ( 5% probability) 6.2% 1% ( 20% probability) 8.1% 3% ( 50% probability) 10.1% 5% ( 20% probability) 12.1% 7% ( 5% probability) 14.0% Mean IRR 10.1% Standard deviation of IRR 1.8% Mean Return/Std. Dev. 5.7 $633,600 60% $89,100 48,057 $41,043 $441,408 9.3% IRR 4.6% 9.4% 13.8% 17.8% 21.6% 13.7% 3.8% 3.6 3 $792,000 75% $89,100 60,072 $29,028 $287,760 10.1% IRR 3.0% 10.6% 16.9% 22.4% 27.4% 16.5% 5.4% 3.1 $950,400 90% $89,100 72,086 $17,014 $134,112 12.7% IRR -3.8% 14.3% 25.7% 34.5% 42.0% 24.5% 9.9% 2.5 Actual Project Assumptions: Consider the following loan information. Total acquisition price: $3,000,000. Property consists of twelve office suites, five on the first floor and seven on the second. Contract rents: three suites at $2,400 per month, two at $3,000 per month, and seven at $1,330 per month. Annual market rent increases: 3% per year. Vacancy and collection losses: 5% per year. Operating expenses: 35% of effective gross income each year. Capital expenditures: 6% of effective gross income each year. Expected holding period: 5 years. Expected selling price in year 5: Year 6 NOI capitalized at 5%. Selling expenses in year 5: 3% of the sale price. First mortgage loan: 75% LTV. Annual mortgage interest rate: 4.5%. Loan term and amortization period: 25 years (monthly payments). Total up-front financing costs: 2% of loan amount. Required return: 9% 1. Create a five-year operating pro forma which calculates BTCF and BTER. 2. Calculate the NPV and IRR of the project based on the levered cash flows. 3. Calculate the project NPV of levered cash flows based on the following required return assumptions: 10%, 12%, 14%, 16%, 18%, and 20%. 4. Calculate the project NPV at 9% and IRRs for levered cash flows based on the following LTV assumptions: 0%, 60%, 70%, 75%, 80%, 85%, and 90%. 5. Calculate the project IRRs for levered cash flows based on the following PGI growth rate assumptions: -1%, +1%, +3%, +5%, and +7%. Complete #5 above for initial LTV ratios of 0%, 60%, 75%, and 90%. 4 Year Potential gross income (PGI) - Vacancy & collection losses (VC) = Effective gross income (EGI) - Operating expenses (OE) - Capital expenditures (CAPX) = Net operating income (NOI) - Debt service (DS) = Before-tax cash flow (BTCF) Assumption 3% inflation 10% of PGI 1 $180,000 2 3 40% of EGI 5% of EGI Sale price (SP) - Selling expenses (SE) = Net sale proceeds (NSP) - Remaining mortgage balance (RMB) = Before-tax equity reversion (BTER) Assumptions: 8.75% exit cap rate 4% selling costs 14% discount rate 75% LTV $1,056,000 purchase price equity investment 3% 6.5% 30 4 5 6 Year Annual BTCF 0 $0 1 0 2 0 3 0 4 0 5 $0 BTER Total CF $0 0 0 0 0 $0 $0 Present value = IRR = up-front financing costs loan amount interest rate (cost of debt, annual) mortgage term (years) mortgage payment (monthly) PV @ 14% Required Internal Rate of Return (ye) 10.00% 12.00% 14.00% 16.00% 18.00% 20.00% Net Present Value Mortgage as a % of Acquisition Price 0% 60% 70% 75% 80% 85% 90% NPV: ye = 14% IRR Initial loan amount Initial loan-to-value ratio NOI in year 1 - Annual debt service = BTCF Initial equity BTCF/initial equity Growth rate in PGI: -1% ( 5% probability) 1% ( 20% probability) 3% ( 50% probability) 5% ( 20% probability) 7% ( 5% probability) Mean IRR Standard deviation of IRR Mean Return/Std. Dev. $0 75% $0 0 $0 $0 #DIV/0! IRR 0.0% 0.0% #DIV/0! 0.0% 0.000% 0.000% 0.000% 0.000% 0.000%