Financial Engineering 5

profileshoomoosh
Assignment5.xlsx
Home>Business & Finance homework help>Financial markets homework help>Financial Engineering 5

Student Information

You must enter the information in the "yellow" fields below before starting the assignment. Fall 2020 expected semi-annual rate 0.01313 Futures Original Modified
Name annual risk free rate 0.02626 Settlement Date Sorghum Rice Oats Soybeans Corn Alfalfa Wheat Date Sorghum Rice Oats Soybeans Corn Alfalfa Wheat
10 digit student ID # 1220885757 notional amount 14420000 Price 4/30/06 26.2 60.1 73.75 147.6519 $ 65.00 74.1 83.9916 7/31/14 41.28 94.7 116.21 232.66 102.42 116.76 132.35
Date 4.92 5/31/06 28.6 59.8 75 153.9846 $ 68.21 76 83.6583 8/31/14 45.07 94.23 118.18 242.63 107.49 119.75 131.82
Class 0.02626 5.02 1.02 6/30/06 30.6 58.2 79.375 159.6507 $ 70.71 77 84.6582 9/30/14 48.22 91.71 125.07 251.56 111.42 121.33 133.4
By downloading this assignment you agree to not share this exam with anyone. You also agree to completing this assignment without the assistance or of anyone unless you have received written approval from the instructor. You agree to not collaborate with anyone with this assignment You agree to not use another student's assignment or materials to aid you with this assignment You understand that not following these rules, or any other rules identified in the ASU Academic Integrity Policy will result in a zero grade for this assignment a violation report to the Dean's office, and likely suspension from ASU. Please teach each other the Excel functions and financial engineering functions. However, do not provide answers or assist others with their answers. Engineers must learn how to solve problems! This assignment is the property of Daniel R. McCarville and Arizona State University. It is copyright protected. 0.027573 5.17 1.03 7/31/06 32.1 56.4 80 163.6503 $ 72.50 77.7 86.3247 10/31/14 50.58 88.87 126.06 257.86 114.24 122.43 136.02
0.02895165 5.17 1 8/31/06 32.4 57.5 84.375 166.65 $ 72.50 81.9 85.6581 11/30/14 51.05 90.6 132.95 262.59 114.24 129.05 134.97
Instructions: 0.0303992325 5.01 0.97 9/30/06 33.3 56.2 81.875 172.9827 $ 75.36 87.9 86.3247 12/31/14 52.47 88.55 129.01 272.57 118.74 138.5 136.02
1. You must enter the information requested above. 0.0319191941 4.91 0.98 10/31/06 33.2 56.9 77.5 164.3169 $ 68.21 85.1 83.325 1/31/15 52.31 89.66 122.12 258.91 107.49 134.09 131.3
2. Include your entire 10 (ten) digit student ID number. 0.0335151538 4.76 0.97 11/30/06 28.2 55.9 67.5 150.9849 $ 58.57 83.3 77.3256 2/28/15 44.43 88.08 106.36 237.91 92.29 131.26 121.84
3. Do not change the questions as I have typed them. 0.0351909115 4.81 1.01 12/31/06 27.3 57.2 58.125 148.3185 $ 54.29 84.7 79.992 3/31/15 43.02 90.13 91.59 233.71 85.54 133.46 126.04
4. Fill in all yellow boxes with the requested information and/or answer. 0.0369504571 4.71 0.98 1/31/07 27.7 55.3 59.375 152.9847 $ 57.50 86 80.9919 4/30/15 43.65 87.14 93.56 241.06 90.6 135.51 127.62
5. To show your cash flow drawings, you must copy the drawings onto the Question Worksheet. 0.03879798 4.71 1 2/28/07 30.1 55.7 67.5 148.3185 $ 62.14 89.2 89.3244 5/31/15 47.43 87.77 106.36 233.71 97.92 140.55 140.75
6. Please show your Excel work in the same worksheet as the Question. 0.040737879 3/31/07 32.7 57.2 76.25 151.6515 $ 66.43 88 93.9906 6/30/15 51.53 90.13 120.15 238.96 104.67 138.66 148.1
7. Data Analysis TookPak results such as Covariance Matrices or Regression Analysis can be inserted in a new worksheet. Bushels 20000 4/30/07 35.4 56.9 71.25 159.3174 $ 70.36 89.7 95.6571 7/31/15 55.78 89.66 112.27 251.04 110.86 141.34 150.73
8. I do give some partial credit where enough detail is presented. Initial Account % 0.05 5/31/07 33.9 58.6 75.625 155.9844 $ 70.71 91 94.6572 8/31/15 53.42 92.34 119.16 245.78 111.42 143.39 149.15
Margin Maintenance 0.75 6/30/07 34.7 57.2 80 148.6518 $ 70.00 92.9 94.3239 9/30/15 54.68 90.13 126.06 234.23 110.3 146.38 148.63
Question Points Actual 7/31/07 32.9 56.6 77.5 146.3187 $ 70.00 95 95.6571 10/31/15 51.84 89.18 122.12 230.55 110.3 149.69 150.73
1a 5 0 MCCA 8/31/07 30.4 56.8 80 140.6526 $ 67.50 105 95.3238 11/30/15 47.9 89.5 126.06 221.63 106.36 165.45 150.2
1b 5 0 Option Type Stock Price IV Strike Price Exp Vol Open Int 9/30/07 32.1 54 80 144.3189 $ 65.00 109 99.3234 12/31/15 50.58 85.09 126.06 227.4 102.42 171.75 156.5
2a 5 0 Call 29.59 0.3384 28 May, 2015 3051 40421 10/31/07 36.2 51.4 86.25 148.6518 $ 62.86 104 91.3242 1/31/16 57.04 80.99 135.9 234.23 99.04 163.87 143.9
2b 5 0 Annual interest rate = 0.02626 Trading days per year = 252 11/30/07 37.2 53.2 82.5 159.6507 $ 66.79 104 87.6579 2/29/16 58.62 83.83 130 251.56 105.23 163.87 138.12
3a 2 0 12/31/07 34.9 50.1 78.125 161.6505 $ 67.86 105 91.3242 3/31/16 54.99 78.94 123.1 254.71 106.92 165.45 143.9
3b 2 0 ABCD 1/31/08 34.4 46.7 86.875 150.9849 $ 68.21 108 94.9905 4/30/16 54.2 73.59 136.89 237.91 107.49 170.18 149.68
3c 2 0 Option Type Stock Price IV Strike Price Exp Vol Open Int 2/29/08 33 43.9 102.5 136.3197 $ 65.71 106 95.6571 5/31/16 52 69.17 161.51 214.8 103.55 167.02 150.73
3d 2 0 38.5 0.39038 37 May, 2015 4892 55057 3/31/08 33 42.5 111.875 138.6528 $ 66.07 104 95.6571 6/30/16 52 66.97 176.28 218.48 104.11 163.87 150.73
3e 2 0 Annual interest rate 0.028886 Trading days per year 252 4/30/08 32.5 42.9 120 139.986 $ 70.71 105 95.9904 7/31/16 51.21 67.6 189.08 220.58 111.42 165.45 151.25
Total 30 0 5/31/08 33.4 43 120.625 140.6526 $ 70.36 101 95.6571 8/31/16 52.63 67.76 190.07 221.63 110.86 159.15 150.73
6/30/08 32.6 41.6 119.375 140.6526 $ 68.93 100 94.3239 9/30/16 51.37 65.55 188.1 221.63 108.61 157.57 148.63
Comments: 7/31/08 32.3 39.9 124.375 145.9854 $ 69.29 99.6 95.6571 10/31/16 50.9 62.87 195.98 230.03 109.17 156.94 150.73
8/31/08 31.3 39.4 124.375 148.9851 $ 68.21 103 94.3239 11/30/16 49.32 62.08 195.98 234.76 107.49 162.3 148.63
9/30/08 31.7 39.8 124.375 154.6512 $ 68.93 108 93.6573 12/31/16 49.95 62.71 195.98 243.68 108.61 170.18 147.58
10/31/08 38.3 39.2 121.875 162.6504 $ 70.36 101 97.3236 1/31/17 60.35 61.77 192.04 256.29 110.86 159.15 153.35
11/30/08 40.6 38.1 105.625 178.3155 $ 76.07 100 106.9893 2/28/17 63.97 60.03 166.43 280.97 119.87 157.57 168.58
12/31/08 42.1 39.4 104.375 184.3149 $ 85.00 101 120.9879 3/31/17 66.34 62.08 164.46 290.42 133.93 159.15 190.64
1/31/09 43 40.9 112.5 179.6487 $ 88.21 101 140.3193 4/30/17 67.76 64.45 177.27 283.07 139 159.15 221.1
2/28/09 43.3 40.3 112.5 173.316 $ 83.57 102 145.9854 5/31/17 68.23 63.5 177.27 273.09 131.68 160.72 230.03
3/31/09 44 42.4 119.375 181.9818 $ 81.43 101 141.6525 6/30/17 69.33 66.81 188.1 286.75 128.31 159.15 223.2
4/30/09 43.3 44.6 121.875 181.9818 $ 82.86 98 135.3198 7/31/17 68.23 70.28 192.04 286.75 130.56 154.42 213.22
5/31/09 41.2 46.6 127.5 183.6483 $ 83.21 98.5 129.6537 8/31/17 64.92 73.43 200.9 289.37 131.12 155.21 204.3
6/30/09 41.4 42.4 131.875 184.9815 $ 83.57 96.2 123.321 9/30/17 65.23 66.81 207.8 291.48 131.68 151.58 194.32
7/31/09 39.4 43.1 130 186.3147 $ 83.21 96.7 118.3215 10/31/17 62.08 67.91 204.84 293.58 131.12 152.37 186.44
8/31/09 39.6 46.1 123.75 193.9806 $ 83.57 96.2 112.3221 11/30/17 62.4 72.64 194.99 305.66 131.68 151.58 176.99
9/30/09 39.5 48.4 121.875 202.3131 $ 85.00 101 110.9889 12/31/17 62.24 76.26 192.04 318.78 133.93 159.15 174.89
10/31/09 36.1 54.3 114.375 202.9797 $ 83.57 101 102.6564 1/31/18 56.88 85.56 180.22 319.84 131.68 159.15 161.76
11/30/09 36.3 53.1 91.25 193.9806 $ 77.50 93.8 98.3235 2/28/18 57.2 83.67 143.78 305.66 122.12 147.8 154.93
12/31/09 39.3 59.3 86.875 189.3144 $ 76.79 91.9 111.6555 3/31/18 61.93 93.44 136.89 298.3 120.99 144.81 175.94
1/31/10 39.7 65.6 86.875 201.9798 $ 78.57 90.1 112.9887 4/30/18 62.56 103.37 136.89 318.26 123.81 141.97 178.04
2/28/10 39.8 71.5 90 219.978 $ 75.71 89.1 114.6552 5/31/18 62.71 112.66 141.81 346.62 119.3 140.39 180.66
3/31/10 41.3 78 80.625 234.9765 $ 78.57 86.8 120.3213 6/30/18 65.08 122.9 127.04 370.25 123.81 136.77 189.59
4/30/10 42 85.5 98.75 238.9761 $ 82.50 87 122.6544 7/31/18 66.18 134.72 155.6 376.55 130 137.09 193.27
5/31/10 43.6 85.7 92.5 244.9755 $ 85.36 84.8 122.6544 8/31/18 68.7 135.04 145.75 386.01 134.5 133.62 193.27
6/30/10 47.6 82.3 98.75 275.9724 $ 93.21 85.1 125.6541 9/30/18 75 129.68 155.6 434.85 146.88 134.09 197.99
7/31/10 48.4 84.5 100 309.3024 $ 98.21 86.2 127.6539 10/31/18 76.26 133.15 157.57 487.37 154.76 135.83 201.14
8/31/10 50.9 86.5 101.875 320.6346 $ 103.21 92.7 129.3204 11/30/18 80.2 136.3 160.52 505.22 162.63 146.07 203.77
9/30/10 48.2 88.2 106.25 318.6348 $ 102.50 108 127.3206 12/31/18 75.95 138.98 167.42 502.07 161.51 170.18 200.62
10/31/10 45.8 93 100.625 302.6364 $ 99.64 102 118.3215 1/31/19 72.17 146.54 158.55 476.86 157.01 160.72 186.44
11/30/10 42.7 93.7 85 281.9718 $ 89.64 98.7 112.3221 2/28/19 67.28 147.64 133.93 444.3 141.25 155.52 176.99
12/31/10 40.1 89.3 82.5 227.6439 $ 83.57 99.3 108.9891 3/31/19 63.19 140.71 130 358.7 131.68 156.47 171.73
1/31/11 35.7 84.6 88.75 194.3139 $ 78.57 97.5 111.9888 4/30/19 56.25 133.3 139.84 306.18 123.81 153.63 176.46
2/28/11 31.7 75.9 90.625 185.3148 $ 76.43 100 114.3219 5/31/19 49.95 119.6 142.8 292 120.43 157.57 180.14
3/31/11 30.6 73.8 94.375 178.6488 $ 73.21 95.2 115.3218 6/30/19 48.22 116.29 148.71 281.5 115.36 150.01 181.71
4/30/11 29.9 73.7 100 181.6485 $ 72.86 92.1 113.322 7/31/19 47.11 116.13 157.57 286.22 114.8 145.12 178.56
5/31/11 29.5 73.9 102.5 185.6481 $ 75.71 94.5 114.3219 8/31/19 46.48 116.44 161.51 292.53 119.3 148.9 180.14
6/30/11 29.6 69 104.375 180.6486 $ 69.64 94 111.9888 9/30/19 46.64 108.72 164.46 284.65 109.74 148.12 176.46
7/31/11 30.4 69.7 108.125 198.3135 $ 72.14 98.6 113.9886 10/31/19 47.9 109.83 170.37 312.48 113.68 155.36 179.61
8/31/11 29.6 69.8 103.125 200.9799 $ 71.43 105 111.6555 11/30/19 46.64 109.98 162.49 316.68 112.55 165.45 175.94
9/30/11 30.5 69.8 102.5 206.9793 $ 70.71 114 110.3223 12/31/19 48.06 109.98 161.51 326.14 111.42 179.63 173.83
10/31/11 37.7 69.6 109.375 219.3114 $ 72.50 106 107.6559 1/31/20 59.4 109.67 172.34 345.57 114.24 167.02 169.63
11/30/11 39.4 68.2 99.375 221.6445 $ 75.36 105 106.656 2/29/20 62.08 107.46 156.59 349.25 118.74 165.45 168.06
12/31/11 36.9 65.9 93.125 204.9795 $ 69.64 108 107.9892 3/31/20 58.14 103.84 146.74 322.99 109.74 170.18 170.16
1/31/12 34.1 67.7 96.25 192.3141 $ 67.86 106 111.9888 4/30/20 53.73 106.67 151.66 303.03 106.92 167.02 176.46
2/29/12 29.9 70.2 99.375 188.9811 $ 65.00 105 114.3219 5/31/20 47.11 110.61 156.59 297.78 102.42 165.45 180.14
3/31/12 28.2 75.6 102.5 187.3146 $ 63.21 98.3 114.9885 6/30/20 44.43 119.12 161.51 295.15 99.61 154.89 181.19
4/30/12 29.1 75.4 108.125 192.6474 $ 68.57 96.9 117.6549 7/31/20 45.85 118.81 170.37 303.55 108.05 152.69 185.39

