Question

"The A.M.I. Company is considering installing a new process machine for the firm's manufacturing facility. The machine costs $573,000 installed, will generate additional revenue of $86,000 per year, and will save $60,000 per year in labor and material costs. The machine will be financed by a $202,000 bank loan repayable in three equal annual installments with a 11% interest rate. The machine will be depreciated using seven-year MACRS. The useful life of the machine is 10 years when the machine will be sold for $29,000. The marginal tax rate is 40%. Compute the IRR of the investment. Enter your answer as a percentage between 0 and 100."

SHOW ALL WORK OR EXPLAIN EXCEL FORMULAS

Answer 1

IRR is the rate at which present value of cash outflows equal to present value of cash inflows. In other words IRR is the rate at which NPV of project is zero. So if IRR>cost of capital project is acceptable and vice-versa.

IRR can be finding out by trial and error method. That is, NPV at two different discount rates must be worked out .Let NPV1 and NPV2 are obtained at two discounting rates 17% and 18% respectively,

IRR=17%+NPV1/ (NPV1-NPV2) *(18-17)

Here (18-17) is difference in discount rates

Here we can work out NPV@17% and 18%

YEAR	MACHINE COSTS	Revenue and Savings	Loan payments	TAX	Net inflow	DF@17%	PV OF INFLOW
0	-371000				-371000	1	-371000
1		146000	-82661.04	-16759.32	46579.64	0.854700855	39811.6581
2		146000	-82661.04	3959.67424	67298.63	0.730513551	49162.5643
3		146000	-82661.04	-15036.26615	48302.69	0.624370556	30158.7798
4		146000		-29772.92	116227.1	0.533650048	62024.5868
5		146000		-37932.44	108067.6	0.456111152	49290.8193
6		146000		-37955.36	108044.6	0.389838592	42119.9703
7		146000		-37932.44	108067.6	0.333195378	36007.6115
8		146000		-48177.68	97822.32	0.284782374	27858.0725
9		146000		-58400	87600	0.243403738	21322.1675
10	29000	146000		-70000	105000	0.208037383	21843.9252
		1460000	-247983.1				8600.15541

Thus NPV @17%=$8600.16

Now we can find out NPV @18%

YEAR	MACHINE COSTS	REVENUE AND SAVINGS	LOAN PAYMENT	TAX	NET INFLOW	DF @18%	PV OF INFLOW
0	-371000				-371000	1	-371000
1		146000	-82661.04	-16759.32	46579.64	0.847457627	39474.2712
2		146000	-82661.04	3959.67424	67298.63	0.71818443	48332.8313
3		146000	-82661.04	-15036.26615	48302.69	0.608630873	29398.5107
4		146000		-29772.92	116227.1	0.515788875	59948.6349
5		146000		-37932.44	108067.6	0.437109216	47237.3265
6		146000		-37955.36	108044.6	0.370431539	40023.1423
7		146000		-37932.44	108067.6	0.313925033	33925.1124
8		146000		-48177.68	97822.32	0.266038164	26024.4704
9		146000		-58400	87600	0.225456071	19749.9518
10	29000	146000		-70000	105000	0.191064467	20061.769
		1460000	-247983.1				(6,823.98)

IRR=17%+8600.16/(8600.16+6823.98)*(18-17)=17.55%

Working for interest and loan payment,depreciation and tax are given below.

Workings for Interest and loan payment

To find installment amount we have to find PV of annuity @11% for 3 years

YEAR	DF @11% workings	DF@11%
1	1/1.11	0.900900901
2	1/1.11/1.11	0.811622433
3	1/1.11/1.11	0.731191381
TOTAL	0	2.443714715

Installment=loan amount/PV of annuity =$202,000/2.4437=$82,661.04

Loan repayment schedule is given below:-

REPAYMENT AND INTEREST CALCULATION
YEAR	Starting loan amount	INSTALLMENT	INTEREST	PRINCIPAL PORTION	End .loan amount
1	202000	82661.04	22220	60441.04	141559
2	141558.96	82661.04	15571.486	67089.5544	74469.41
3	74469.4056	82661.04	8191.6346	74469.40538	0.000216

Interest is calculated @11% of starting principal amount. Balance amount out of $82,661.04 is principal portion. End loan amount is =starting loan amount–principal portion of installment.

Calculation of tax

CORPORTATE TAX CALCULATION
						Net benefits for Tax
YEAR	Additional revenue (A)	Cost savings(B)	capital gain(C )	depreciation(D )	Interest (I)	(A+B+C-D-I)	TAX@40%
1	86000	60000		81881.7	22220	41898.3	16759.32
2	86000	60000		140327.7	15571.49	-9899.1856	-3959.67424
3	86000	60000		100217.7	8191.635	37590.66538	15036.2662
4	86000	60000		71567.7		74432.3	29772.92
5	86000	60000		51168.9		94831.1	37932.44
6	86000	60000		51111.6		94888.4	37955.36
7	86000	60000		51168.9		94831.1	37932.44
8	86000	60000		25555.8		120444.2	48177.68
9	86000	60000				146000	58400
10	86000	60000	29000			175000	70000

Calculation of depreciation

CALCULATION OF DEPRECIATION
YEAR	MACRS 7Year rate	Depreciation=rate(%)*573,000
1	14.29	81881.7
2	24.49	140327.7
3	17.49	100217.7
4	12.49	71567.7
5	8.93	51168.9
6	8.92	51111.6
7	8.93	51168.9
8	4.46	25555.8
TOTAL	100	573000