After working with a mortgage broker, you believe you can get a mortgage for $350,000, the full value of the house, at 3.5%, for thirty years. You believe the home can be rented for $3,050 per month and that rents will increase by 5% every year. Vacancy costs will offset rental revenue by 5% per year. Management costs include maintenance you will be required to do on the property and are expected to be approximately 4.5% of the net rental revenue. Your marginal tax rate is 25%. You expect the home to be depreciated on a straight-line basis over the thirty years. You believe you will keep the property for thirty years and then sell it. The terminal value of your property can be calculated using time value of money principles, assuming its value will increase at an average of 6% per year from today (the discount rate), compounded annually. A spreadsheet has been created for your analysis. Use this sheet to do all your analysis, submit this sheet as part of the quiz with the filed saved as Week 9: Day 5: LASTNAME.xls. Here are some helpful hints. 1. Every cell that is highlighted in green means the cell holds a critical number from the problem narrative. For example, cell D6 holds the vacancy rate, that number can be found in this narrative. Numbers in cells with a bold border around them are going to be referenced in the Canvas quiz. 2. The first part of the problem is designed to guide you through the process of getting to after tax operating cash flows, line 23. 3. The second part of the problem involves calculating a terminal value, using time value of money, make sure that calculation and number is in cell AH25. 4. The third part of the problem will be adding the after-tax operating cash flows with the terminal value so they are all in one row, making the NPV and IRR calculations easier. 5. The fourth part of the problem will be running the NPV, IRR, and payback period, before mortgage payments are included. You should be able to interpret the NPV. IRR. and payback period to see if you would be willing to do this deal on its own merits. 6. Now consider the fact that you do not have the money to purchase the property, but instead will need to finance it. Calculate monthly mortgage payment and annualize it. Recalculate the after-tax operating cash flows by subtracting the annual mortgage amount. 7. Re-run your IRR, NPV, and payback period. Imprortant Data pour le Mortgage interest Rate Tex Role Vacancy Rate Maintenance Expenses Slum Lords Associated 1 2 4 5 6 7 12 13 14 15 17 18 19 20 21 22 25 25 27 29 30 Intial Investment Rental Revenue Net Rental Revenue - Management Costs -Depreciation PeTax Rental Income o o dolyage Sabbadini molto fed Deprecision After Tex Obersting Cash Flow Terminal Value Value in Cel AG25 Total Discounting Cash Flows NPV IRR Mantly Mortgage Annualized Total Discounting Cash Flows NPV IRR Monthly Mortgage Payment M