Question

Assume 100% occupancy

The specifics of the opportunity are as follows:

Property purchase price $2,250,000. Loan to Value Ratio (LTV) 80%

Loan Terms: fully amortized over 30 years at 4.25% APR paid monthly

The property offers six recently updated “luxury” apartments. Each apartment has 3 bedrooms and 2.5 baths in 1,800 square feet of living space. Rents are $1,690 per month per apartment. All six units are leased but one of the units only receives half rent because the tenants in that unit are responsible for year round cosmetic maintenance of the walkways and greenspaces and also minor emergency repairs. You plan to invest an additional $150,000 in paid in capital for cosmetic updates on the property and closing fees. The tax rate on the property is 1.75% of the purchase price. For the sake of simplicity will assume that taxes and rents are constant.

1 What is your first year profit (revenues minus expenses) from this investment both in dollars and in rate of return?

Instructions: below is a description of an investment. Set up a spreadsheet for valuing this opportunity using Excel functions to answer the questions given in the bullet points below such that by simply entering the values given in the bullet points in specific cells the values in your model will update and provide correct answers

Answer 1

* Equations to be potsed in excel are mentioned as excel format is unacceptable.

in Calculating revenue we need to add Full rent(Perceived) including Tenant who undertake maintance and Substract half of his Rent in Expenses As maintanace.

No implication of depreciation is given. Rent and tax are considered constant.

investment of 150,000 is capital investment so didnt consider as expenditure

	No: of Appartments	Rent Per Month	Total Month or Fraction of Occupancy	Revenue(C2*D4) ie,(1690*72)
Revenue	6	1690	12months*6=72 (in excel 12*B2)	121,680
Expenses				Total Expenses
Maintanance			(1690*(1/2)*12)=10,140
Loan Interest	Loan Amount=2,250,000*80%(LTV given as 80%)=1,800,000	Interest Rate=4.25%	(B6*C6) ie,2.25M*.0.0425=76,500
				(D5+D6)=86,640
PreTax Profit				(E2-E8),ie121,680-86,640=35,040
Tax @1.75%of property value			(2,250,000*1.75%)	(39,375)
Profit/(Loss) After Tax				(E9-E10)ie,35040-39375=(-4335)