In: Accounting
Suppose you are buying a beach house condo for $450,000. You are planning to finance the ENTIRE amount of the condo via a mortgage for 30 years, 5% fixed, 0-points. The HOA fees, property taxes, Annual maintenance + Upkeep of the condo = $12,000 per year. Utilities are $150/month You are planning to rent this condo, and your rental income is $40,000 per year (gross), which goes up by 2% per year. Expenses to rent out the property are 30% of the gross income per year. Since this is an investment property, you will be depreciating it under residential rental property with 27.5 years depreciation. Assume that the condo will appreciate by 3% annually. 1. Provide a detailed calculation representing the annual schedule of income, expenses, depreciation, taxable income for this investment, and property appreciation. 2. If your MARR is 5%, determine if this is a good investment based on TAXABLE INCOME and APPRECIATION only. Find the IRR for this investment. Please, state all assumptions and show all your work.
Requirement a : |
|||||||||||||||||||||||||||||||
Schedule of income, expenses, taxable income | |||||||||||||||||||||||||||||||
Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 |
Value of condo | 450000 | 463500 | 477405 | 491727.2 | 506479 | 521673.3 | 537323.5 | 553443.2 | 570046.5 | 587147.9 | 604762.4 | 622905.2 | 641592.4 | 660840.2 | 680665.4 | 701085.3 | 722117.9 | 743781.4 | 766094.9 | 789077.7 | 812750.1 | 837132.6 | 862246.5 | 888113.9 | 914757.4 | 942200.1 | 970466.1 | 999580.1 | 1029567.5 | 1060454.5 | 1092268.1 |
Income | 40000 | 40800 | 41616 | 42448.32 | 43297.29 | 44163.23 | 45046.5 | 45947.43 | 46866.38 | 47803.7 | 48759.78 | 49734.97 | 50729.67 | 51744.27 | 52779.15 | 53834.73 | 54911.43 | 56009.66 | 57129.85 | 58272.45 | 59437.9 | 60626.65 | 61839.19 | 63075.97 | 64337.49 | 65624.24 | 66936.72 | 68275.46 | 69640.97 | 71033.79 | |
Expenses | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | -12000 | |
Utilty expense [ 12 x 150 ] | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | -1800 | |
Cost @102% | -12000 | -12240 | -12484.8 | -12734.5 | -12989.19 | -13248.97 | -13513.95 | -13784.23 | -14059.91 | -14341.11 | -14627.93 | -14920.5 | -15218.9 | -15523.28 | -15833.75 | -16150.4 | -16473.43 | -16802.9 | -17138.95 | -17481.73 | -17831.37 | -18188 | -18551.76 | -18922.79 | -19301.25 | -19687.27 | -20081.02 | -20482.64 | -20892.29 | -21310.14 | |
Dep. | -16363.64 | -16363.6 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.6 | -16363.64 | -16363.64 | -16363.64 | -16363.6 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | -16363.64 | ||||
Interest @5% | -22500 | -22161.3 | -21805.75 | -21432.38 | -21040.34 | -20628.7 | -20196.48 | -19742.65 | -19266.12 | -18765.77 | -18240.41 | -17688.8 | -17109.55 | -16501.37 | -15862.78 | -15192.3 | -14488.22 | -13748.97 | -12972.76 | -12157.74 | -11301.97 | -10403.42 | -9459.93 | -8469.27 | -7429.07 | -6336.87 | -5190.06 | -3985.9 | -2721.54 | -1393.96 | |
Taxable income |
-24663.64 | -23765 | -22838.19 | -21882.2 | -20895.88 | -19878.08 | -18827.57 | -17743.09 | -16623.29 | -15466.82 | -14272.2 | -13037.9 | -11762.42 | -10444.02 | -9081.02 | -7671.59 | -6213.86 | -4705.85 | -3145.5 | -1530.66 | 140.92 | 1871.59 | 3663.86 | 5520.27 | 7443.53 | 9436.46 | 11502 | 30006.92 | 32227.14 | 34529.69 |
Loan calculation | |||||||||||||||||||||||||||||||
Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 |
Principle | 450000 | 443226.9 | 436115.1 | 428647.7 | 420806.9 | 412574.1 | 403929.7 | 394853 | 385322.5 | 375315.5 | 364808.1 | 353775.4 | 342191 | 330027.4 | 317255.6 | 303845.2 | 289764.4 | 274979.4 | 259455.3 | 243154.9 | 226039.5 | 208068.3 | 189198.6 | 169385.4 | 148581.5 | 126737.4 | 103801.1 | 79718.04 | 54430.79 | 27879.19 | |
EMI | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | 29273.15 | |
Interest | 22500 | 22161.34 | 21805.75 | 21432.38 | 21040.34 | 20628.7 | 20196.48 | 19742.65 | 19266.12 | 18765.77 | 18240.41 | 17688.77 | 17109.55 | 16501.37 | 15862.78 | 15192.26 | 14488.22 | 13748.97 | 12972.76 | 12157.74 | 11301.97 | 10403.42 | 9459.93 | 8469.27 | 7429.07 | 6336.87 | 5190.06 | 3985.9 | 2721.54 | 1393.96 | |
Principle deduction | 6773.15 | 7111.8 | 7467.39 | 7840.76 | 8232.8 | 8644.44 | 9076.66 | 9530.5 | 10007.02 | 10507.37 | 11032.74 | 11584.38 | 12163.6 | 12771.78 | 13410.37 | 14080.88 | 14784.93 | 15524.17 | 16300.38 | 17115.4 | 17971.17 | 18869.73 | 19813.22 | 20803.88 | 21844.07 | 22936.28 | 24083.09 | 25287.24 | 26551.61 | 27879.19 | |
Principle due | 443226.85 | 436115.1 | 428647.7 | 420806.9 | 412574.1 | 403929.7 | 394853 | 385322.5 | 375315.5 | 364808.1 | 353775.4 | 342191 | 330027.4 | 317255.6 | 303845.2 | 289764.4 | 274979.4 | 259455.3 | 243154.9 | 226039.5 | 208068.3 | 189198.6 | 169385.4 | 148581.5 | 126737.4 | 103801.1 | 79718.04 | 54430.8 | 27879.18 | 0 | |
Step 1 : calculation of EMI | |||||||||||||||||||||||||||||||
Cost of house condo | 450,000 | ||||||||||||||||||||||||||||||
Annual interestrate | 5% | use PMT function to calculateEMI using the figures given | |||||||||||||||||||||||||||||
No of peiods[ years ] | 30 | ||||||||||||||||||||||||||||||
EMI = | ($29,273.15) | It is showing negative because, it is a outflow |
Requirement 2 :
For calculaing the IRR, we need to add back the depreciation in the taxable income and find out the IRR using the funcion in excel
conclusion IRR is 12% , therefore it is a good investment as MARR is 5% only.
Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | |
Taxable income | -24663.64 | -23765 | -22838.19 | -21882.2 | -20895.88 | -19878.08 | -18827.57 | -17743.09 | -16623.29 | -15466.82 | -14272.2 | -13037.9 | -11762.42 | -10444.02 | -9081.02 | -7671.59 | -6213.86 | -4705.85 | -3145.5 | -1530.66 | 140.92 | 1871.59 | 3663.86 | 5520.27 | 7443.53 | 9436.46 | 11502 | 30006.92 | 32227.14 | 34529.69 | |
Dep. | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | 16363.64 | ||||
Total income | -8300 | -7401.34 | -6474.55 | -5518.56 | -4532.24 | -3514.44 | -2463.93 | -1379.45 | -259.65 | 896.82 | 2091.44 | 3325.71 | 4601.22 | 5919.62 | 7282.62 | 8692.05 | 10149.78 | 11657.79 | 13218.14 | 14832.98 | 16504.56 | 18235.23 | 20027.5 | 21883.91 | 23807.17 | 25800.1 | 27865.64 | 30006.92 | 32227.14 | 34529.69 | |
IRR = | 12% | ||||||||||||||||||||||||||||||
Use IRR function in the excel to calculate IRR | |||||||||||||||||||||||||||||||
= IRR ( select value from year 1 to 30 of total income ). | |||||||||||||||||||||||||||||||