Question

Bob Jensen Inc. purchased a $900,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 $207,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.)

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

formulas used :-

12 13 14 900000 Initial Investment salvage value project life (years) Annual cashflow Discount Rate Depriciation Net income Average Investment 10 207000 12% 90000 117000 450000 4.3 Pay back period ARR (INITIAL INV.) ARR (Avg. Inv.) 13.00% 26.00% Year cashflow 5.00 von WNAO Present value Cumulative PV -900000 -900000 207000 184821.4286 184821.43 207000 165019.1327 349840.56 207000 147338.5113 497179.07 207000 131552.2422 628731.31 207000 117457.3591 746188.67 207000 104872.6421 851061.32 207000 93636.28757 944697.60 207000 83603.82819 1028301.43 207000 74646.27517 1102947.71 207000 66648.45997 1169596.17 NPV PV payback period IRR MIRR $269,596.2 6.5 18.9% 15.0%

Depriciation =C14/C16

Net Income =C17-C19

Average Investment =AVERAGE(C14,C15)

Pay back period =C14/C17

ARR ( INITIAL INVESTMENT) =C20/C14

ARR ( AVERAGE INVESTMENT) =C20/C21

NPV =NPV(0.12,C32:C41)+C31

Present value Payback period =6+((900000-E37)/D38)

IRR =IRR(C31:C41,0.13)

MIRR =MIRR(C31:C41,0.12,0.12)

i hope my efforts will be fruitful to you...?