Question

Explain formulas used.

A company is set up to refurbish old factories and turn them into retail outlets. The company purchases 5 factories each costing half a million pounds. One factory is purchased at the start of each of the next 5 years. The cost of refurbishment is £20,000 per factory and is payable continuously for one year after the purchase. Once the refurbishment for a particular factory is complete, retail stores pay rent to the company for the factory at a rate of £48,000 pa payable monthly in arrears. Eaclh factory is sold 10 years after completion of its refurbishment for E600,000. The company employs a manager to run this project. She is paid £50,000 pa payable at the end of each month whilst the company has ownership of any of the factories. Calculate: (a) the net present value of the project assuming an interest rate of 4% pa effective the discounted payback period the accumulated profit on the day that the last factory is sold (b) (c)

Answer 1

	Year	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Year 11	Year 12	Year 13	Year 14
		1	2	3	4	5	6	7	8	9	10	11	12	13	14
	Cost of buying factory	-500000	-500000	-500000	-500000	-500000
	Cost of refurbishment	-20000	-20000	-20000	-20000	-20000
	Rent received
	Factory 1	48000	48000	48000	48000	48000	48000	48000	48000	48000	48000
	Factory 2	0	48000	48000	48000	48000	48000	48000	48000	48000	48000	48000
	Factory 3	0	0	48000	48000	48000	48000	48000	48000	48000	48000	48000	48000
	Factory 4	0	0	0	48000	48000	48000	48000	48000	48000	48000	48000	48000	48000
	Factory 5	0	0	0	0	48000	48000	48000	48000	48000	48000	48000	48000	48000	48000
	Cashflow from Selling Factory
	Factory 1	0	0	0	0	0	0	0	0	0	600000
	Factory 2	0	0	0	0	0	0	0	0	0	0	600000
	Factory 3	0	0	0	0	0	0	0	0	0	0	0	600000
	Factory 4	0	0	0	0	0	0	0	0	0	0	0	0	600000
	Factory 5	0	0	0	0	0	0	0	0	0	0	0	0	0	600000
	Manager Salary to be given	-50000	-50000	-50000	-50000	-50000	-50000	-50000	-50000	-50000	-50000	-50000	-50000	-50000	-50000
	Net Cashflows (Sum of above rows)	-522000	-474000	-426000	-378000	-330000	190000	190000	190000	190000	790000	742000	694000	646000	598000
	Discount Rate	4%
a	NPV	836096		Excel:	NPV(discount rate, series of cash flow)
				Actual Formula	NPV = F / [ (1 + i)^n ]
					PV = Present Value
					F = Future payment (cash flow)
					i = Discount rate (or interest rate)
					n = the number of periods in the future the cash flow
b	Discounted Payback Period formula = Year before the discounted payback period occurs + (Cumulative cash flow in year before recovery / Discounted cash flow in year after recovery)
	Year	1	2	3	4	5	6	7	8	9	10	11	12	13	14
	Net Cashflows	-522000	-474000	-426000	-378000	-330000	190000	190000	190000	190000	790000	742000	694000	646000	598000
	Discount Rate	4%
	NPV	-501923	-438240	-378712	-323116	-271236	150160	144384	138831	133491	533696	481989	433470	387971	345330
	Cumulative Cashflow		-940163	-816952	-701828	-594352	-121076	294544	283216	272323	667187	1015685	915459	821441	733301
	Year before the discounted payback period occurs	6
	Cumulative Cashflow in year before recovery	121076	Year 6
	Discounted Cashflow in year after recovery	144384	Year 7
	Discounted Payback Period	6.84	Year before the discounted payback period occurs + (Cumulative cash flow in year before recovery / Discounted cash flow in year after recovery)
c	Accumulated Profit on the day last factory is sold	=	sum of all net cashflows across all years
		=	2100000