Reference no: EM131018274

Please give this task to a good experts who well known about topics and Spreadsheet Analysis. Because this finance solution is totally based on Excel calculation.

Project for Estimating the Term Structure

Take the bond price data given for your UIN below on 50 bonds. Each bond has a face value of $100 and pays the given coupon semi-annually starting at the next coupon payment dates as given. Follow the steps discussed in class to estimate the term structures of discount factors, spot interest rates, and forward interest rates. Coupon is the rate in per cent per year applied to the face value of a bond to determine the annual coupon in dollars, half of which is paid every six months on coupon payment dates. For example, Coupon of 10% for a bond with a face value of $100 will result in $10 in annual coupon and a payment of $5 on each coupon payment date every half year.

Answer the following questions:

1-50: In the simplified bond price equation the coefficients of parameter "a" for the 50 bonds.
51-100: In the simplified bond price equation the coefficients of parameter "b" for the 50 bonds.
101-150: In the simplified bond price equation the coefficients of parameter "c" for the 50 bonds.
151-200: In the simplified bond price equation the left side number for the 50 bonds.
201: Estimated value of "a."
202: Estimated value of "b."
203: Estimated value of "c."

To plot, take 100 values of the term (x-axis) at intervals of .25 year starting at time 0. Then produce the 100 values (y-axis) of discount factor function, forward rate and spot rate functions for the plots. Answer the following:

204-303: Discount factor function values for the 100 different terms .25, .50,....,25.00.
304-403: Forward rate function values for the 100 different terms .25, .50,....,25.00.
404-503: Spot rate function values for the 100 different terms .25, .50,....,25.00.

The project grade will be based on the accuracy of your answers for each question receiving equal weight. Answers without any verbal description are required to be submitted in a two-column spread-sheet with the first column showing the question number and the second column your answers.

For UIN 651382511 this is my data

Bond     QSP       NextCoup  CouponYearsMat

 1       109.44    0.204      15.20      0.704

 2       110.01    0.216      15.80      1.216

 3       110.58    0.228      16.40      1.728

 4       111.15    0.240      17.00      2.240

 5       111.72    0.252      17.60      2.752

 6       112.29    0.264      18.20      3.264

 7       112.86    0.276      18.80      3.776

 8       113.43    0.288      19.40      4.288

 9       114.00    0.300      20.00      4.800

10       114.57    0.312      20.60      5.312

11       115.14    0.324      21.20      5.824

12       115.71    0.336      21.80      6.336

13       116.28    0.348      22.40      6.848

14       116.85    0.360      23.00      7.360

15       117.42    0.372      23.60      7.872

16       117.99    0.384      24.20      8.384

17       118.56    0.396      24.80      8.896

18       119.13    0.408      25.40      9.408

19       119.70    0.420      26.00      9.920

20       120.27    0.432      26.60     10.432

21       120.84    0.444      27.20     10.944

22       121.41    0.450      27.80     11.450

23       121.98    0.450      28.40     11.950

24       122.55    0.450      29.00     12.450

25       123.12    0.450      29.60     12.950

26       123.69    0.450      30.20     13.450

27       124.26    0.450      30.80     13.950

28       124.83    0.450      31.40     14.450

29       125.40    0.450      32.00     14.950

30       125.97    0.450      32.60     15.450

31       126.54    0.450      33.20     15.950

32       127.11    0.450      33.80     16.450

33       127.68    0.450      34.40     16.950

34       128.25    0.450      35.00     17.450

35       128.82    0.450      35.60     17.950

36       129.39    0.450      36.20     18.450

37       129.96    0.450      36.80     18.950

38       130.53    0.450      37.40     19.450

39       131.10    0.450      38.00     19.950

40       131.67    0.450      38.60     20.450

41       132.24    0.450      39.20     20.950

42       132.81    0.450      39.80     21.450

43       133.38    0.450      40.40     21.950

44       133.95    0.450      41.00     22.450

45       134.52    0.450      41.60     22.950

46       135.09    0.450      42.20     23.450

47       135.66    0.450      42.80     23.950

48       136.23    0.450      43.40     24.450

49       136.80    0.450      44.00     24.950

50       137.37    0.450      44.60     25.450

Normal Completion

American Option Trading Project

1. Goal of project: Estimation of fair value of an American option, comparison of this value with the market price of the option, decision to trade (buy or sell or sell-short), and closure of the option after a holding period of at least one day to determine profits. There should be at least 30 option trades and a project report in the format given below.

