Question

Bob Jensen Inc. purchased a $1,000,000 machine to manufacture specialty taps for electrical equipment. Jensen expects to sell all it can manufacture in the next 10 years. To encourage capital investments, the government has exempted taxes on profits from new investments. This legislation is to be in effect for the foreseeable future. The machine is expected to have a 10-year useful life with no salvage value. Jensen uses straight-line depreciation. The net cash inflow is expected to be $230,000 each year for 10 years. Jensen uses a 12% discount rate in evaluating capital investments. Assume, for simplicity, that MACRS depreciation rules do not apply. Required: Using Excel (including built-in functions for NPV, IRR, and MIRR), compute the following for the above-referenced investment: 1. The payback period, under the assumption that 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) of the proposed investment under the assumption that cash inflows occur at year-end. (Do not round intermediate calculations. Round your final answer to nearest whole dollar amount.) 4. The present value payback period, in years, of the proposed investment under the assumption that cash inflows occur evenly throughout the year. (Note: because of this assumption, the present value calculations will be approximate, not exact.) To calculate present value amounts, use the appropriate factors from Appendix C, Table 1. (Do not round intermediate calculations. Round your final answer to 1 decimal place.) 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 requirement, you might want to consult either of the following two references: MIRR Function and/or IRR in Excel.)

Answer 1

	Year	cash flow	present value of cash flow = cash flow/(1+r)^n r = 12%
	0	-1000000	-1000000
	1	230000	205357.14
	2	230000	183354.59
	3	230000	163709.46
	4	230000	146169.16
	5	230000	130508.18
	6	230000	116525.16
	7	230000	104040.32
	8	230000	92893.142
	9	230000	82940.306
	10	230000	74053.844
3-	NPV = sum of present value of cash flow		299551.3
5-	IRR = Using IRR function in MS excel	irr(cell reference cash flow year 0: cell reference cash flow year 10)
6-	MIRR = Using MIRR function in MS excel	irr(cell reference cash flow year 0: cell reference cash flow year 10,12%,12%)	14.97%
1-	Payback period = initial investment/cash flow	1000000/230000	4.3
2-	ARR using average investment = (cash flow-depreciation)/(initial investment/2)	(230000-100000)/1000000	13.00%
	ARR = (cash flow-depreciation)/initial investment	(230000-100000)/(1000000/2)	26.00%
	Year	present value of cash flow = cash flow/(1+r)^n r = 12%	cumulative present value
	0	-1000000
	1	205357.1429	205357.14
	2	183354.5918	388711.73
	3	163709.457	552421.19
	4	146169.158	698590.35
	5	130508.1768	829098.53
	6	116525.1579	945623.68
	7	104040.3195	54376.32	amount to be recovered
	8	92893.14244
	9	82940.30575
	10	74053.84442
4-	discounted payback period = year before final year of recovery+(amount to be recovered/present value of cash flow in final year)	6+(54376.32/104040)	6.5