Question

A 5 storey office block is in the market for R 50 000 000 (including transfer duties,
administration and commission expenses). The following information is
applicable:
? The office block consists of 5000 m2 lettable space.
? You realize upon looking at the drawings that there is a possibility to add
another floor consisting of 1000 m2 lettable space. (The height restriction
for this particular property is 6 storeys).
? The development cost for the additional floor will amount to R10 000 000
over a 6 month construction period.(For purposes of this question, assume
that this payment is only made at the end of the 6 month period)
? A financial institution offers you a mortgage bond of 80% on the total cost
of the office block, including the new additional floor. The amortization rate
is 11%, monthly compounded. Bond payments will be made at the end of
each month.
? You gather from the current rental market that you may ask R 200 per
square meter, which include operating expenses.
? Diverse expenses including maintenance, property tax, water and lights,
amounting to R 50 per square metre.
? The expected growth in the value of the property is at 15% per annum.
? The lease period is 10 years, while the bond period is 20 years.
? Escalation on the lease amount as well as on the diverse expenses is at
6% per annum.
? You plan to sell the property at the end of the investment period which is
after 10 years.
? The discount rate where applicable is 11%, calculated monthly.
(a) Calculate the net monthly cash flow for the first year, after taking into account
the development costs, bond and diverse expenses as well as the lease
payments.
(b) Determine the net present value for the first year.
(c) Determine the Net Present Value for the investment period.
(d) What is the internal rate of return?

Answer 1

	A	Cost of purchase	50,000,000
	B	Cost of additional floor	10,000,000
	C=A+B	Total Cost	60,000,000
	D=0.8*C	Mortgage bond amount	48,000,000
		Mortgate interest =11%
		Monthly interest=(11/12)%
		Number of months of mortgage	240	(20*12)
	E	Monthly Mortgate payment	495,450	(Using PMT function of excel with Rate=(11/12)%, Nper=240,PV=-48000000)
	F	Monthly rental	1,200,000	(200*(5000+1000)
	G	Diverse expenses per month	300,000	50*6000
	H=F-G-E	Net monthly cash flow for the first year	404,550
	I	Initial Cash Flow for down payment	(12,000,000)	(60000000-48000000)
		Discount rate=(11/12)% monthly
	J	Present value of monthly cash flow of first year	4,577,307	(Using PV function of excel with Rate=(11/12)%, Nper=12,Pmt=-404550)
	K=I+J	Net present value for the first year	(7,422,693)
		Future Value of Mortgage Bond after 10 years=120 months	143,479,181	(Using FV function of excel with Rate=(11/12)%, Nper=120,PV=-48000000)
		Future Value of Monthly Bondents after 10 years=120 months	107,511,728	(Using FV function of excel with Rate=(11/12)%, Nper=120,Pmt=-495450)
		Mortgage payment at the end of 10 years	35,967,453	(143479181-107511728)
		CASH FLOWS FOR 10 Years
	N	YEAR	0	1	2	3	4	5	6	7	8	9
	E	Monthly Mortgate payment	495,450	495,450	495,450	495,450	495,450	495,450	495,450	495,450	495,450	495,450
	F	Monthly rental	1,200,000	1,272,000	1,348,320	1,429,219	1,514,972	1,605,871	1,702,223	1,804,356	1,912,618	2,027,375
	G	Diverse expenses per month	300,000	318,000	337,080	357,305	378,743	401,468	425,556	451,089	478,154	506,844
	H=F-G-E	Net Monthly Cash flow	404,550	458,550	515,790	576,464	640,779	708,953	781,217	857,817	939,013	1,025,081	SUM
Using FV function with Rate=(11/12)%, Nper =12, Pmt=-H	L	PresentValue of Monthly cash flow at end of year	4,577,302	5,188,289	5,835,935	6,522,439	7,250,134	8,021,490	8,839,128	9,705,824	10,624,522	11,598,341	78,163,403
	P	SUM OF PV of Monthly cash flow	78,163,403
	Q	Initial Cash flow of down payment	(12,000,000)
	R=60 million *(1.15^10)	Value of property at the end of 10 years	242,733,464									242,733,464
	S	Mortgage payment at the end of 10 years	(35,967,453)									(35,967,453)
	T=R+S	Total terminal Cash Flow	206,766,011
	U=T/(1.11^10)	Present value (PV) ofTerminal Cash Flow	72,819,780
	NPV=P+Q+U	Net Present value of investment period	138,983,182
		Year	0	1	2	3	4	5	6	7	8	9	10
		Total Cash flow	(7,422,693)	5,188,289	5,835,935	6,522,439	7,250,134	8,021,490	8,839,128	9,705,824	10,624,522	11,598,341	206,766,011
		Internal Rate of Return (Using IRR function over Total Cash Flow)	85%