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 ). | |||||||||||||||||||||||||||||||
ekkarill92 answered 1 year agoRelated 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
Latest Questions
- What are the basic elements of a valid contract? What are the elements of an offer?...
- Jean-Jacques Rousseau and John Locke both seem to have an idea as to what best impacts...
- 1. Provide an overview of the systems design phase. 2. Explain Apple’s view of user interface...
- Please consider the following, and offer three (3) responses between one (1) and three (3) sentences...
- Assume you are a new employee at Payroll Inc. a company which process payroll, advises small...
- Write a program that prints the count of all prime numbers between A and B (inclusive),...
- Write an HTML file for a web page that contains the items below. Use an internal...
ADVERTISEMENT