Question

3.         Find the IRR of each investment.

You currently own and operate a bar in Chicago called the Big Tap. Up until now you have only served beer and hard alcohol to your customers (no food), and all liquor served has been purchased through a distributor. Business has been good, but you have just bought and moved into a new building and are looking to further expand the bar's business. The new building you are now located in has more space than the bar's previous location and the basement of the new building is currently unfinished. You have come up with two different investment possibilities: Investment Alternative 1: Microbrewery Renovate the basement into a microbrewery and begin brewing your own beer for sale at the bar. Investment Alternative 2: Kitchen Renovate the basement into a kitchen and begin to serve a full menu of food at the bar. Information: You have estimated that both projects will require an initial investment of $100,000 for renovations and new equipment purchases. You have found a local bank that will lend 70% of the initial investment, financed over 5 years at 6% per year. You can assume that the entire initial investment will be depreciated using the straight-line method over a 10-year period. You expect both investments to generate additional revenues of $50,000 and additional expenses of $32,000 in the first year. Both the revenues and expenses directly related to the new investments are expected to grow at a rate of 2% each year.

Answer 1

Question 1
D/A	0.7
E/A	0.3
r debt	6%
r equity	40%
Tax rate	25%
WACC=	(70%*6%*(1-25%))+(30%*40%)=
	15.15%

Annual pmt. On the loan =70000/((1-1.06^-5)/0.06)
16617.75

Loan Amortisation table
	Annuity	Tow. Int.	Tow.Loan	Loan Bal.
0				70000
1	16617.75	4200	12417.75	57582.25
2	16617.75	3454.94	13162.82	44419.44
3	16617.75	2665.17	13952.58	30466.85
4	16617.75	1828.01	14789.74	15677.11
5	16617.75	940.63	15677.12	-0.0111
	83088.75	13088.74	70000.01

Question 2,3 & 4
Microbrewery
Year	Inflows	Outflows	Other cash effects	Depn.	Loan pmt.	Loan int.	Taxable income	NATCF(microbrew)
1	2	3	4	5	6	7	8=2+3+4+5+7	9=8*(1-25%)+Col.5
0		-100000					-100000	-100000
1	50000	-32000	-3000	-10000	12417.75	-4200	800	10600
2	51000	-32640	-3030	-10000	13162.82	-3454.94	1875.065	11406.30
3	52020	-33292.8	-3060.3	-10000	13952.58	-2665.17	3001.73	12251.30
4	53060.4	-33958.7	-3090.90	-10000	14789.74	-1828.01	4182.83	13137.12
5	54121.61	-34637.8	-3121.81	-10000	15677.12	-940.63	5421.34	14066.01
6	55204.04	-35330.6	-3153.03	-10000			6720.42	15040.32
7	56308.12	-36037.2	-3184.56	-10000			7086.36	15314.77
8	57434.28	-36757.9	-3216.41	-10000			7459.94	15594.95
9	58582.97	-37493.1	-3248.57	-10000			7841.30	15880.97
10	59754.63	-38243	-3281.06	-10000			8230.61	16172.96
Year	NATCF	PV Factor	PV	PV outflows	FV Factor	FV Inflows
0	-100000	1	-100000	-100000
1	10600	0.86843	9205.38		3.559389	37729.526
2	11406.3	0.75417	8602.35		3.091089	35257.887
3	12251.3	0.65495	8023.99		2.684402	32887.419
4	13137.12	0.56878	7472.13		2.331222	30625.55
5	14066.01	0.49395	6947.86		2.024509	28476.754
6	15040.32	0.42896	6451.69		1.758149	26443.126
7	15314.77	0.37252	5705.10		1.526834	23383.115
8	15594.95	0.32351	5045.13		1.325952	20678.162
9	15880.97	0.28095	4461.71		1.1515	18286.942
10	16172.96	0.24398	3945.94		1	16172.958
				-100000		269941.44
		NPV	-34138.73
		IRR	6%
		MIRR	10.44%
		MIRR=(269941.44/100000)^(1/10)-1
		10.44%

Kitchen
Year	Inflows	Outflows	Other cash effects	Depn.	Loan pmt.	Loan int.	Taxable income	NATCF(Kitchen)
1	2	3	4	5	6	7	8=2+3+4+5+7	9=8*(1-25%)+Col.5
0		-100000					-100000	-100000
1	50000	-32000	3000	-10000	12417.75	-4200	6800	15100
2	51000	-32640	3030	-10000	13162.82	-3454.94	7935.07	15951.30
3	52020	-33292.8	3060.3	-10000	13952.58	-2665.17	9122.33	16841.75
4	53060.4	-33958.7	3090.90	-10000	14789.74	-1828.01	10364.64	17773.48
5	54121.61	-34637.8	3121.81	-10000	15677.12	-940.63	11664.96	18748.72
6	55204.04	-35330.6	3153.03	-10000			13026.48	19769.86
7	56308.12	-36037.2	3184.56	-10000			13455.48	20091.61
8	57434.28	-36757.9	3216.41	-10000			13892.75	20419.56
9	58582.97	-37493.1	3248.57	-10000			14338.44	20753.83
10	59754.63	-38243	3281.06	-10000			14792.72	21094.54

Year	NATCF	PV Factor	PV	PV outflows	FV Factor	FV Inflows
0	-100000	1	-100000	-100000
1	15100	0.86843	13113.33		3.559389	53746.78
2	15951.3	0.75417	12030.07		3.091089	49306.89
3	16841.75	0.65495	11030.51		2.684402	45210.03
4	17773.48	0.56878	10109.20		2.331222	41433.92
5	18748.72	0.49395	9260.87		2.024509	37956.96
6	19769.86	0.42896	8480.47		1.758149	34758.37
7	20091.61	0.37252	7484.58		1.526834	30676.56
8	20419.56	0.32351	6605.94		1.325952	27075.36
9	20753.83	0.28095	5830.73		1.1515	23898.03
10	21094.54	0.24398	5146.72		1	21094.54
				-100000		365157.45

NPV	-10907.58
IRR	12%
MIRR	13.83%

MIRR=(365157.45/100000)^(1/10)-1
13.83%