A family currently lives in an apartment whose monthly rent is $950. they are th
ID: 2723203 • Letter: A
Question
A family currently lives in an apartment whose monthly rent is $950. they are thinking of buying a house which would cost 220,000. they plan to live in this house for 5 years and sell it at the end of the 5th year. They would put a downpayment of 20,000 and finance the balance through a mortgage at 3.5% interest rate. The mortgage is to be repaid in 5 annual installments ( which include both principal and interest) at the end of each year for the next 5 years.The house will have the following additional expenses: annual maintenance: $1500; property taxes $5500; Insurance $1200; . Assume they are in tax bracket of 20% and the price of home, rent and expenditure increases by 2.5% per year. Their opportunity cost or required rate of return is 5% per year. Note that property taxes are tax deductible and there are no tax payable on capital gains. Use annual compounding for amortization schedule of mortgage
Calculate total cost of owning in year 3
answer $8511
should they buy this house or continue to rent
answer: buy since IRR is 5.47
calulate the rent saved during year 4
answer 12,276
calculate ownership operating advantage in year 3
answer (39,962)
calculate the post tax morgatage cost (principal repayment plus after tax interest cost) for year 1
answer $42,896
how would i go by solving this type of problem in excel ? step by step soultion please ?
Explanation / Answer
1. Cost of owning in year 3 is :
The costs of owning a home are :1200 + 5500 + 1500 = 8200 in year 1 .
This increase by 2.5% each year and hence the value of the in year 3 is = 8511
2. The IRR is obtained by using = IRR formula in excel wherein we have =IRR(cash flow range). The cash flow for buying is calculated and is shown as below:
3. The rent saved in year 3 is calculated in year 3 is as follows:
The rent in year 1 = 950* 12 = 11,400
This inreases by 1.025 in every year
So the cost after 3 years in year 4 = 11,400*1.025^ 3 = 12,276
4 and 5 cannot be calculted since the persons annual income is not given and is needed to be calculated.
Year 0 1 2 3 4 5 Down Payment -outflow -44000 Loan repayment each year -38,981 -38,981 38,981 -38,981 -38,981 Additional expenditure -8200 -8405 -8615 -8831 -9051 Sale of house 248910 Cash flow -44000 -47,181 -47,386 30,366 -47,812 2,00,878 IRR 5.47%Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.