Question 1

You wish to hedge against interest rate changes so you enter into a swap
contract with another party for the next ten years based on the term
structure shown to the right, and the notional amount shown below. Term Structure
Year Rate
Notional amount 14420000 1 2.6260%
2 2.7573%
3 2.8952%
a) What is the value of the floating rate portion of the swap? Vfloat = 4 3.0399%
Points 5 5 3.1919%
Grade 6 3.3515%
7 3.5191%
b) Determine the fixed rate for the contract such that the swap has a r = 8 3.6950%
value of zero at the signing of the contract. 9 3.8798%
Points 5 10 4.0738%
Grade

Question 2

A buyer buys four futures contracts for soybeans per the quantity and Buyer Seller
initial price shown below. A seller sells the contracts for the same Margin Account Settlement Margin Account
quantity and price. Day Deposits Call Balance Price Deposits Call Balance
Initial Price $ 4.92 1 4.92
# Bushels 20,000
Initial Account % 5.00%
Margin Maintenance % 75% 2 5.02
3 5.17
a) Based on the daily settlment prices shown in the table to the right,
complete the yellow portions of the table identifying where
appropriate the Deposits, Margin Calls, and Account Ballances for 4 5.17
both the Buyer and the Seller.
Note, not all of the yellow cells will require a value.
Points 5 5 5.01
Grade
b) If the contract is closed at the close of the 10th day, calculate the 6 4.91
Total Gain or Loss for both the Buyer and the Seller. Fill in the yellow
cells provided at the bottom of the table to the right.
Points 5 7 4.76
Grade
Note, you do not need to do all of this with Excel formulas. 8 4.81
Calculators are fine, or a combination of both. It is just a step by step
process!
9 4.71
Note: How many bushels are in a contract?
10 4.71
Deposits
Final Balance
Total (Gain or Loss)

Question 3

A farmer has an uncertain price for her 20,000 tons of sorghum, but there Feed Grain Commodities
is not a future or option available to perform a perfet hedge. So, she Date Sorghum A B C D E F
chooses to base her hedge on one of six other future grain commodities, Jul-14 41.28 94.70 232.66 116.21 132.35 116.76 102.42
A, B, C, D, E, and F whose historical data are shown to the far right. Aug-14 45.07 94.23 242.63 118.18 131.82 119.75 107.49
All prices shown to the right are $ per ton. Sep-14 48.22 91.71 251.56 125.07 133.40 121.33 111.42
Today's date is Jul-20 Oct-14 50.58 88.87 257.86 126.06 136.02 122.43 114.24
The current prices are: Current Price Nov-14 51.05 90.60 262.59 132.95 134.97 129.05 114.24
Sorghum 45.85 Dec-14 52.47 88.55 272.57 129.01 136.02 138.50 118.74
A 118.81 Jan-15 52.31 89.66 258.91 122.12 131.30 134.09 107.49
B 303.55 Feb-15 44.43 88.08 237.91 106.36 121.84 131.26 92.29
C 170.37 Mar-15 43.02 90.13 233.71 91.59 126.04 133.46 85.54
D 185.39 Apr-15 43.65 87.14 241.06 93.56 127.62 135.51 90.60
E 152.69 May-15 47.43 87.77 233.71 106.36 140.75 140.55 97.92
Complete parts a through e below: F 108.05 Jun-15 51.53 90.13 238.96 120.15 148.10 138.66 104.67
Jul-15 55.78 89.66 251.04 112.27 150.73 141.34 110.86
a) In the yellow box area provided, create summary table and correlation Summary Sorghum A B C D E F Aug-15 53.42 92.34 245.78 119.16 149.15 143.39 111.42
table feed grain commodities; fill in the yellow cells provided. Average Sep-15 54.68 90.13 234.23 126.06 148.63 146.38 110.30
Points 2 Variance Oct-15 51.84 89.18 230.55 122.12 150.73 149.69 110.30
Grade StDev Nov-15 47.90 89.50 221.63 126.06 150.20 165.45 106.36
Dec-15 50.58 85.09 227.40 126.06 156.50 171.75 102.42
Correlation Sorghum A B C D E F Jan-16 57.04 80.99 234.23 135.90 143.90 163.87 99.04
Sorghum Feb-16 58.62 83.83 251.56 130.00 138.12 163.87 105.23
A Mar-16 54.99 78.94 254.71 123.10 143.90 165.45 106.92
B Apr-16 54.20 73.59 237.91 136.89 149.68 170.18 107.49
C May-16 52.00 69.17 214.80 161.51 150.73 167.02 103.55
D Jun-16 52.00 66.97 218.48 176.28 150.73 163.87 104.11
E Jul-16 51.21 67.60 220.58 189.08 151.25 165.45 111.42
F Aug-16 52.63 67.76 221.63 190.07 150.73 159.15 110.86
Sep-16 51.37 65.55 221.63 188.10 148.63 157.57 108.61
b) Which of the other commodities, A, B, C, D, E, or F would you choose Recommended Oct-16 50.90 62.87 230.03 195.98 150.73 156.94 109.17
recommend to the farmer for hedging his Sorghum crop? commodity is: Nov-16 49.32 62.08 234.76 195.98 148.63 162.30 107.49
Points 2 Dec-16 49.95 62.71 243.68 195.98 147.58 170.18 108.61
Grade Jan-17 60.35 61.77 256.29 192.04 153.35 159.15 110.86
Feb-17 63.97 60.03 280.97 166.43 168.58 157.57 119.87
c) Calculate the number of tons and dollars of the commodity future you chose # of tons = Mar-17 66.34 62.08 290.42 164.46 190.64 159.15 133.93
in part b) above for the farmer to use as a hedge for her sorghum crop. $ = Apr-17 67.76 64.45 283.07 177.27 221.10 159.15 139.00
Points 2 May-17 68.23 63.50 273.09 177.27 230.03 160.72 131.68
Grade Jun-17 69.33 66.81 286.75 188.10 223.20 159.15 128.31
Jul-17 68.23 70.28 286.75 192.04 213.22 154.42 130.56
d) Calculate the standard deviation of the farmer's newly hedged position. New StDev = Aug-17 64.92 73.43 289.37 200.90 204.30 155.21 131.12
Points 2 Sep-17 65.23 66.81 291.48 207.80 194.32 151.58 131.68
Grade Oct-17 62.08 67.91 293.58 204.84 186.44 152.37 131.12
Nov-17 62.40 72.64 305.66 194.99 176.99 151.58 131.68
e) Is the farmer buying long or selling short these futures to hedge her Buy or sell? Dec-17 62.24 76.26 318.78 192.04 174.89 159.15 133.93
sorghum crop? Jan-18 56.88 85.56 319.84 180.22 161.76 159.15 131.68
Points 2 Feb-18 57.20 83.67 305.66 143.78 154.93 147.80 122.12
Grade Mar-18 61.93 93.44 298.30 136.89 175.94 144.81 120.99
Apr-18 62.56 103.37 318.26 136.89 178.04 141.97 123.81
May-18 62.71 112.66 346.62 141.81 180.66 140.39 119.30
Jun-18 65.08 122.90 370.25 127.04 189.59 136.77 123.81
Jul-18 66.18 134.72 376.55 155.60 193.27 137.09 130.00
Aug-18 68.70 135.04 386.01 145.75 193.27 133.62 134.50
Sep-18 75.00 129.68 434.85 155.60 197.99 134.09 146.88
Oct-18 76.26 133.15 487.37 157.57 201.14 135.83 154.76
Nov-18 80.20 136.30 505.22 160.52 203.77 146.07 162.63
Dec-18 75.95 138.98 502.07 167.42 200.62 170.18 161.51
Jan-19 72.17 146.54 476.86 158.55 186.44 160.72 157.01
Feb-19 67.28 147.64 444.30 133.93 176.99 155.52 141.25
Mar-19 63.19 140.71 358.70 130.00 171.73 156.47 131.68
Apr-19 56.25 133.30 306.18 139.84 176.46 153.63 123.81
May-19 49.95 119.60 292.00 142.80 180.14 157.57 120.43
Jun-19 48.22 116.29 281.50 148.71 181.71 150.01 115.36
Jul-19 47.11 116.13 286.22 157.57 178.56 145.12 114.80
Aug-19 46.48 116.44 292.53 161.51 180.14 148.90 119.30
Sep-19 46.64 108.72 284.65 164.46 176.46 148.12 109.74
Oct-19 47.90 109.83 312.48 170.37 179.61 155.36 113.68
Nov-19 46.64 109.98 316.68 162.49 175.94 165.45 112.55
Dec-19 48.06 109.98 326.14 161.51 173.83 179.63 111.42
Jan-20 59.40 109.67 345.57 172.34 169.63 167.02 114.24
Feb-20 62.08 107.46 349.25 156.59 168.06 165.45 118.74
Mar-20 58.14 103.84 322.99 146.74 170.16 170.18 109.74
Apr-20 53.73 106.67 303.03 151.66 176.46 167.02 106.92
May-20 47.11 110.61 297.78 156.59 180.14 165.45 102.42
Jun-20 44.43 119.12 295.15 161.51 181.19 154.89 99.61
Jul-20 45.85 118.81 303.55 170.37 185.39 152.69 108.05

Copy, Paste, IP Data