Question

You have an opportunity to purchase the 23 site Plum Creek manufactured home/RV park for $250,000. The park caters to extended stay residents (six months or longer) and charges a market rental rate of $240 per site per month. You expect to be able to raise site rentals 3% per year to account for inflation. There are no tenant reimbursements or passthroughs. You plan a five year holding period so you create a six year proforma. The owner reports an annual vacancy rate of 10% but you believe an annual 15% vacancy rate every year of the holding period is a more realistic estimate. You use 15%. For expense estimates, you rely on public records (tax office), an income/expense statement provided by the owner/seller, and Darrell Hess & Associates, a national manufactured home park brokerage company that compiles expense data from hundreds of parks nationwide. The previous year’s property taxes were $5,042. The new assessment and tax rate has not been set, so the previous year’s taxes are used in your pro-forma for the first year. Hazard and liability insurance is estimated to be 2% of effective gross income (EGI) in first year. Each site is individually metered for electricity and the tenants are responsible for their usage. The park owner reported annual water and trash removal expenses of $5,460 for the previous year, and this amount appears reasonable for your first year projection. The annual administrative/management expense (part time on-site manager, advertising, legal fees, accounting fees and office expenses, etc.) is estimated to be 29% of EGI by Darrell Hess & Assoc. The owner did not report any maintenance expense as he did all of the work himself. You believe a $2,000 first year expense is reasonable for the road maintenance, landscape maintenance, etc. You expect expenses to increase 3% each year of the five year holding period except insurance and management expenses tied to EGI. Page 7 of 7 You have obtained a loan commitment from a bank for 80% (20% down payment aka equity contribution) of the purchase price with a 20 year amortization at 6% interest. 1) Create a six year Pro-forma statement of cash flows on an Excel spreadsheet. 2) Calculate the following ratios and indicators (all before tax) by Excel formulas at the bottom of the spreadsheet to three decimals: A) Operating expense ratio (annual operating expense/EGI); according to Darrell Hess & Associates, operators can expect a 50% operating expense ratio. B) All Five Years of Debt Coverage Ratio (NOI/debt service); 1.3 or greater is considered good by the lender. C) All Five Years Return on Equity (BTCF/equity invested); investor surveys indicate 13% or better is expected for each year. D) Using the IRV formula, calculate the indicated overall rate (cap rate) RO for Year One to see if it is within the 9-11% range for manufactured home parks indicated by a national investor survey. Use the $250,000 asking price as the value in IRV for the calculation. E) Calculate the value of the subject after the five year holding period (the reversion) using a 10.5% terminal RO. Remember to carry the pro-forma to a sixth year to obtain Year Six NOI. F) Calculate the unleveraged IRR of the cash flows using the $250,000 asking price. G) Calculate the NPV of the property using a 12% IRR

Answer 1

Solution

Firstlly we will prepare intrest payment A/c as the loan amount amortizes in 20 year .

LOAN = 250000*80%=$200000

Interest a/c
year	particular	$	year	particular	$
1	To cash	12000	1	Profit loss a/c	12000
2	To cash	11400	2	Profit loss a/c	11400
3	To cash	10800	3	Profit loss a/c	10800
4	To cash	10200	4	Profit loss a/c	10200
5	To cash	9600	5	Profit loss a/c	9600
6	To cash	9000	6	Profit loss a/c	9000

Now next we will prepare profit and loss statement and cash flow statement

Profit and loss a/c
Particulars	year 1	year 2	year 3	year4	year 5	year 6	Remarks
Revenue from rent	56304	56304	56304	56304	56304	56304	($240*36 sites*85) as avacancy is 15%
Add: additional as inflation expense@3% of rentals	1689.12	1689.12	1689.12	1689.12	1689.12	1689.12	Assuumed it is additionally collected apart from rent.
Total Revenue (A+B)	57993.12	57993.12	57993.12	57993.12	57993.12	57993.12	gross revenue
Expense
Tax	5042	5193.26	5349.0578	5509.529534	5674.81542	5845.059883	each year @3% increment
Hazard and Liability Insurance	1159.8624	1159.8624	1159.8624	1159.8624	1159.8624	1159.8624	each year @2% EGI
Administrative and management expenses	16818.0048	16818.0048	16818.0048	16818.0048	16818.0048	16818.0048	EACH YEAR @29% of EGI
Interest	12000	11400	10800	10200	9600	9000	year 5
Loan repayments		10000	10000	10000	10000	10000
Depriciation							assumed nil rated
total expense(b)	35019.8672	44571.1272	44126.925	43687.39673	43252.68262	42822.92708
Net Profit (a-b)	22973.2528	13421.9928	13866.195	14305.72327	14740.43738	15170.19292
Cash Flow Statement
Particulars	year 1	year 2	year 3	year4	year 5	year 6
operating income
Net income	22973.2528	13421.9928	13866.195	14305.72327	14740.43738	15170.19292
add: interest	12000	11400	10800	10200	9600	9000
Operating income	34973.2528	24821.9928	24666.195	24505.72327	24340.43738	24170.19292
investment income
RV PARK HOME	-250000
investment income	-250000	0	0	0	0	0
FINANCING INCOME
LOAN	200000
less:interest	12000	11400	10800	10200	9600	9000
Loan repayments		10000	10000	10000	10000	10000
FINANCING INCOME	188000	-21400	-20800	-20200	-19600	-19000

Ratio

Ratio	year 1	year 2	year 3	year4	year 5	year 6
operating expense ratio	25.84605484	33.95046447	33.43316173	32.91585898	32.39855624	31.8812535
Debt service (times)	3.3346044	2.632916912	2.779190074	2.942671843	3.126588833	3.335028089
BTCF/Equity invested	56.0305056	37.2305056	38.4305056	39.6305056	40.8305056	42.0305056