Please help !!!

Excel format to help

(CHAPTER 7) You are making a buying-vs-renting decision. You have the following information: RENTI BUYI The house you like costs $200,000. You expect home values to increase by 10% every year. Property taxes: 2% of house value, due at the end of each year. (In other words, the property taxes due at the end of year 1 are based on the house value in year o.) Maintenance: $1,000 per year. You would take a home mortgage loan with an LTV of 80%. Loan information: 30-year term, fully amortizing with fixed annual payments, 5% annual interest rate, remaining balance due upon house sale. When you sell the house the estimated selling expenses are $5,000, and you expect no capital gain taxes. If you instead rent a house just like this one, you'd be paying $15,000 on rent every year. Your income puts you in a 25% income tax bracket. Calculate the after-tax cash flows if you buy rather than rent. Use them to calculate the after-tax Internal Rate of Return (ATIRR). The attached Excel spreadsheet might help you to put all information and required calculations in an organized way! (HINT: If you are doing all math right, the value in cell E14 should be 13,099.94.) (a) If you can earn a 30% annual return on other investments, investing your money into this house and selling it after 2 years is a 1 (use "1" for "good" or "2" for "bad") idea financially. That's because the calculated IRR is (Select ] (use "1" for "greater than" or "2" for "lower than") the 30% required annual return. The calculated after-tax IRR equals (Select] [Select ] [Select] V [Select] % (round to 2 decimal places; use "O" for any blank values). (b) If you can earn a 30% annual return on other investments, investing your money into this house and selling it after 3 years is a Select ] (use "1" for "good" or "2" for "bad") idea financially. That's because the calculated IRR is [ Select] (use "1" for "greater than" or "2" for "lower than") the 30% required annual return. The calculated after-tax IRR equals (Select] V [ Select ] [Select] (Select] % (round to 2 decimal places; use "O" for any blank values). (c) Using 30% as your required return, in order for you to be indifferent between buying and not buying for 3 years, the LTV needs to approximately equal [Select ] [ Select ] %. (HINT: Use "solver" or "goal seek" in Excel!) File Home Insert Page Layout Formulas Data Review View Add-ins Help Calibri 11 y AA == X Cut [G Copy Paste Format Painter Clipboard a Wrap Text Merge & Center A Font Alignment B4 X B Year 0 Year 1 D Year 2 E Year 3 5 1 2 Equity invested 3 4 House price 5 6 Ownership expenses 7 1. tax-deductible: 8 ......1.1. property tax 9 ......1.2. loan payment: interest 10 2. non-tax-deductible: 11 ......2.1. maintenance 12 ......2.2. loan payment: principal 13 14 Total after-tax ownership expenses 15 16 Rent 17 18 Cash flow upon sale if sell in X years 19 1. house price 20 2. selling expenses 21 3. loan balance due 22 23 Total after-tax buy-instead-of-rent cash flows (CHAPTER 7) You are making a buying-vs-renting decision. You have the following information: RENTI BUYI The house you like costs $200,000. You expect home values to increase by 10% every year. Property taxes: 2% of house value, due at the end of each year. (In other words, the property taxes due at the end of year 1 are based on the house value in year o.) Maintenance: $1,000 per year. You would take a home mortgage loan with an LTV of 80%. Loan information: 30-year term, fully amortizing with fixed annual payments, 5% annual interest rate, remaining balance due upon house sale. When you sell the house the estimated selling expenses are $5,000, and you expect no capital gain taxes. If you instead rent a house just like this one, you'd be paying $15,000 on rent every year. Your income puts you in a 25% income tax bracket. Calculate the after-tax cash flows if you buy rather than rent. Use them to calculate the after-tax Internal Rate of Return (ATIRR). The attached Excel spreadsheet might help you to put all information and required calculations in an organized way! (HINT: If you are doing all math right, the value in cell E14 should be 13,099.94.) (a) If you can earn a 30% annual return on other investments, investing your money into this house and selling it after 2 years is a 1 (use "1" for "good" or "2" for "bad") idea financially. That's because the calculated IRR is (Select ] (use "1" for "greater than" or "2" for "lower than") the 30% required annual return. The calculated after-tax IRR equals (Select] [Select ] [Select] V [Select] % (round to 2 decimal places; use "O" for any blank values). (b) If you can earn a 30% annual return on other investments, investing your money into this house and selling it after 3 years is a Select ] (use "1" for "good" or "2" for "bad") idea financially. That's because the calculated IRR is [ Select] (use "1" for "greater than" or "2" for "lower than") the 30% required annual return. The calculated after-tax IRR equals (Select] V [ Select ] [Select] (Select] % (round to 2 decimal places; use "O" for any blank values). (c) Using 30% as your required return, in order for you to be indifferent between buying and not buying for 3 years, the LTV needs to approximately equal [Select ] [ Select ] %. (HINT: Use "solver" or "goal seek" in Excel!) File Home Insert Page Layout Formulas Data Review View Add-ins Help Calibri 11 y AA == X Cut [G Copy Paste Format Painter Clipboard a Wrap Text Merge & Center A Font Alignment B4 X B Year 0 Year 1 D Year 2 E Year 3 5 1 2 Equity invested 3 4 House price 5 6 Ownership expenses 7 1. tax-deductible: 8 ......1.1. property tax 9 ......1.2. loan payment: interest 10 2. non-tax-deductible: 11 ......2.1. maintenance 12 ......2.2. loan payment: principal 13 14 Total after-tax ownership expenses 15 16 Rent 17 18 Cash flow upon sale if sell in X years 19 1. house price 20 2. selling expenses 21 3. loan balance due 22 23 Total after-tax buy-instead-of-rent cash flows