Rent versus Own Analysis

This project requires you to compare the costs of owning a home against renting a home. Assume a home can be purchased for $150,000 with a $30,000 down payment and financed with a fully amortizing mortgage loan of $120,000 at 4.5 percent interest for 30 years. Other costs associated with owning include annual maintenance (initial cost of $600), insurance (initial cost of $600), and property taxes (initial cost of 1.5 percent of property value). These expenses would not need to be paid if renting. Assume that growth rates for expenses (including insurance, maintenance, and property taxes) equal to 2 percent per year. Assume the property value will grow at a rate of a constant 2 percent per year. After six years, the property will be sold. A selling expense of 7 percent would have to be paid at that time. Assume the income tax rate is 24 percent. Alternatively, assume the home can be rented for $12,000 for the first year, with an annual 2 percent growth rate in rent.

Be sure to show your work in Excel. In other words, do not simply type values into the boxes, but reference prior cells when calculating results. Please refer for Exhibits 7-2 and 7-3 for sample calculations. In your report, identify and explain the net cash flow in each year, for owning relative to renting. If an annual after-tax return of 15 percent is available on an investment of comparable risk, which is the better option, owning or renting?

Part 1: Property and Loan Information

1. Fill in the table for the property information. (9 points)

2. Fill in the table for the loan information. Apply the Excel PMT function to calculate the monthly payment. (9 points)

Formulae: Interest = Beginning loan balance * Periodic rate

Amortization = Monthly payment Interest

Ending loan balance = Beginning loan balance Amortization

Part 2: Loan Schedule

3. Fill in the table for the loan schedule over six years. Make sure you reference cells (e.g., monthly payment and periodic rate). (9 points)

4. Fill in the table for the yearly summary of the loan schedule. These values should be referenced from Section C. For example, Interest for year 1 would be the sum of Interest in months 1 through 12. (9 points)

5. Fill in the table for the yearly property data. Apply the FV function or FV formula using correct growth rates. (9 points)

Part 3: Cash Flow Analysis

6. Fill in the table for the before tax cash flows from owning. Apply the FV function or FV formula to calculate the property taxes, insurance, and maintenance after year 1. Reference the Interest and Principal from Section C. (9 points)

Formulae: Property taxes = Property value * Property tax rate

Cash outflows before taxes = Property taxes + Insurance + Maintenance + Principal + Interest

7. Fill in the table for tax deductions. Reference the property taxes from Section F and Interest from Section D. (9 points)

Formulae: Total tax deductions = Property taxes + Interest

Tax savings = Total tax deductions * Income tax rate

8. Fill in the table for net cash flows from owning. Reference the Cash outflows before taxes from Section F and the Tax savings from Section G. Reference the cost of renting from Section E. (9 points)

Formulae: After tax cost of owning = Cash Outflows before taxes - Tax savings

After tax cash flows Own vs. Rent = Cost of renting - After tax cost of owning

1. Fill in the table for the cash flows from selling the home in year 6. Reference the Property value from Section E and the Loan balance from Section D. (9 points)

Formulae: Selling costs = Property value * Selling expense rate

Benefit from sale = Property value Selling costs Loan balance

10. Fill in the table for the net cash flows. Items to include in the net cash flow consist of the down payment (cash outflow), After tax cash flows Own vs. Rent foe each year, and the Benefit from Sale. Calculate the internal rate of return (IRR) using the Excel function. (9 points)

116531 FIND Al Propedyfatormation Loan Information Purchase price Initial Yearly Rent 1711 Yearly Property Data Rental grawdrat Property growth rate Maintenance Insurance Expenses prowthrate 120 Property value income tax rate Property tax rate Selling expense rate Down payment Loan amount Loan to value ratio Interest rate Loan term (in years) Payments (per year) Number of periods Part 21 toan Schedule and Property Data Periodie (monthly rate Monthly loan Payment SO 109 of Yearly Summary of Loan Schedule 110 End of year Payment Balance interest Principal 121 Part 3) Cash Flow Analysis #241 23 Before Tax Cash Flows from Owning Year Property taxes Insurance Maintenance Prinopal Interest Cash outflows before taxes G) Tax Deductions from Owning 22 A MOTIO 140 CAMINA and party Pampany.com perty Safe G)Tax Deductions from Owning Year Property taxes Interest Total tax deductions Taxsavings 14) Net Cash flows from Owning 16) Not Gash lows and R Year Cash outflows beforetans Tax swines Aftertax cost of owning Cost of seeing Aftertax cash flows Own vs. Rent 151) Cash Flow from Selling in Year 6 Year Property valor Selling costs Loan balance Benefit from s de MOB 116531 FIND Al Propedyfatormation Loan Information Purchase price Initial Yearly Rent 1711 Yearly Property Data Rental grawdrat Property growth rate Maintenance Insurance Expenses prowthrate 120 Property value income tax rate Property tax rate Selling expense rate Down payment Loan amount Loan to value ratio Interest rate Loan term (in years) Payments (per year) Number of periods Part 21 toan Schedule and Property Data Periodie (monthly rate Monthly loan Payment SO 109 of Yearly Summary of Loan Schedule 110 End of year Payment Balance interest Principal 121 Part 3) Cash Flow Analysis #241 23 Before Tax Cash Flows from Owning Year Property taxes Insurance Maintenance Prinopal Interest Cash outflows before taxes G) Tax Deductions from Owning 22 A MOTIO 140 CAMINA and party Pampany.com perty Safe G)Tax Deductions from Owning Year Property taxes Interest Total tax deductions Taxsavings 14) Net Cash flows from Owning 16) Not Gash lows and R Year Cash outflows beforetans Tax swines Aftertax cost of owning Cost of seeing Aftertax cash flows Own vs. Rent 151) Cash Flow from Selling in Year 6 Year Property valor Selling costs Loan balance Benefit from s de MOB