Question

One year? ago, your company purchased a machine used in manufacturing for $95,000. You have learned that a new machine is available that offers many advantages and that you can purchase it for $140,000 today. The CCA rate applicable to both machines is 20%?; neither machine will have any? long-term salvage value. You expect that the new machine will produce earnings before? interest, taxes,? depreciation, and amortization ?(EBITDA) of $40,000 per year for the next ten years. The current machine is expected to produce EBITDA of$20,000 per year. All other expenses of the two machines are identical. The market value today of the current machine is $50,000. Your? company's tax rate is 45%?, and the opportunity cost of capital for this type of equipment is 10%. Should your company replace its? year-old machine?

what is the NPV of the replacement?

Answer 1

Formula sheet

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

2

3

Input Data

4

Old Machine

New Machine

5

Purchase Cost

95000

140000

6

EBITDA per year

20000

40000

7

8

Salvage Value

0

0

9

Remaining Useful life

9

10

(Assuming life of old machine also 10 years)

10

Depreciation

CCA 20%

CCA 20%

11

Current Market Value

50000

12

Tax Rate

0.45

13

Cost of capital

0.1

14

15

16

To determine the NPV, incremental cash flow from the replacement needs to be calculated.

17

To calculate the incremental cash flow, cash flow for existing and new Machine needs to be calculated.

18

19

Calculation of Cash flow of old Machine:

20

21

Equipment Cost

=D5

22

Life of Machine

10

years

23

Salvage value

0

24

25

CCA Rate

0.2

26

Year

-1

0

1

2

3

4

5

6

7

8

9

27

Depreciation Expense

=D28*$D$25

=E28*$D$25

=F28*$D$25

=G28*$D$25

=H28*$D$25

=I28*$D$25

=J28*$D$25

=K28*$D$25

=L28*$D$25

=M28*$D$25

28

Book Value

=D21

=D28-E27

=E28-F27

=F28-G27

=G28-H27

=H28-I27

=I28-J27

=J28-K27

=K28-L27

=L28-M27

=M28-N27

29

30

Before Tax market value

=D11

31

Book value of old Machine

=E28

32

Gain or loss on sale

=D30-D31

33

Tax Expense on gain or loss

=-D32*D12

34

Net Proceed from sale of old Machine

=D30+D33

35

36

Cash flow for old Machine:

37

Operating cash flow needs to be calculated using the following formula:

38

Operating Cash Flow = EBIT*(1-T)+Depreciation

39

Free cash flow can be calculated using following equation:

40

Free Cash Flow = Operating Cash Flow - Capital Expenditures - Change in working capital

41

42

Year

0

=D42+1

=E42+1

=F42+1

=G42+1

=H42+1

=I42+1

=J42+1

=K42+1

=L42+1

43

Opportunity cost

=-D34

44

EBITDA

=$D$6

=$D$6

=$D$6

=$D$6

=$D$6

=$D$6

=$D$6

=$D$6

=$D$6

45

Depreciation Expense

=-F27

=-G27

=-H27

=-I27

=-J27

=-K27

=-L27

=-M27

=-N27

46

EBIT

=SUM(E44:E45)

=SUM(F44:F45)

=SUM(G44:G45)

=SUM(H44:H45)

=SUM(I44:I45)

=SUM(J44:J45)

=SUM(K44:K45)

=SUM(L44:L45)

=SUM(M44:M45)

47

Tax expense

=-E46*$D$12

=-F46*$D$12

=-G46*$D$12

=-H46*$D$12

=-I46*$D$12

=-J46*$D$12

=-K46*$D$12

=-L46*$D$12

=-M46*$D$12

48

EBIT*(1-T)

=SUM(E46:E47)

=SUM(F46:F47)

=SUM(G46:G47)

=SUM(H46:H47)

=SUM(I46:I47)

=SUM(J46:J47)

=SUM(K46:K47)

=SUM(L46:L47)

=SUM(M46:M47)

49

Add Depreciation

=-E45

=-F45

=-G45

=-H45

=-I45

=-J45

=-K45

=-L45

=-M45

50

Operating Cash Flow

=E48+E49

=F48+F49

=G48+G49

=H48+H49

=I48+I49

=J48+J49

=K48+K49

=L48+L49

=M48+M49

51

Free Cash flow for old Machine

=D43

=SUM(E50:E50)

=SUM(F50:F50)

=SUM(G50:G50)

=SUM(H50:H50)

=SUM(I50:I50)

=SUM(J50:J50)

=SUM(K50:K50)

=SUM(L50:L50)

=SUM(M50:M50)

52

53

54

55

Calculation of Cash flow of new Machine:

56

Depreciation schedule of the new Machine can be written as follows:

