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