Question

Consider the following costs and benefits associated with two treatments, A and B. Costs and benefits are in dollars

Costs

Treatment	Yr 1	Yr2	Yr3
A	100	700	400
B	400	300	200

Benefits

Treatment	Yr 1	Yr2	Yr3
A	300	400	900
B	500	600	100

A.        Assuming a discount rate of 5% and no discounting applied in Yr 1, calculate the present value of net benefits for each treatment.

Ans.

B.        If the treatments are stopped after Yr 2, which treatment do you prefer?  Why?

C.        Assume a really large discount rate-- 100%.  Calculate the present value of net benefits (use all three years and don't discount for the first year)

D.        How does the higher discount rate affect the net present value?  Does it affect which treatment you prefer?

Answer 1

Bock1 Micrasaft Excel Hcme Insert Page Laynin Famulas Data Revew Viw Devploper E AitoSum Ci Caibri A A Wrap Text Normal 1 General Bad Goud cepy FIL Calculation Sort Find & Fiter Select- Concitional Foemat as Neutral Formatting Table Check Cell Insert Delete Format Paste Merge &Center % A B Fcrmat Paintor Clear Cel Clipbcard Alignment Number Font Styles Edting Calculation C B31 A. C D G H M K 2. COST BENEFIT 4 Treatment Treatment year2 year 1 year 2 year 1 year 3 year 3 5 A 100 700 400 A 300 400 900 6 400 300 200 500 600 100 7 8 10 Calculation A 11 12 Benefit Cost discount rate 13 5% 14 Benefits NPV A 367.80 1497.28 Treatment A Treatment B 15 NPV B 1162.13 295.01 16 17 18 Cost NPV A 1129.48 19 NPV B 867.12 20 Sheetl Sheet2 Sheet3 4 Ready 1556 е в 1954 ENG 29-10-2019 OType here to search

Part A of the above question has been on the calculation A of the excel. Given that discount rate of 5% and no discounting done on the first year, net present value of benefits can be done as as seen the image above.

Net present values of benefits and cost of both treatment has been done separately. To find net present benefit of treatment A and B, i have used the NPV function of the excel. Keep in mind that we are not discounting year 1 of benefits and cost. So what we are doing is discounting benefits and cost to year 1. i.e discounting year 2 and 3 rates to year 1.

Net present value of treatment A (NPV A) = J5+NPV(C13,K5:L5)

= 1497.28

Net present value of treatment B (NPV B) = J6+NPV(C13,K6:L6)

= 1162.13

In the above calculation keep in mind that we need to sum present values of benefits of year 2 and 3 with benefits of year 1 to find correct answer. I have done the same in the above equations. As we can see from the calcualtions, NPV of A is greater than NPV of B. We need to choose treatment with highest NPV. Therefore treatment offer better value.

For deeper understanding, I have found NPV values of cost too.

NPV cost treatment A = D5+NPV(C13,E5:F5)

= 1129.48

NPV cost treatment B = D6+NPV(C13,E6:F6)

= 867.12

While doing NPV cost of treatments keep in mind the concepts of discounting year 2 and 3 rates to year 1, as discussed in NPV calculation of benefits. As you can see NPV of cost of A is greater than B. Does it mean treatment B is better? To find that out, subtract cost from benefits of each treatment, as shown in the imgae

Net benefits treatment A = NPV of benefit - NPV of cost

= C14-C18

= 1497.28 - 1129.48

= 367.80

Net benefits of treatment B = NPV of benefits - NPV of cost

= C15-C19

= 1162.13 - 867.12

= 295.01

Therefore treatment A offer better value for money than B.

Part B

Bock1 Micrasaft Fxcel Ное Insert Page Laynin Famulas Data Revew Viw Devploper E AitoSum Caibri A A Wrap Text Normal 1 General Bad Goud Cepy FIL Calculation Concitional Foemat as Neutral Formatting Table Check Cell Insert Delete Format Sort & Find & Fiter Selert Paste в г и Merge & Center % A Fcrmat Painter Clear Clipbcard Fom Alignment Number Styles Cels Edting A39 A. В C D Е G Н L M K 20 21 22 Calculation B Benefit cost 23 24 Benefits NPV A 680.95 Treatment A -85.71 1071.43 25 NPV B Treatment B 385.71 26 NPV A 27 Cost 766.67 NPV B 28 685.71 29 30 31 Calculation C 32 discount rate 100% 33 725.00 34 Benefits NPV A 35 NPV B 825.00 36 37 38 39 Sheet1 Sheet2 Sheet,3 Ready 1556 He 1954 O Type here to search 4ENG 29-10-2019

Calculations regarding part B of the question is done on Calculation B on the image.

The question says, treatments are stopped after year 2, which means we have consider cost and benefits till year 2 only. We can avoid year 3 for this part of the question. i.e we need to discount only year 2 cost and benefits to year 1. For this i have calculated NPV of both cost and benefits of treatment A and B. Using same discount rate 5%.

NPV of benefits treatment A = J5+NPV(C13:K5)

= 680.95

NPV benefits treatment B = J6+NPV(C13+K6)

= 1071.43

NPV cost of treatment A = D5+NPV(C13,E5)

= 766.67

NPV cost of treatment B = D6+NPV(C13,E6)

= 685.71

One this is done subtract cost from benefit to reach a final decision.

Benefit - cost treatment A = NPV benefit - NPV cost

= 680.95 - 766.67

= -85.71

Benefit - cost treatment B = NPV benefit - NPV cost

= 1071.43 - 685.71

= 385.71

Treatment A give negative benefits (cost is more than benefits) Treatment B gives positive return of 385.71, so we choose Treatment B. (benefit is more than the cost incurred).

Part C.

Bock1 Micrasaft Fxcel Ное Insert Page Laynin Famulas Data Revew Viw Devploper E AitoSum Caibri A A Wrap Text Normal 1 General Bad Goud Cepy FIL Calculation Concitional Foemat as Neutral Formatting Table Check Cell Insert Delete Format Sort & Find & Fiter Selert Paste в г и Merge & Center % A Fcrmat Painter Clear Clipbcard Fom Alignment Number Styles Cels Edting A39 A. В C D Е G Н L M K 20 21 22 Calculation B Benefit cost 23 24 Benefits NPV A 680.95 Treatment A -85.71 1071.43 25 NPV B Treatment B 385.71 26 NPV A 27 Cost 766.67 NPV B 28 685.71 29 30 31 Calculation C 32 discount rate 100% 33 725.00 34 Benefits NPV A 35 NPV B 825.00 36 37 38 39 Sheet1 Sheet2 Sheet,3 Ready 1556 He 1954 O Type here to search 4ENG 29-10-2019

Solution to part c of the problem has been shown in calculation c in the above image.

The question says we now use discount rate of 100%. Keep in mind the concepts i said in part A of the question, find Net present value of benefit can be done as

NPV benefit Treatment A = J5+NPV(C33,K5:L5)

= 725

NPV benefit treatment B = J6+NPV(C33,K6:L6)

= 825

Part D

There exist inverse relation between NPV and discount rate. Higher the discount rate lower will be the NPV and vise versa.

Higher the discount rate, lower is amount i have to invest today to get same amount that i could get by investing more at lower interest rate.

Higher discount rate have impact on our decision making on the treatment we prefer. At 100% discount rate

NPV of benefit of treatment A = 725

NPV of benefit of treatment B = 825

Now look at benefit of treatment A and B at 5% discount rate

NPV benefit treatment A = 1497.28

NPV benefit treatment B = 1162.13

As we can see at higher discount rate treatment B offers better value for money than A. When discounting rate fall, present value of future benefits will go up (time value of money comes down).