The first picture is answer key from the previous assignment. Please

A B C D E F G H J 1 Assumptions: 2 Purchase Price 10,120,000 3 Potential Gross Income (t=1) 4,250,000 4 Vacancy & Collection Losses (% of PGD) 4% 5 Operating Expense Ratio 40% 6 Capital Expenditures (% of EGI) 6.50% 7 Leasing Commissions (% of Vacancy Loss) 25% 8 Property Valuse Growth Rate 5% 9 PGI Growth Rate 5% 10 Selling Expenses (% of Resale Price) 2.75% 11 Discount Rate 7% 12 Resale Price (t=5) 12,915,969 13 14 Q1 15 Going-In Cap Rate 24% 16 EGI Multiple 2.48 17 Operating Expense Ratio 40% 18 Going-Out Cap Rate 24% 19 Net Income Multiple 4.1 20 21 02 22 It sounded like a pretty decent deal. Since the rates are high, you can have more use. 23 24 03 and 04 0 2 3 5 6 25 Potential Gross Income 4.250.000.00 4,462,500.00 4,685,625.00 4,919,906.25 5,165,901.56 5,424,196.64 26 Vacany & Collection Losses 170,000.00 178,500.00 187,425.00 196,796.25 206,636.06 216,967.87 27 Other Income 0.00 0.00 0.00 0.00 0.00 0.00 28 Rental Concessions 0.00 0.00 0.00 0.00 0.00 0.00 29 Effective Gross Income (EGI) 4,080,000.00 4.284,000.00 4,498,200.00 4,723,110.00 4.959.265.50 5,207,228.78 30 Operating Expenses 1,632,000.00 1,713,600.00 1,799,280.00 1,889.244.00 1,983, 706.20 2,082,891.51 31 Expense Recoveries 0.00 0.00 0.00 0.00 0.00 0.00 32 Net Operating Income 2,448,000.00 2,570,400.00 2,698,920.00 2,833,866.00 2,975,559.30 3,124,337.27 33 Capital Expenditures 265.200.00 278.460.00 292.383.00 307,002.15 322.352.26 338,469.87 34 Leasing Commissions 42,500.00 44,625.00 46.856.25 49,199.06 51,659.02 54,241.97 35 Operating Cash Flow 2,140,300.00 2,247,315.00 2,359,680.75 2,477,664.79 2,601,548.03 2,731,625.43 36 Reversion Cash Flow 0.00 0.00 0.00 0.00 12,560,779.85 0.00 37 Property-Before-Tax-Cash Flow 10,120,000.00) 2,140,300.00 2.247,315.00 2,359,680.75 2,477,664.79 15,162,327.88 2,731,625.43 38 Present Value of PBTCF (10,120,000.00) 2,000,280.37 1,962,891.96 1,926,202.39 1,890,198.60 10,810,530.23 39 Property Value 18,590,103.55 40In this assignment, you will use the W that you used for Homework Assignment #5. You can add your answers directly to the answer key from the previous assignment. Remember: Your submission for this assignment should be calculated in Microsoft Excel. Please show all your formulas in the spreadsheet. 1 can only give you partial credit if 1 see how you did each calculation. To pay for the building, you get an interest-only 15-year loan worth $15 million with an interest rate of 6.5%. 1. What is the L'T'l.llr of the loan? 2. How much cash will you need for your equity investment? 3. What is the debt service for each year? 4-. What is the EBTCF for each year? 5. What is the debt yield ratio in each year? 6. What is the cash-on-cash return in each year? Given this new information, you'd like to change the discount rate. [In other words, get rid ofthe discount rate you used previously.) The debt investors expect to earn the interest rate on their loan. You expect to earn 300 basis points more than that 7. Using the WACC formula, what is the project's required return? 8. What is the project's NP'J? 9. What is the project's IRR? 10. What is your NP'vI from an equity perspective? 11. What is your lRR from an equity perspective