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.
Edit Paste 10 11 12 13 14 15 16 17 18 20 21 22 23 25 26 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Font Alignment Number Genera Wrap Text Merge nput Variables Inflation expectation 30% 2.00% Down payment $300,000 $210,000 Purchase price Loan amount $2,200 Initial monthly rent Interest rate (Fixed) 00% Rental growth rate $3,000 2.00% Mortgage initiation fee Property appreciation 2.00% Loan maturity (years) 30 $4,500 $1.002.57 Mortgage Payment nsurance $3,000 Holding period (years) Maintenance Expense growth 2.00% Selling expenses 6.50% Marginal tax rate 25.00% 8.50% Invetments opportunity $5,250 Property tax Option 1: Buy the house Year Out flows $93,000 Down payment Origination $7,803 $7,500 $7,650 $7,959 $8,118 nsurance & Maintenance $5,571 $5,683 $5,250 $5,355 $5,462 Property tax $12,031 $12,031 $12,031 $12,031 $12,031 Mortgage payments $93,000 $24,781 $25,036 $25,296 $25,561 $25,832 Total n flow $0 $3,396 $3,384 $3,372 $3,358 $3,344 Tax benefits overall out of pocket" $93,000 $21,385 $21,652 $21,924 $22,203 $22,488 Other $8,333 $8,182 $8,025 $7,862 $7,692 Interest payment $3,698 $3,849 $4,006 $4,169 $4,339 Principal payments $300,000 $306,000 $312,120 $318,362 $324,730 $331,224 House value $90,000 $99,698 $109,667 $119,915 $130,451 $141,284 Total equity Cash at time sold $119,755 For Conditional Formatting Edit Paste 10 11 12 13 14 15 16 17 18 20 21 22 23 25 26 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Font Alignment Number Genera Wrap Text Merge nput Variables Inflation expectation 30% 2.00% Down payment $300,000 $210,000 Purchase price Loan amount $2,200 Initial monthly rent Interest rate (Fixed) 00% Rental growth rate $3,000 2.00% Mortgage initiation fee Property appreciation 2.00% Loan maturity (years) 30 $4,500 $1.002.57 Mortgage Payment nsurance $3,000 Holding period (years) Maintenance Expense growth 2.00% Selling expenses 6.50% Marginal tax rate 25.00% 8.50% Invetments opportunity $5,250 Property tax Option 1: Buy the house Year Out flows $93,000 Down payment Origination $7,803 $7,500 $7,650 $7,959 $8,118 nsurance & Maintenance $5,571 $5,683 $5,250 $5,355 $5,462 Property tax $12,031 $12,031 $12,031 $12,031 $12,031 Mortgage payments $93,000 $24,781 $25,036 $25,296 $25,561 $25,832 Total n flow $0 $3,396 $3,384 $3,372 $3,358 $3,344 Tax benefits overall out of pocket" $93,000 $21,385 $21,652 $21,924 $22,203 $22,488 Other $8,333 $8,182 $8,025 $7,862 $7,692 Interest payment $3,698 $3,849 $4,006 $4,169 $4,339 Principal payments $300,000 $306,000 $312,120 $318,362 $324,730 $331,224 House value $90,000 $99,698 $109,667 $119,915 $130,451 $141,284 Total equity Cash at time sold $119,755 For Conditional Formatting
