Question

Mr. Bailey has approached you regarding an opportunity he has to become a homeowner.  Mr. Bailey has asked you to perform a financial analysis to determine if this would be a wise move to purchase the new condominium, or if he should continue to rent.  You will create an Excel spreadsheet and a written Word document to explain the results for Mr. Bailey.  

Currently he rents a downtown condominium for $2500 per month. A neighboring unit has recently gone onto the market for $500,000. Mr. Bailey feels that this would make a great investment for him and it would make sense to stop renting and purchase this unit. Mr. Bailey can put down 20% on the new unit. He will assume a 30-year mortgage for the condominium with a 6% APR. Mr. Bailey plans to remain in the condominium for 5 years and then sell and move to suburban Berkshire Farms.

Financial Details
If Mr. Bailey purchases the condo, he will have additional monthly fees of:

$1000 HOA fee (maintenance, pool, health club)
$300 property taxes
$100 repairs

You have reviewed real estate trends and have determined that over 5 years the condo will appreciate approximately 3% per year. When he sells the condo, you estimate that he will pay 5% in commission and an additional $2,000 in closing costs.

Excel Spreadsheet:

1. Mortgage payment with costs to Buy versus Rent (Sheet 1)
2. Amortization Schedule for the mortgage (Sheet 2)
3. Present value of the proceeds if he were to sell the property in 5 years (Sheet 3)

Word Document:

In a professional 3- 5 page written analysis explain the results of your findings for Mr. Bailey. Provide a detailed written explanation of your calculations for the present value of the proceeds if he were to sell the property in 5 years. In addition, provide an explanation of the importance of the time value of money and the key decisions to be made in this buy versus rent decision. You should also include qualitative decisions to consider in this scenario for Mr. Bailey (e.g. what are some factors which influence this buy versus rent decision which should be considered).

Answer 1

Statement Rent Vs Mortgage payment
As per Currently rents
Year	1	2	3	4	5	Total
	$	$	$	$	$	$
Per Month	2500	2500	2500	2500	2500	-
Annual Cost	30000	30000	30000	30000	30000	150000
As per Loan payment option
Market Price	500000
Down payment @ 20% Market Price	100000
Balance on which loan need to be take	400000
Rate on interest	6%
Year	1	2	3	4	5	Total
	$	$	$	$	$	$
Interest amt	24000	23200	22400	21600	20800	112000
Principal repayment	13333	13333	13333	13333	13333	66667
Annual outflow	37333	36533	35733	34933	34133	178667
HOA fee	12000	12000	12000	12000	12000	60000
property taxes	3600	3600	3600	3600	3600	18000
repairs	1200	1200	1200	1200	1200	6000
	54133	53333	52533	51733	50933	262667
Present value of the proceeds
Market Price	500000
increase @ 3%	15000
increase @ 3% per annual for 5yrs	75000
Market Price after 5yrs	575000
Discounting @ 3% for 5yrs present value will be	496000
Profit on sale after sale and end of 5yrs
Discounting @ 3% for 5yrs present value will be	496000
Total cost outflow for Mortgage payment	262667
Gain on sale	233333
less commission present value	24800
sub-total	208533
closing costs	2000
Final Gain at present value	206533