Consider the buy vs. rent Excel spreadsheet provided to you in tab A2.
a. According to the assumptions made in that spreadsheet, should the average individual buy or rent? Briefly explain.
b. Using Goal Seek alter the buy vs. rent Excel spreadsheet so that it shows the minimum rate of price appreciation the homeowner must receive in order to be better off buying than renting. Highlight in yellow the cell that includes this price appreciation rate.
Inflation expectation Purchase price Initial monthly rent Rental growth rate Property appreciation Insurance Maintenance Expense growth Marginal tax rate Property tax 2.0096 $230,000 $1,800 2.0096 2.0096 $3,450 $2,300 2.0096 25.0096 $4,025 Down payment Loan amount Interest rate (Fixed) Mortgage initiation fee Loan maturity (years) Mortgage Payment Holding period (years) Selling expenses Invetments opportunity 30% $161,000 4.00% $3,000 30 $768.64 5 6.5096 8.50% Option 1: Buy the house 0 1 2 3 5 Year Out flows Down payment + Origination Insurance & Maintenance Property tax Mortgage payments $72,000 $5,750 $4,025 $9,224 $5,865 $4,106 $9,224 $5,982 $4,188 $9,224 $6,102 $4,271 $9,224 $6,224 $4,357 $9,224 Total: $72,000 $18,999 $19,194 $19,394 $19,597 $19,804 In flow Tax benefits $0 $2,603 $2,595 $2,585 $2,575 $2,564 Overall "out of pocket" $72,000 $16,395 $16,600 $16,809 $17,022 $17,241 Other Interest payment Principal payments House value Total equity $6,388 $2,835 $230,000 $234,600 $69,000 $76,435 $6,273 $6,153 $6,028 $5,897 $2,951 $3,071 $3,196 $3,326 $239,292 $244,078 $248,959 $253,939 $84,078 $91,935 $100,013 $108,318 Cash at time sold $91,812 Option 2: Rent the house Rent expenses $21,600 $22,032 $22,473 $22,922 $23,381 Investments portfolio $72,000 $72,915 $73,681 $74,279 $74,693 $74,903 Inflation expectation Purchase price Initial monthly rent Rental growth rate Property appreciation Insurance Maintenance Expense growth Marginal tax rate Property tax 2.0096 $230,000 $1,800 2.0096 2.0096 $3,450 $2,300 2.0096 25.0096 $4,025 Down payment Loan amount Interest rate (Fixed) Mortgage initiation fee Loan maturity (years) Mortgage Payment Holding period (years) Selling expenses Invetments opportunity 30% $161,000 4.00% $3,000 30 $768.64 5 6.5096 8.50% Option 1: Buy the house 0 1 2 3 5 Year Out flows Down payment + Origination Insurance & Maintenance Property tax Mortgage payments $72,000 $5,750 $4,025 $9,224 $5,865 $4,106 $9,224 $5,982 $4,188 $9,224 $6,102 $4,271 $9,224 $6,224 $4,357 $9,224 Total: $72,000 $18,999 $19,194 $19,394 $19,597 $19,804 In flow Tax benefits $0 $2,603 $2,595 $2,585 $2,575 $2,564 Overall "out of pocket" $72,000 $16,395 $16,600 $16,809 $17,022 $17,241 Other Interest payment Principal payments House value Total equity $6,388 $2,835 $230,000 $234,600 $69,000 $76,435 $6,273 $6,153 $6,028 $5,897 $2,951 $3,071 $3,196 $3,326 $239,292 $244,078 $248,959 $253,939 $84,078 $91,935 $100,013 $108,318 Cash at time sold $91,812 Option 2: Rent the house Rent expenses $21,600 $22,032 $22,473 $22,922 $23,381 Investments portfolio $72,000 $72,915 $73,681 $74,279 $74,693 $74,903
