Question

1 Bob Jensen Inc. purchased a $200,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 $46,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.) Unadjusted payback period 2a. ARR based on initial investment years 2b ARR based on average investment 3. İNPV 4. Present value payback period 5. Internal rate of return (IRR) 6. Modified internal rate of return (MIRR) years

Answer 1

1. Calculating Unadjusted Payback Period

Unadjusted Payback Period = Initial Investment/Annual Cash Inflow

Unadjusted Payback Period = 200000/46000

Unadjusted Payback Period = 4.35 Years

Unadjusted Payback Period = 4.4 Years (Rounded to 1 Decimal)

2.a Calculating Accounting Rate of Return (ARR) on initial Investment

Calculating Depreciation per year

Depreciation = (Cost – Residual Value)/Useful Life

Depreciation = ($200000 – 0)/10

Depreciation = $20,000

Calculating Accounting Rate of Return on Initial Investment

Accounting Rate of Return (on Initial Investment) = Average Accounting Profit/Initial Investment x 100

Accounting Rate of Return (on Initial Investment) = (46000-20000)/200000 x 100

Accounting Rate of Return (on Initial Investment) = 26000/200000 x 100

Accounting Rate of Return (on Initial Investment) = 13%

2.b Calculating Accounting Rate of Return (ARR) on Average Investment

Calculating Average Investment

Average Investment = (Initial Investment + Investment at the end of project)/2

Average Investment = (200000 + 0)/2

Average Investment = $100000

Accounting Rate of Return (on Average Investment) = Average Accounting Profit/Average Investment x 100

Accounting Rate of Return (on Average Investment) = (46000-20000) / 100000 x 100

Accounting Rate of Return (on Average Investment) = 26000/100000 x 100

Accounting Rate of Return (on Average Investment) = 26%

3 and 4. Calculating Net Present Value (NPV) and Present value Payback Period

Years	Cash Inflow	Disc. Factor @ 12%	Present Value of Inflows	Cummulative Present Value of Inflows
1	46000	0.892857	41071.43	41071.43
2	46000	0.797194	36670.92	77742.35
3	46000	0.71178	32741.89	110484.24
4	46000	0.635518	29233.83	139718.07
5	46000	0.567427	26101.64	165819.71
6	46000	0.506631	23305.03	189124.74
7	46000	0.452349	20808.06	209932.80
8	46000	0.403883	18578.63	228511.43
9	46000	0.36061	16588.06	245099.49
10	46000	0.321973	14810.77	259910.26
	Present value of Inflow		259910.3
	Less :- Initial Investment		-200000.0
	NPV		59910.3

4. Calculating Present Value Payback Period

Present Value Payback Period = 6 Years + (200000-189124.74) / (209932.80-189124.74)

Present Value Payback Period = 6 Years + 0.52 years

Present Value Payback Period = 6.52 Years

5 and 6. Calculating Internal Rate of return(IRR) and Modified Internal Rate of Return(MIRR)

Years	Cash Flow
0	($200,000.00)
1	46000
2	46000
3	46000
4	46000
5	46000
6	46000
7	46000
8	46000
9	46000
10	46000

5	IRR Formula	=IRR((B38:B48,12%)		18.94%
6	MIRR Formula	=MIRR((B38:B48),12%,12%)		14.97%