2. Data: Take following data generated for 15 calls and 15 puts with strike prices, rates of interest and years to maturity, and market prices of options on the opening day or trade day (first day of holding period) and the market prices of the options on the end of the closing period. Following these data are 32 prices of 15 underlying stocks on which the 15 pairs of calls and puts are written. These prices are prior to the trading date with the 32nd stock price (on row 32) as the price as of the close of the day before the trading date and the 1st stock price (on first row) as the price as of 32 days prior to the trading date. The data needed to calculate fair value of an option include the following:

i) Strike price. There are 15 strike prices for each of the 15 pairs of calls and puts.

ii) Years to maturity (Mat), the same for the call and put written on the same stock.

iii) Yield to maturity (Rate) on a T-bill or any other zero-coupon Treasury bond with a date of maturity close to maturity of the option. 15 such interest rates are given.

iv) Market prices of the options on the day of opening (Call0, Put0) and on closing (CallM and PutM) of trades.

v) Daily stock prices (the 32 prices given). The 32nd stock price is as of close prior to trading day and the 1st stock price is as of 32 days prior to trading day.

vi) The stock goes ex-dividend every quarter starting at 0.25 years from trading date over the life of the option. The amount of quarterly dividend is 5% of the (32nd) stock price just prior to the trading date.

vii) An estimate of annual stock volatility (standard deviation of stock's 31 daily continuously compounded rate of return) based on 32 daily stock prices including and prior to trading day, using the procedure discussed in class. The annual volatility is times the estimated standard deviation obtained from daily stock prices, because there are approximately 252 trading days in a year. The 32nd day stock price is ex-dividend price.

3. Method of Calculating Fair Option Value: Follow the step-by-step procedure used in class to determine fair value of each option using a 5-step binomial tree.

4. Trading, holding, and closing options: Compare the fair price of an option (obtained by using all information as of trading day) with the market price of the option on that day to determine the trade (buy or sell). Note that the trade is actually for the open on the following day, based on information as of close of the trading day. Ideally, you should base your trade on opening market price on the trading day. Decide a trade for each of the chosen 30 options. Hold your trades over the holding period and close the trades at the end of the holding period by selling the options bought and by buying the options sold-short.

5. Profits: Calculate profits from trading, equal to selling market price of an option minus buying market price of the same option.

6. Project Report: The project grade will be based on the accuracy of the reported answers, required to be submitted in a spread sheet of two columns with the question numbers in the first column and answers in the second column attached to e-mail before the due date.
It is required that you submit all workings attached to your email submission.

Answer the following questions for each option:

1 - 15: Annualized stock volatility (standard deviation of continuously compounded rate of return) for the 15 stocks.
16 - 30 Fair values of 15 calls on 15 stocks.
31 - 45 Fair values of 15 puts on 15 stocks.
46 - 60 Trades for calls (write 1 for buy and 0 for short-sell).
61 - 75 Trades for puts (write 1 for buy and 0 for short-sell).
76 - 90 Profits from 15 call trades.
91 - 105 Profits from 15 put trades.
106 Total profit from all trades.

7. Individual Data

For UIN 651382511

Stock    Strike    Mat       Rate      Call0     Put0      CallMPutM

 1        34.50    0.300     0.1970     15.45    126.98      2.41     19.84

 2        35.50    0.350     0.1990     93.80     81.52     14.66     12.74

 3        36.50    0.400     0.2010    153.66     73.86     24.01     11.54

 4        37.50    0.450     0.2030     14.47    158.91      2.26     24.83

 5        38.50    0.500     0.2050    102.31    109.73     15.99     17.14

 6        39.50    0.550     0.2070    173.75     99.08     27.15     15.48

 7        40.50    0.600     0.2090     61.83    145.43      9.66     22.72

 8        41.50    0.650     0.2110      6.42    201.46      1.00     31.48

 9        42.50    0.700     0.2130     23.86    185.37      3.73     28.96

10        43.50    0.750     0.2150    118.38    148.29     18.50     23.17

11        44.50    0.800     0.2170    194.23    138.56     30.35     21.65

12        45.50    0.850     0.2190    180.38    148.98     28.18     23.28

13        46.50    0.900     0.2210    389.64    127.95     60.88     19.99

14        47.50    0.950     0.2230     96.10    182.37     15.01     28.50

15        48.50    1.000     0.2250    111.49    184.24     17.42     28.79

32 Prices of 15 stocks in 15 columns

 32.35  32.96  33.48  31.80  40.86  39.10  41.47  35.05  48.90  42.52  36.01  45.81  46.56  46.05  50.47

 42.62  42.02  39.97  29.23  29.25  35.93  56.90  36.47  42.43  39.91  40.57  40.65  38.87  44.55  55.55

 40.18  35.02  45.92  51.73  38.34  27.62  72.81  44.10  63.42  43.19  40.20  46.70  42.09  58.12  38.57

 38.07  37.15  39.87  31.04  35.34  27.03  39.77  51.81  42.78  36.02  33.89  61.62  50.13  39.71  47.82

 32.70  36.35  25.36  55.56  35.60  47.31  55.75  43.46  33.84  27.22  42.77  53.59 122.37  48.34  75.64

 20.81  32.91  44.83  34.42  25.97  25.92  34.21  40.98  42.67  40.42  37.85  39.68  34.31  53.02  30.74

 35.40  34.40  20.34  21.96  39.43  23.72  49.93  40.48  30.76  41.58  50.19  35.29  36.63  30.54  42.45

 17.38  62.03  24.66  43.92  57.57  38.95  51.47  28.71  21.18  46.42  28.66  32.67  63.84  76.40  39.51

 33.88  44.03  47.11  16.54  19.98  28.16  44.01  71.41  62.84  59.20  50.76  53.80  32.38  43.61  37.37

 41.19  39.77  51.93  28.28  38.48  36.43  30.08  19.83  33.59  68.82  33.02  57.02  51.39 160.89  28.34

 31.78  37.99  21.73  50.90  46.00  49.75  33.86  24.50  44.51  32.22  29.74  35.23  92.27  17.11  62.97

 38.66  52.41  41.81  82.68  48.90  29.42  47.80  55.44  59.82  78.61  81.00  15.48  38.17  45.80  23.29

 30.10  23.99  21.63  35.41  31.49  40.90  46.31  45.85  35.39  18.55  17.82  22.01  70.70  26.84  24.69

 32.39  44.32  33.53  44.85  45.32  30.23  35.22  61.57  74.88  29.22  51.31  64.45  48.26  39.41  53.48

 36.32  27.01  28.60  22.17  43.86  18.66  52.38  43.54  38.84  61.44  23.00  79.87  20.44  20.80  38.92

 44.99  55.14  37.43  30.16  31.13  35.29  29.31  44.56  18.64  70.18  25.81  20.79  62.11  95.42  34.54

 17.15  17.29  38.88  16.58  31.25  33.64  32.96  61.94  54.16  68.71  28.80  15.63  41.80  24.60 100.02

 24.41  43.89  26.40  20.40  36.52  23.03  24.46  37.51  55.55  34.13 107.56  24.48  44.38  31.33  32.19

 13.26  24.20  23.42  26.72  23.26  58.05  81.64  69.24  49.47  45.70 153.35  46.27  45.98  21.11  54.55

 15.05  33.75  15.14  40.56  70.82  76.62  57.16  21.13  43.04  21.18  32.88  57.70  40.88  21.08  15.83

 19.94  15.47  56.23  12.56  45.53  62.15  17.41  12.60  33.11  16.95  40.10  12.45  37.54  32.57  67.48

 22.69  34.11  17.40  26.27  10.81  42.69  31.19  17.80  97.00  60.56  39.88  24.73  75.41  43.37  39.70

 32.14  33.66  67.58  37.09  47.84  35.67  26.79  18.63  43.78  21.01  23.22 114.51  98.99  70.82  44.01

 13.67  12.84  17.25  16.59  35.60  24.98  42.30  27.21  40.66  91.67  45.04  25.06  82.69  34.67  52.28

 90.48  26.91  25.00  57.72  30.01 104.34  26.54  62.07  42.21  17.48  28.54  55.85  18.07  21.47  62.13

 36.63  20.77  35.82  17.70  47.92  23.40  13.48  38.35 113.55  24.51  45.14  27.55  88.38  39.84  51.50

 18.85  93.75  17.47  41.87  25.37  36.71  49.51  27.60  24.84  50.60  49.28  38.41  19.91  21.32 121.96

 12.67  42.14  16.89  12.69  75.42  19.73  15.37  16.73  24.74  70.99  18.10  67.74  47.64  49.06  34.99

42.68  40.88  35.59 186.02  35.63  43.61   8.47   8.52 185.82  36.33  12.88  22.24  31.19 125.32  17.01

 34.35  40.48  35.43  64.63  53.85  38.49  25.71  32.49  38.66  33.64  64.23  37.41  29.66  10.35  38.20

 43.07   9.72  39.71  35.53 108.58  38.69  53.83  41.68  18.00  36.85  40.50  20.65  18.80  22.72  19.49

 15.09  35.03  46.15  11.66  33.59  46.92  22.66   5.71  11.38  32.35  46.11  42.68  79.00  24.95  27.36

Normal Completion