Question

Internal Rate of Return

Mike's Bread Company is investigating the purchase of a new $45,000 delivery truck that would contain specially designed warming racks. The new truck would have a six-year useful life. It would save $5,400 per year over the present method of delivering pizzas. In addition, it would result in the sale of 1,800 more loaves of bread each year. The company realizes a contribution margin of $2 per bread.

Required:

(Ignore income taxes.)

What would be the total annual cash inflows associated with the new truck for capital budgeting purposes?

Find the internal rate of return promised by the new truck to the nearest whole percent point.

In addition to the data above, assume that due to the unique warming racks, the truck will have a $13,000 salvage value at the end of six years. Under these conditions, compute the internal rate of return to the nearest whole percentage point. (Hint: You may find it helpful to use the net present value approach; find the discount rate that will cause the net present value to be closest to zero.

Answer 1

Formula sheet

A1	B	C	D	E	F	G	H	I	J	K
2
3		Input Data
4		Initial Investment	45000
5		Annual Savings in pizza delivery	5400
6		Additional Sale of Breads	1800
7		Contribution margin per bread	2
8		Salvage Value	13000
9		Period	6	years
10
11		Free cash flow can be calculated as followed:
12		Year	0	1	2	3	4	5	6
13		Investment	=-D4
14		Savings in pizza delivery		=D5	=E14	=F14	=G14	=H14	=I14
15		Cash inflow due to additional bread sales		=$D$6*$D$7	=$D$6*$D$7	=$D$6*$D$7	=$D$6*$D$7	=$D$6*$D$7	=$D$6*$D$7
16		Salvage Value							=D8
17		Net Cash Flow	=D13+D14	=E14+E15+E16	=F14+F15+F16	=G14+G15+G16	=H14+H15+H16	=I14+I15+I16	=J14+J15+J16
18
19		Calculation of IRR for project A:
20		IRR is the rate at which NPV of the project will be zero.
21		Given the following cash flow IRR can be calculated as below:
22
23		Year	0	=D23+1	=E23+1	=F23+1	=G23+1	=H23+1	=I23+1
24		Net Cash Flow	=D17	=E17	=F17	=G17	=H17	=I17	=J17
25
26		NPV=-45000+9000/(1+IRR)^1 +9000/(1+IRR)^2+…+22,000/(1+IRR)^6
27		0=-45000+9000/(1+IRR)^1 +9000/(1+IRR)^2+…+22,000/(1+IRR)^6
28		IRR can be found using hit and trial method for above equation.
29
30		IRR can also be found using IRR function in excel as follows:
31		Year	0	=D31+1	=E31+1	=F31+1	=G31+1	=H31+1	=I31+1
32		Cash Flow	=D24	=E24	=F24	=G24	=H24	=I24	=J24
33		IRR	=IRR(D32:J32)	=IRR(D32:J32)
34
35		Hence IRR of the project is	=D33
36