Spreadsheet Problem. Use the Ch7_Rent_vs_Own worksheet in the Excel workbook provided on the website. Determine the after-tax IRR for owning versus renting in each of the five years with the following changes in the original assumptions in the spreadsheet:
a. The homeowner has a 15 percent marginal tax rate instead of 28 percent.
b. Rents and property values will not increase over the five years.
c. The loan amount is $105,000 instead of $120,000.
d. The initial rent for year
Net cost of renting Rents $12,000 $12,240 \begin{tabular}{|l|l|l|} \hline$12,485 & $12,734 & $12,989 \\ \hline \end{tabular} Net Cash Flow from Owning Before Sale Cost of renting Cost of owning After Tax Cash Flow Own vs Rent \begin{tabular}{|r|r|r|r|r|} \hline$12,000 & $12,240 & $12,485 & $12,734 & $12,989 \\ \hline 9.859 & 9.936 & 10,016 & 10,099 & 10,185 \\ \hline$2,141 & $2,304 & $2,469 & $2,635 & $2,804 \\ \hline \end{tabular} BT Cash Flow - Sale Sales price Selling Costs Mortgage balance Benefit from sale (own - rent) AT Cash Flow - Sale Sales price Less selling costs Less purchase price Gain on sale Exclusion Taxable gain Tax After tax cash flow \begin{tabular}{|r|r|r|r|r|} \hline$153,000 & $156,060 & $159,181 & $162,365 & $165,612 \\ \hline 10,710 & 10,924 & 11,143 & 11,366 & 11,593 \\ \hline 150,000 & 150,000 & 150,000 & 150,000 & 150,000 \\ \hline(7,710) & (4,864) & (1,961) & 999 & 4,019 \\ \hline 0 & 0 & 0 & 999 & 4,019 \\ \hline 0 & 0 & 0 & 0 & 0 \\ \hline 0 & 0 & 0 & 0 & 0 \\ \hline 23,509 & 27,662 & 31,966 & 36,430 & 41,061 \\ \hline \end{tabular} Cash savings and IRR summary \begin{tabular}{|r|r|r|r|r|r|r|r|} \hline & & Cash flows & & & ATIRR \\ \hline Year Sold & 0 & 1 & 2 & 3 & 4 & 5 & \\ \hline 1 & 30,000 & $25,650 & & & & & 14.50% \\ \hline 2 & 30,000 & 2,141 & 29,966 & & & \\ \hline 3 & 30,000 & 2,141 & 2,304 & 34,435 & & & 3.67%% \\ \hline 4 & 30,000 & 2,141 & 2,304 & 2,469 & 39,065 & 12.34% \\ \hline 5 & 30,000 & 2,141 & 2,304 & 2,469 & 2,635 & 43,865 & 13.71% \\ \hline \end{tabular} Net cost of renting Rents $12,000 $12,240 \begin{tabular}{|l|l|l|} \hline$12,485 & $12,734 & $12,989 \\ \hline \end{tabular} Net Cash Flow from Owning Before Sale Cost of renting Cost of owning After Tax Cash Flow Own vs Rent \begin{tabular}{|r|r|r|r|r|} \hline$12,000 & $12,240 & $12,485 & $12,734 & $12,989 \\ \hline 9.859 & 9.936 & 10,016 & 10,099 & 10,185 \\ \hline$2,141 & $2,304 & $2,469 & $2,635 & $2,804 \\ \hline \end{tabular} BT Cash Flow - Sale Sales price Selling Costs Mortgage balance Benefit from sale (own - rent) AT Cash Flow - Sale Sales price Less selling costs Less purchase price Gain on sale Exclusion Taxable gain Tax After tax cash flow \begin{tabular}{|r|r|r|r|r|} \hline$153,000 & $156,060 & $159,181 & $162,365 & $165,612 \\ \hline 10,710 & 10,924 & 11,143 & 11,366 & 11,593 \\ \hline 150,000 & 150,000 & 150,000 & 150,000 & 150,000 \\ \hline(7,710) & (4,864) & (1,961) & 999 & 4,019 \\ \hline 0 & 0 & 0 & 999 & 4,019 \\ \hline 0 & 0 & 0 & 0 & 0 \\ \hline 0 & 0 & 0 & 0 & 0 \\ \hline 23,509 & 27,662 & 31,966 & 36,430 & 41,061 \\ \hline \end{tabular} Cash savings and IRR summary \begin{tabular}{|r|r|r|r|r|r|r|r|} \hline & & Cash flows & & & ATIRR \\ \hline Year Sold & 0 & 1 & 2 & 3 & 4 & 5 & \\ \hline 1 & 30,000 & $25,650 & & & & & 14.50% \\ \hline 2 & 30,000 & 2,141 & 29,966 & & & \\ \hline 3 & 30,000 & 2,141 & 2,304 & 34,435 & & & 3.67%% \\ \hline 4 & 30,000 & 2,141 & 2,304 & 2,469 & 39,065 & 12.34% \\ \hline 5 & 30,000 & 2,141 & 2,304 & 2,469 & 2,635 & 43,865 & 13.71% \\ \hline \end{tabular}
