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 todo on the property and are expected to be approximately4.5%of the net rental revenue.

Your marginal tax rate is 25%.You expect the home to be depreciated, on a straight-line basis overt he 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

Here are some helpful hints.

Every cell that is highlighted in green means the cell holds a critical number from the problem narrative.For example, cell D6holds 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 theCanvas quiz.

The first part of the problem is designed to guide you through the process of getting to after tax operating cash flows, line 23

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.

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.

The fourth part of the problem will be running theNPV, 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.

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.

Re-run your IRR, NPV, and payback period.

Imprortant Data Discunt Rase Mortgage Interest Rate Tax Rule Vacancy Rate Maintenance Experses Slum Lords Asssociated 1 2 5 11 15 16 17 18 12 21 21 22 24 25 o Inital investment Rental Revenue Vacancy Net Rental Revenue - Management Costs Depreciation Pre-Tax Rental income Taxes +Depreciation After Tax Operating Cash Flow Terminal Value Value in Cell AG25) % Total Discounting Cash Flows NPV IRR Monty Mortgage Annualized Total Discounting Cash Flows NPV IRR Monthly Mortgage Payment O D 0 0 Imprortant Data Discunt Rase Mortgage Interest Rate Tax Rule Vacancy Rate Maintenance Experses Slum Lords Asssociated 1 2 5 11 15 16 17 18 12 21 21 22 24 25 o Inital investment Rental Revenue Vacancy Net Rental Revenue - Management Costs Depreciation Pre-Tax Rental income Taxes +Depreciation After Tax Operating Cash Flow Terminal Value Value in Cell AG25) % Total Discounting Cash Flows NPV IRR Monty Mortgage Annualized Total Discounting Cash Flows NPV IRR Monthly Mortgage Payment O D 0 0