Question

please show all steps

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 lke 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 (ATIRRI The attached Excel spreadsheet might help you to put all information and required calculations in an organized way! CHINT: you are doing all math right, the value in cell E14 should be 13,099.94.) (a) if you can earn a 45% annual return on other investments, Investing your money into this house and selling after 2 years is a "1" for "good" or "2" for "bad") idea financially. That's because the calculated IRR is v (use "1" for greater than" or "2" for "lower than the consum.hr 7 2 3 5 6 7 0 w E R T Y o P tab S D F G Н. J к Cane Art Z X C V B N M 26 control Command AVON option Command

Answer 1

Tax sheild on Tax sheild Maintenance Depreciation on Interest Sale Value Annual Loan Property Repaid Tax Year Cashflow after tax 0 (a) 1 2 3 0 (d) -1000 -1000 -1000 -1000 -1000 0 (e) 1667 1667 1667 1667 1667 1667 1667 4 0 5 0 6 -1000 0 7 0 8 -1000 -1000 -1000 0 9 0 1667 1667 1667 1667 10 (h=b+c+d-e-f-g) -11,741 -12,171 -12,643 -13,160 - 13,727 -14,349 -15,032 -15,781 - 16,603 -17,505 -18,495 -19,581 -20,774 -22,083 -23,521 -25,100 -26,833 -28,737 -1000 0 0 11 (f) 2,000 1,970 1,938 1,905 1,870 1,834 1,795 1,755 1,713 1,668 1,621 1,572 1,521 1,467 1,410 1,350 1,288 1,222 1,153 1,081 1,005 925 -1000 -1000 -1000 12 0 13 0 1667 1667 1667 1667 14 (b) (c) -10,408 -4000 - 10,408 -4400 - 10,408 -4840 -10,408 -5324 -10,408 -5856.4 - 10,408 -6442.04 -10,408 -7086.24 -10,408 -7794.87 -10,408 -8574.36 -10,408 -9431.79 -10,408 -10375 -10,408 -11412.5 -10,408 -12553.7 -10,408 -13809.1 -10,408 - 15190 -10,408 -10,408 -18379.9 -10,408 -20217.9 -22239.7 -10,408 -24463.6 - 10,408 -26910 -10,408 -29601 -10,408 -32561.1 - 10,408 -35817.2 -10,408 -39398.9 -10,408 -43338.8 -10,408 -47672.7 -10,408 -52440 -10,408 -57684 - 10,408 -63452.4 -1000 0 15 -1000 0 16 - 16709 -1000 0 17 0 0 18 -1000 -1000 -1000 -1000 19 -10,408 1667 1667 1667 1667 1667 1667 1667 0 -30,827 20 0 21 -1000 0 22 0 -1000 -1000 23 1667 841 0 24 -1000 25 -1000 -33,124 -35,646 -38,417 -41,461 -44,805 - 48,479 -52,516 -56,952 -61,827 -67,183 -63,073 -9,02,146 26 27 1667 753 0 1667 660 0 1667 563 0 1667 461 0 1667 354 0 1667 242 0 1667 120 10000 Total After tax cash flow -1000 -1000 -1000 -1000 -1000 28 29 30 Note 1: It is assumed house property have 30 year of life M& depreciation as per SLM is charged and sold at the end of 30 year at a price of $ 15,000. SO SLM per year (200000/30) $ 6,667 and tax sheild on depreciation is (6667 * 25%) $ 1,667. Note 2: Annual Loan payment = Loan taken/PVIFA @ 5%, n=30 Annual Loan payment = (200000*80%)/15.3725 = 10,408 Loan amortization shedule Principal Paid (e=d-c) 2,408 2,528 2,655 2,788 2,927 3,073 3,227 3,388 6,672 Annual Year Op. Loan Bal. Interest Instalment (a) (b) (c =b*5%) (d) 1 1,60,000 8,000 10,408 2 1,57,592 7,880 10,408 3 1,55,064 7,753 10,408 4 1,52,409 7,620 10,408 5 1,49,621 7,481 10,408 6 1,46,694 7,335 10,408 7 1,43,621 7,181 10,408 8 1,40,394 7,020 10,408 9 1,37,006 6,850 10,408 10 1,33,448 10,408 11 1,29,712 6,486 10,408 12 1,25,790 6,290 10,408 13 1,21,672 6,084 10,408 14 1,17,347 5,867 10,408 15 1,12,806 5,640 10,408 16 1,08,039 5,402 10,408 17 1,03,033 5,152 10,408 18 97,776 4,889 10,408 19 92,257 4,613 10,408 20 86,462 4,323 10,408 21 80,377 4,019 10,408 22 73,988 3,699 10,408 23 67,279 3,364 10,408 24 60,235 3,012 10,408 25 52,839 2,642 10,408 26 45,073 2,254 10,408 27 36,919 1,846 10,408 28 28,357 1,418 10,408 29 19,367 968 10,408 30 9,927 481 10,408 3,558 3,736 3,922 4,118 4,324 4,541 4,768 5,006 5,256 5,519 5,795 6,085 6,389 6,709 7,044 7,396 7,766 8,154 8,562 8,990 9,440 9,927 Tax sheild Closing on Interest Loan Bal. (f=b-e) (g=c* 25%) 1,57,592 2,000 1,55,064 1,970 1,52,409 1,938 1,49,621 1,905 1,46,694 1,870 1,43,621 1,834 1,40,394 1,795 1,37,006 1,755 1,33,448 1,713 1,29,712 1,668 1,25,790 1,621 1,21,672 1,572 1,17,347 1,521 1,12,806 1,467 1,08,039 1,410 1,03,033 1,350 97,776 1,288 92,257 1,222 86,462 1,153 80,377 1,081 73,988 1,005 67,279 925 60,235 841 52,839 753 45,073 660 36,919 563 28,357 461 19,367 354 9,927 242 0 120 Note 3: Property tax for 1st year 200000*2%=4,000 and will increase annaully at 10%. Note 4: Sales Value Cost of sales Net Sales 15000 $ -5000 $ 10000 $