Question

In: Accounting

Suppose you are buying a beach house condo for $450,000. You are planning to finance the...

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.

Solutions

Expert Solution

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 ).

Related Solutions

You are buying a condo on the beach in Galveston, TX. Here are the details. Overview:...
You are buying a condo on the beach in Galveston, TX. Here are the details. Overview: condo 893 sqft 2 beds $286/sqft 2 baths 673 Built in 2006 131 days on Trulia Description: New washer/dryer, fresh paint in living and kitchen in October. This condo is one of the best on Seawall. enjoy the beautiful interior with its relaxed atmosphere. You'll be drawn to the balcony where you will enjoy the amazing biew of the ocean and enjoy the sounds...
Suppose you are buying your first condo for $160,000, and you will make a $15,000 down...
Suppose you are buying your first condo for $160,000, and you will make a $15,000 down payment. You have arranged to finance the remainder with a 30-year, monthly payment, amortized mortgage at a 4.5% nominal interest rate, with the first payment due in one month. What will your monthly payments be?
Suppose you own a house where you live and a condo for rent. The monthly rent...
Suppose you own a house where you live and a condo for rent. The monthly rent is $1,600 and you will get it at the end of each month. Yet you have to pay maintenance fees and insurance premium. They are $600 per month in total. The property tax is proportional to the (present) value of the property. The tax rate is 0.1% per month and you pay the tax every month on the last day of each month with...
2. Assume that you are buying a house for $500, 000 and you haveto finance...
2. Assume that you are buying a house for $500, 000 and you have to finance 80% of it from the bank at 6% p.a. for 20 years. It is to be repaid monthly.a) What is the monthly repayment amount? Assume the interest is compounded monthly.b) At the end of year 5 you have to sell your house and move to the U.S.i. How much money do you still owe the bank?ii. How much principal and interest have you paid...
(In Java Using recursive techniques) When buying a house or condo (using a mortgage), or when...
(In Java Using recursive techniques) When buying a house or condo (using a mortgage), or when taking out a loan, you’ll likely wind up with some form of a fixed monthly payment. Mortgages and loans work this way: You take out a loan from the bank or lender for a specified amount of money at a specified annual interest rate with a specified monthly payment amount At the beginning of every month, interest is added to the amount you owe...
Using Java: When buying a house or condo (using a mortgage), or when taking out a...
Using Java: When buying a house or condo (using a mortgage), or when taking out a loan, you’ll usually wind up with some form of a fixed monthly payment. Mortgages and loans work this way: You take out a loan from the bank or lender for a specified amount of money at a specified annual interest rate with a specified monthly payment amount At the beginning of every month, interest is added to the amount you owe the bank or...
You are planning to buy a house appraised for $350,000 and finance it through a mortgage...
You are planning to buy a house appraised for $350,000 and finance it through a mortgage of $300,000. What is the loan-to-value ratio and is it be above or below the normal cutoff set by your lender of 80 percent for a prime mortgage? Being securely employed, your take-home pay is $2,300 per month and you have no substantial other debts. Your lender has offered you a 2.5 percent, 30 year, fixed-rate mortgage. What is the amount that you will...
The Jones family is buying a new house at the price of $165,000. They will finance...
The Jones family is buying a new house at the price of $165,000. They will finance it with a twenty-year mortgage that has an interest rate of 8%. (a)Assuming that the family can make a $39,000 down payment, what will their monthly mortgage payment be? (b)If the family could increase the down payment by $10,000,then how much would their monthly mortgage payment be? (c)In total, how much money can the family save by making the larger down payment
Suppose you need a 5-year mortgage loan to purchase a house that worth $450,000. The bank...
Suppose you need a 5-year mortgage loan to purchase a house that worth $450,000. The bank offers two interest rate options for you to choose: (i). Fixed rate at 3.5%. Interest rate will remain fixed for that loan's entire term, no matter how the market interest rate changes. (ii). Variable rate which varies with market interest rate and is typical 1.5% above the market interest rate. Which one would you choose? Briefly explain why.
MORTGAGE AMORTIZATION: Suppose you are considering buying a house with a market price of $350,000. You...
MORTGAGE AMORTIZATION: Suppose you are considering buying a house with a market price of $350,000. You plan on making a down payment of 20% and financing the remainder using a fully amortizing, 30-year, monthly payment mortgage with a fixed interest rate of 4.50%. Assuming your first payment is due exactly one month from today... • What is your required monthly payment? • During the first five years (i.e., 60 months), what is the percentage of your total payments which go...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT