Question

Bob Jensen Inc. purchased a $480,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. Jensen uses a 8% 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	$	36,000
2		49,000
3		74,000
4		122,000
5		220,000
6		183,000
7		169,000
8		147,000
9		74,000
10		50,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 MACRS depreciation. The asset qualifies as a 5-year property. (Use Exhibit 12.4)

Required:

Compute the following for the proposed investment:

1. Its payback period (in years) 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. Its accounting (book) rate of return based on (a) the initial investment, and (b) an average investment (calculated here as a simple average of the 10 average annual book values; for each year, the average book value is the sum of the beginning-of-year and end-of-year book value, divided by two; note: the average book value for each of the last four years is $0). (Round your final answers to 1 decimal place.)

3. Its estimated net present value (NPV). Use the built-in NPV function in Excel. (Round your final answer to nearest whole dollar amount.)

4. Its internal rate of return (IRR). Use the built-in IRR function in Excel. (Round your final answer to 1 decimal place.)

5. Its modified internal rate of return (MIRR). (Round your 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.)

Answer 1

1. Payback Period = Cost of Investment/Annual Net Cash Flow

In the above example, Cost of Investment = $ 480,000

To calculate annual net cash flow, we have been told to assume that the cash flow throughtout the year is constant. Hence to calculate, annual net cash flow, We will first add all the cash flows mentioned in the table:

36,000+49,000+74,000+122,000+220,000+183,000+169,000+147,000+74,000+50,000 = $1,124,000

Now we will divide above ampount by total number of years:

1124000/10 = $112,400

Hence Pre tax annual cash flow = $ 112,400 But we need to know the annual net cash flow. Hence we will subtract the tax liability of 25% from Pre tax Annual Cash flow to arrive at annual net cash flow:

Annual Net Cash Flow = 112400*(1-25%) = $84,300

Now, Payback Period = $480,000/$84,300 = 5.7 Years

2. Accounting Rate of Return = Average Accounting Profit/Average Investment

To calculate average accounting profit, first we need to calculate the depreciation.

Depreciation = $ 480,000 / 10 years = $ 48,000

Hence Average accounting Profit = Average annual net cash flow - Depreciation = $ 84,300 - $ 48,000 = $ 36,300

Accounting Rate of Return = $ 36,300 / $480,000 = 7.6%

3. NPV ( Using Excel):

In this calculation, we will not use the depreciation because depreciation is a non cash expense.

For NPV Calculation, we will use discount rate of 8% as mentioned in the problem. and since this discount rate is pre tax, we will use pre tax cash flows. Now we have to use the NPV function in Excel where period = 10 years, R = 8% , Value 1 = -$480,000 (Since it is an investment, it is an outflow)

Value 2 to Value 11 = Positive cash inflows given in the table

So, after applying this in excel, the NPV = $228,719

4. IRR (Using Excel) :

For IRR Calculation, We have to put in the IRR Function in Excel and Input the values in the columns 1 to 11 with the first value being -$480,000 and the cash inflow table given in the table.

After applying this, the IRR = 16.8%

5. MIRR (Using Excel) :

MIRR is IRR which is modified to include the difference between re-investment rate and investment return. But in the current example, the re-investment rate and return is not given, so calculating MIRR without these two components is not possible.