Question

Bob Jensen Inc. purchased a $1,150,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 $265,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.) ulations. Round your final answer to 1 decimal place. years 1. Unadjusted payback period 2a. ARR based on initial investment 2b. ARR based on average investment 3. NPV Present value payback period Internal rate of return (IRR) Modified internal rate of return (MIRR) years % %

Answer 1

1.

Year	Cashflow ($)	Cumulative Cash Inflow($)
0	-1150000
1	265000	265000
2	265000	530000
3	265000	795000
4	265000	1060000
5	265000	1325000
6	265000	1590000
7	265000	1855000
8	265000	2120000
9	265000	2385000
10	265000	2650000
Thus, Payback Period will be between 4th and 5th Year.
Payback Period:
=4+((1325000-1150000)/265000)
4.7	Years

2.

Accounting Rate of Return

(a) Based on Initial Investment

=Average Net Income/Initial Investment

=$265000/$1150000

23.0%

(b) Based on Average Investment

=Average Net Income/Average Investment

=$265000/($1150000/2)

46.1%

3.

Year	Cashflow ($)	PV Factor @ 12%	PV
0	-1150000	1.000	-1150000
1	265000	0.893	236607.1429
2	265000	0.797	211256.3776
3	265000	0.712	188621.7657
4	265000	0.636	168412.2908
5	265000	0.567	150368.1168
6	265000	0.507	134257.2471
7	265000	0.452	119872.5421
8	265000	0.404	107029.0554
9	265000	0.361	95561.65662
10	265000	0.322	85322.9077
NPV			$3,47,309.1
NPV
$3,47,309.1
=NPV(12%,B3:B12)+B2

4.

Year	Cashflow ($)	PV Factor @ 12%	PV	Cumulative PV of Cash Inflow
0	-1150000
1	265000	0.893	236607.143	236607.143
2	265000	0.797	211256.378	447863.520
3	265000	0.712	188621.766	636485.286
4	265000	0.636	168412.291	804897.577
5	265000	0.567	150368.117	955265.694
6	265000	0.507	134257.247	1089522.941
7	265000	0.452	119872.542	1209395.483
8	265000	0.404	107029.055	1316424.538
9	265000	0.361	95561.657	1411986.195
10	265000	0.322	85322.908	1497309.103
Thus, Payback Period will be between 6th and 7th Year.
Payback Period:
=6+((1209395.483-1150000)/119872.542)
6.5	Years

5 and 6:

Year	Cashflow ($)
0	-1150000
1	265000
2	265000
3	265000
4	265000
5	265000
6	265000
7	265000
8	265000
9	265000
10	265000
5. IRR
19.0%
=IRR(B2:B12,10%)
6. MIRR
21.4%
=MIRR(B2:B12,12%,23%)

(For MIRR, Reinvestment rate is assumed to be accounting rate of return based on initial investment.)

1	Unadjusted Payback Period	4.7	Years
2a	ARR based on Initial Investment	23.0%
2b	ARR based on Average Investment	46.1%
3	NPV	$3,47,309.1
4	Present Value Payback Period	6.5	Years
5	Internal Rate of Return (IRR)	19.0%
6	Modified Internal Rate of Return (MIRR)	21.4%