a.

b.

Fill in the missing blanks on the spreadsheet. Use answers from part a to solve for part b. Include and show formulas please.

B C D E F G H K 6.75% 25 4 6 1 (3.26) (9.04) 2 (3.37) (8.93) 3 (3.49) (8.82) (3.60) (8.70) 5 (3.72) (8.58) (3.85) (8.45) 7 (3.98) (8.32) 8 (4.11) (8.19) 9 (4.25) (8.05) 10 (4.40)| (7.91) ) 17 11 (4.55) (7.76) 12 (4.70) (7.60) 13 (4.86) (7.45) 14 (5.02) (7.28) 15 (5.19) (7.11) 16 (5.37) (6.94) (5.55) 18 (5.73) (6.57) 19 (5.93) (6.38) 201 (6.13) (6.18) (6.76) Calculate PV of borrow and buy option. Also, don't forget that in this case the company can sell equipment at the end of the 25th year (residual value) which will be cash inflow. All numbers in mln.$. 1 Perods 1-40 are half-years 2 Discount rate (yearly) 3 Useful life of equipment 4 5 6 Time Period 7 Principal repayment 8 Interest expense 9 Total cash flows 10 Discounted flows 11 11 45 12 13 Time Period 14 Principal repayment 15 Interest expense 16 Total cash flows 17 Discounted flows 18 10 19 20 Time Period 21 21 Principal repayment 22 Interest expense 23 Total cash flows Discounted flows 25 26 27 Time Period 28 Principal repayment 29 Interest expense 30 Total cash flows 31 Discounted flows 32 33 34 Time Period 35 Residual value 36 Total cash flows 37 Discounted flows 21 (6.33) (5.97) 22 (6.55) (5.75) 23 (6.77) (5.53) 24 (7.00) (5.31) 25 (7.23) (5.07) 26 (7.48) (4.83) 27 (7.73) (4.57) 28 (7.99) (4.31) 29 (8.26) (4.04) 30 (8.54) (3.76) 31 (8.83) (3.48) 32 19.13) (3.18) 33 (9.43) (2.87) 34 (9.75) (2.55) 35 ( (10.08) (2.22) 36 (10.42) (1.88) 37 (10.77) (1.53) 38 (11.14) (1.17) 39 (11.51) (0.79) 40 (11.90) ( (0.40) 41 42 43 44 45 46 47 48 49 50 40.19 40.19 J K 4 9 10 8 (9.83) (10.37) (8.61) 18 19 20 (7.77) (9.48) (15.79) A B C D E F G . 1 Calculate PV of financial lease option. All numbers are from Exhibit 5 (in mln.$). Periods 1-42 are half-years 2 Discount rate (yearly) 6.75% 3 4 Time Period 0 1 2 3 5 6 7 5 Lease payments (0.20) (3.76) (7.97) (10.02) (10.32) (8.62) (10.36) 6 Discounted flows 7 8 9 Time Period 11 12 13 14 15 16 17 10 Lease payments (10.42) (9.68)| (10.44) (8.58) (11.60) (7.34) (11.47) 11 Discounted flows 12 13 14 Time Period 21 22 23 24) 25 26 27 15 Lease payments (20.22) (5.07) (15.87) (4.12) (22.81) (3.34) (23.65) 16 Discounted flows 17 18 19 Time Period 31 32 33 34 35 36 37 20 Lease payments (20.02) (6.07) (6.29) (12.29) (21.39) (6.55) (18.11) 21 Discounted flows 22 23 24 Time Period 41 42 43 44 45 46 47 25 Lease payments (6.65) (2.04) 26 Discounted flows 27 28 PV of Lease Alternative (total) 28 29 30 (1.96) (2.66) (24.06) 38 39 40 (8.86) (8.61) (13.47) 48 49 50 B C D E F G H K 6.75% 25 4 6 1 (3.26) (9.04) 2 (3.37) (8.93) 3 (3.49) (8.82) (3.60) (8.70) 5 (3.72) (8.58) (3.85) (8.45) 7 (3.98) (8.32) 8 (4.11) (8.19) 9 (4.25) (8.05) 10 (4.40)| (7.91) ) 17 11 (4.55) (7.76) 12 (4.70) (7.60) 13 (4.86) (7.45) 14 (5.02) (7.28) 15 (5.19) (7.11) 16 (5.37) (6.94) (5.55) 18 (5.73) (6.57) 19 (5.93) (6.38) 201 (6.13) (6.18) (6.76) Calculate PV of borrow and buy option. Also, don't forget that in this case the company can sell equipment at the end of the 25th year (residual value) which will be cash inflow. All numbers in mln.$. 1 Perods 1-40 are half-years 2 Discount rate (yearly) 3 Useful life of equipment 4 5 6 Time Period 7 Principal repayment 8 Interest expense 9 Total cash flows 10 Discounted flows 11 11 45 12 13 Time Period 14 Principal repayment 15 Interest expense 16 Total cash flows 17 Discounted flows 18 10 19 20 Time Period 21 21 Principal repayment 22 Interest expense 23 Total cash flows Discounted flows 25 26 27 Time Period 28 Principal repayment 29 Interest expense 30 Total cash flows 31 Discounted flows 32 33 34 Time Period 35 Residual value 36 Total cash flows 37 Discounted flows 21 (6.33) (5.97) 22 (6.55) (5.75) 23 (6.77) (5.53) 24 (7.00) (5.31) 25 (7.23) (5.07) 26 (7.48) (4.83) 27 (7.73) (4.57) 28 (7.99) (4.31) 29 (8.26) (4.04) 30 (8.54) (3.76) 31 (8.83) (3.48) 32 19.13) (3.18) 33 (9.43) (2.87) 34 (9.75) (2.55) 35 ( (10.08) (2.22) 36 (10.42) (1.88) 37 (10.77) (1.53) 38 (11.14) (1.17) 39 (11.51) (0.79) 40 (11.90) ( (0.40) 41 42 43 44 45 46 47 48 49 50 40.19 40.19 J K 4 9 10 8 (9.83) (10.37) (8.61) 18 19 20 (7.77) (9.48) (15.79) A B C D E F G . 1 Calculate PV of financial lease option. All numbers are from Exhibit 5 (in mln.$). Periods 1-42 are half-years 2 Discount rate (yearly) 6.75% 3 4 Time Period 0 1 2 3 5 6 7 5 Lease payments (0.20) (3.76) (7.97) (10.02) (10.32) (8.62) (10.36) 6 Discounted flows 7 8 9 Time Period 11 12 13 14 15 16 17 10 Lease payments (10.42) (9.68)| (10.44) (8.58) (11.60) (7.34) (11.47) 11 Discounted flows 12 13 14 Time Period 21 22 23 24) 25 26 27 15 Lease payments (20.22) (5.07) (15.87) (4.12) (22.81) (3.34) (23.65) 16 Discounted flows 17 18 19 Time Period 31 32 33 34 35 36 37 20 Lease payments (20.02) (6.07) (6.29) (12.29) (21.39) (6.55) (18.11) 21 Discounted flows 22 23 24 Time Period 41 42 43 44 45 46 47 25 Lease payments (6.65) (2.04) 26 Discounted flows 27 28 PV of Lease Alternative (total) 28 29 30 (1.96) (2.66) (24.06) 38 39 40 (8.86) (8.61) (13.47) 48 49 50