Question

Bob Jensen Inc. purchased a $700,000 machine to manufacture specialty taps for electrical equipment. Jensen expects to sell all it can manufacture in the next 10 years. The machine is expected to have a 10-year useful life with no salvage value. Jensen uses straight-line depreciation. Jensen uses a 10% discount rate in evaluating capital investments, the investment is subject to taxes, and the projected pretax operating cash inflows are as follows: Year Pretax Cash Inflow 1 $ 70,000 2 86,000 3 129,000 4 215,000 5 258,000 6 323,000 7 291,000 8 259,000 9 130,000 10 87,000 Jensen has been paying 25% for combined federal, state, and local income taxes, a rate that is not expected to change during the period of this investment. The firm uses straight-line depreciation. Assume, for simplicity, that MACRS depreciation rules do not apply. Required: Using Excel, compute the following for the proposed investment: 1. The payback period, under the assumption that the cash inflows occur evenly throughout the year. (Do not round intermediate calculations. Round your final answer to 1 decimal place.) 2. The accounting (book) rate of return based on (a) initial investment, and (b) average investment. (Round your final answers to 1 decimal place.) 3. The net present value (NPV). (Do not round intermediate calculations. Round your final answer to nearest whole dollar amount.) 4. The present value payback period of the proposed investment under the assumption that the cash inflows occur evenly throughout the year. (Note: use the formula at the bottom of Appendix C, Table 1 to calculate present value factors.) (Do not round intermediate calculations. Round your final answer to 2 decimal places.) 5. The internal rate of return (IRR). (Do not round intermediate calculations. Round your final answer to 1 decimal place.) 6. The modified internal rate of return (MIRR). (Do not round intermediate calculations. Round your final answer to 1 decimal place.) (In conjunction with this question, you might want to consult either of the following two references: MIRR Function and/or IRR in Excel.) 1. Unadjusted payback period years 2a. ARR based on initial investment % 2b. ARR based on average investment % 3. NPV 4. Present value payback period years 5. Internal rate of return (IRR) % 6. Modified internal rate of return (MIRR) %

Answer 1

The cashflows are calculated as given below

Year	0	1	2	3	4	5	6	7	8	9	10
Pretax operating cashflows		70000	86000	129000	215000	258000	323000	291000	259000	130000	87000
Depreciation		-70000	-70000	-70000	-70000	-70000	-70000	-70000	-70000	-70000	-70000
EBT		0	16000	59000	145000	188000	253000	221000	189000	60000	17000
Less Tax @25%		0	4000	14750	36250	47000	63250	55250	47250	15000	4250
PAT		0	12000	44250	108750	141000	189750	165750	141750	45000	12750
Add Depreciation		70000	70000	70000	70000	70000	70000	70000	70000	70000	70000
Capital Cost	-700000
Cashflows	-700000	70000	82000	114250	178750	211000	259750	235750	211750	115000	82750
Cumulative Cashflow	-700000	-630000	-548000	-433750	-255000	-44000	215750	451500	663250	778250	861000
Discounted cashflows	-700000	63636.364	67768.595	85837.716	122088.66	131014.3992	146622.1033	120977	98782.93776	48771.22611	31903.7072
Cumulative Discounted cashflows	-700000	-636363.64	-568595.04	-482757.3253	-360668.67	-229654.271	-83032.1677	37944.86	136727.7965	185499.0226	217402.7298

1) As the cumulative cashflows turn positive in year 6

Payback period = 5+ 44000/259750 = 5.1694 years or 5.2 years

2)

a) Accounting rate of return = Average PAT/ Initial Investment

Average PAT = (0+12000+44250+108750+141000+189750+165750+141750+45000+12750)/10 = $86100

Accounting rate of return (ARR) = 86100/700000= 0.1230 or 12.3%

b) Average Investment = 1/2* (Book value at beginning - book value at end) = 1/2*(700000+0) = 350000

So, Accounting rate of return (ARR) = 86100/350000= 0.2460 or 24.6%

3) NPV = -700000+ 70000/1.1+82000/1.1^2+114250/1.1^3+178750/1.1^4+....+82750/1.1^10

= 217402.73 or $217403

4)

As the cumulative discounted cashflows turn positive in year 7

Payback period = 6+ 83032/120977 = 6.6863 years or 6.69 years