57

Equipment Cost

=E5

58

Life of Machine

10

years

59

Salvage value

0

60

61

CCA Rate

0.2

62

Year

0

1

2

3

4

5

6

7

8

9

10

63

Depreciation Expense

=D64*$D$25

=E64*$D$25

=F64*$D$25

=G64*$D$25

=H64*$D$25

=I64*$D$25

=J64*$D$25

=K64*$D$25

=L64*$D$25

=M64*$D$25

64

Book Value

=D57

=D64-E63

=E64-F63

=F64-G63

=G64-H63

=H64-I63

=I64-J63

=J64-K63

=K64-L63

=L64-M63

=M64-N63

65

66

Cash Flow of the machine can be calculated as follows:

67

Year

0

=D67+1

=E67+1

=F67+1

=G67+1

=H67+1

=I67+1

=J67+1

=K67+1

=L67+1

=M67+1

68

Investment

=-D57

69

EBITDA

=$E$6

=$E$6

=$E$6

=$E$6

=$E$6

=$E$6

=$E$6

=$E$6

=$E$6

=$E$6

70

Depreciation Expense

=-E63

=-F63

=-G63

=-H63

=-I63

=-J63

=-K63

=-L63

=-M63

=-N63

71

EBIT

=E69+E70

=F69+F70

=G69+G70

=H69+H70

=I69+I70

=J69+J70

=K69+K70

=L69+L70

=M69+M70

=N69+N70

72

Tax expense

=-E71*$D$12

=-F71*$D$12

=-G71*$D$12

=-H71*$D$12

=-I71*$D$12

=-J71*$D$12

=-K71*$D$12

=-L71*$D$12

=-M71*$D$12

=-N71*$D$12

73

EBIT*(1-T)

=E71+E72

=F71+F72

=G71+G72

=H71+H72

=I71+I72

=J71+J72

=K71+K72

=L71+L72

=M71+M72

=N71+N72

74

Add Depreciation

=-E70

=-F70

=-G70

=-H70

=-I70

=-J70

=-K70

=-L70

=-M70

=-N70

75

Operating Cash Flow

=E73+E74

=F73+F74

=G73+G74

=H73+H74

=I73+I74

=J73+J74

=K73+K74

=L73+L74

=M73+M74

=N73+N74

76

Free Cash flow for old Machine

=D68

=E75

=F75

=G75

=H75

=I75

=J75

=K75

=L75

=M75

=N75

77

78

79

Calculation of incremental cash flow

80

Year

0

=D80+1

=E80+1

=F80+1

=G80+1

=H80+1

=I80+1

=J80+1

=K80+1

=L80+1

=M80+1

81

Free Cash flow for new Machine

=D76

=E76

=F76

=G76

=H76

=I76

=J76

=K76

=L76

=M76

=N76

82

Free Cash flow for old Machine

=D51

=E51

=F51

=G51

=H51

=I51

=J51

=K51

=L51

=M51

=N51

83

Incremental Cash flow

=D81-D82

=E81-E82

=F81-F82

=G81-G82

=H81-H82

=I81-I82

=J81-J82

=K81-K82

=L81-L82

=M81-M82

=N81-N82

84

85

NPV calculation:

86

NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.

87

Given the following cash flow and MARR, NPV for the project can be calculated as follows:

88

Year

0

=D88+1

=E88+1

=F88+1

=G88+1

=H88+1

=I88+1

=J88+1

=K88+1

=L88+1

=M88+1

89

Free Cash Flow (FCF)

=D83

=E83

=F83

=G83

=H83

=I83

=J83

=K83

=L83

=M83

=N83

90

MARR (i)

=D13

91

(P/F,i,n) for each year

=1/((1+$D90)^E88)

=1/((1+$D90)^F88)

=1/((1+$D90)^G88)

=1/((1+$D90)^H88)

=1/((1+$D90)^I88)

=1/((1+$D90)^J88)

=1/((1+$D90)^K88)

=1/((1+$D90)^L88)

=1/((1+$D90)^M88)

=1/((1+$D90)^N88)

92

Present Value of cash flows = FCF*(P/F,i,n)

=E89*E91

=F89*F91

=G89*G91

=H89*H91

=I89*I91

=J89*J91

=K89*K91

=L89*L91

=M89*M91

=N89*N91

93

Present value if future cash flows

=SUM(E92:N92)

=SUM(E92:N92)

94

95

NPV for Project

=Present value fo future cash flows - Initial investment

96

=D93+D89

=D93+D89

97

98

Hence NPV of the Project is

=D96

99

Since NPV of incremental cash flow due to replacement is positive,

100

therefore the machine should be replaced.

101