I need to fill out Tab III, IV, and V from the Excel document with the information from the same Excel document. No research is needed for it. Only to move the information from the other tabs to the last three, as mentioned above. Include the correct formulas where is need it.
Index This file is the template to use for the class project for FINC 6367 - International Finance INDEX Source data for DC1 I. Initial data DC1 Source data for DC2 II. Final data DC2 III. Trading profits IV. One year forecastStudents must update the cells below V. Hedging Team members Garza-Gomez, Xavier: Please include the names of students in the group, the currency and the semester Cheyenne Miller Alicia Murray Eduardo Zarate Currency studiedUSD/CHF Semester/yearSummer 2023 Source data for DC1 Forex RatesForward RatesFuturesOptionsInterest ratesForecast http://www.investing.com/currencies/streaming-forex-rates-majorshttp://www.investing.com/rates-bonds/forward-rateshttps://www.cmegroup.com/markets/fx/g10/japanese-yen.settlements.htmlhttps://www.cmegroup.com/markets/fx/g10/japanese-yen.settlements.options.html#optionProductId=8123 All major currencies SWISS FRANC FUTURES - SETTLEMENTSUSDSecured Overnight Financing Rate (SOFR)https://www.fxstreet.com/rates-charts/forecast CHF/USD Monthly Options - Settlementshttps://www.global-rates.com/en/interest-rates/sofr/sofr.aspx PairBidAskHighLowChg.Chg. %TimeNameBidAskHighLowChgTimeCHF/USD Weekly Friday Options - Settlements EUR/USD1.08931.08961.0971.0844-0.0063-0.57%16:59:38USDCHF ON FWD-0.946-0.802-0.946-2.481.70422:10:00Current interest rates USD/JPY143.72143.73143.88142.70.610.42%16:59:38USDCHF TN FWD-0.873-0.836-0.029-0.870.00722:20:00MonthOpenHighLowLastChangeSettleEst. VolumePrior day OI june 22 20235.0500%SOFR (Secured Overnight Financing Rate) is an interest rate published by the Federal Reserve Bank of New York. SOFR can be seen as the average interest rate for secured loans issued in American Dollars (USD) with a maturity of 1 day (overnight). SOFR is a reference rate (benchmark rate) and an alternative to the US Dollar LIBOR rate.USD/CHF GBP/USD1.27131.27151.27521.2685-0.0035-0.27%16:59:38USDCHF SN FWD-0.889-0.852-0.889-0.860.00122:14:0023-Sep1.127551.1281.119151.12475-0.00151.124417,51036,966 june 21 20235.0500%https://www.fxstreet.com/rates-charts/usdchf/forecast USD/TRY25.232525.242525.808324.80050.35351.42%16:59:42USDCHF SW FWD-7.54-7.46-7.54-7.46022:10:0023-Dec1.1321.13840B1.13090A1.13090A-0.00141.1352513491 june 20 20235.0500% USD/CHF0.89680.89720.90130.89440.00230.26%16:59:38USDCHF 2W FWD-13.03-12.86-12.98-13.50.1922:12:0024-Mar-1.14940B1.14230A1.14940B-0.001451.14645067CallsStrike PricePuts june 16 20235.0500% USD/CAD1.31791.31841.32261.31430.0030.23%16:59:42USDCHF 3W FWD-19.38-19.18-19.34-19.610.2922:10:0024-Jun-1.15875B1.15180A1.15875B-0.00141.15605024Est. VolPrior day OIHighLowOpenLastSettleChangeChangeSettleLastOpenLowHighPrior day OIEst. Vol june 15 20235.0600% EUR/JPY156.62156.62156.83155.06-0.17-0.11%16:59:32USDCHF 1M FWD-27.57-27.27-27.51-27.550.3322:10:0024-Sep-----0.001351.1635501500.06740B.05970A-.05970A0.0644-0.00151.0600.00005----00 june 14 20235.0500% AUD/USD0.66770.66780.67670.6663-0.0078-1.15%16:59:40USDCHF 2M FWD-58.14-57.57-57.97-57.72-0.1922:12:0024-Dec-----0.001351.170950800.06240B.05470A-.05470A0.0594-0.00151.06500.00005----00 june 13 20235.0500% NZD/USD0.61470.61530.61910.6117-0.0028-0.45%16:59:28USDCHF 3M FWD-86.46-85.87-86.33-86.03-0.0622:24:0025-Mar-----0.001351.17840000.05740B.04970A-.04970A0.0544-0.00151.0700.00005----00 june 12 20235.0500% EUR/GBP0.85660.85690.86090.8536-0.0026-0.30%16:59:40USDCHF 4M FWD-115.38-114.84-115.33-114.840.9722:12:0025-Jun-----0.00131.1860000.05240B.04470A-.04470A0.0494-0.00151.07500.00005----00 june 09 20235.0500% EUR/CHF0.97730.97760.98150.975-0.0029-0.30%16:59:28USDCHF 5M FWD-143.84-143.17-143.81-143.340.0322:12:0025-Sep-----0.00111.192050000.04740B.03970A-.03970A0.0444-0.00151.0800.00005----00 june 08 20235.0500% AUD/JPY969696.8495.25-0.69-0.71%16:59:41USDCHF 6M FWD-171.46-170.66-171.24-170.672.8422:15:0025-Dec-----0.000851.197950000.04240B.03470A-.03470A0.0394-0.00151.08500.0001----100 june 07 20235.0500% GBP/JPY182.64182.84182.95181.260.30.16%16:59:28USDCHF 7M FWD-215.35-201.35-208.69-208-5.0122:20:0026-Mar-----0.00061.20390000.03750B.02980A-.02980A0.0345-0.00141.0900.0001----00 june 06 20235.0500% CHF/JPY160.21160.21160.44158.490.260.16%16:59:34USDCHF 8M FWD-241.06-227.06-233.7-233.64-4.2122:20:0026-Jun-----0.000351.20990000.03250B.02480A-.02480A0.0295-0.00151.09500.0001----00 EUR/CAD1.43591.4361.44251.4318-0.0047-0.33%16:59:28USDCHF 9M FWD-258.38-257.35-257.89-257.62.0622:22:0026-Sep-----0.000151.215950000.02750B.01990A-.01990A0.0245-0.00151.100.00015----9000USDLIBOR AUD/CAD0.88020.88030.88970.8795-0.0083-0.93%16:59:34USDCHF 10M FWD-290.26-275.26-285.13-284.171.9422:22:0026-Dec----0.000151.22210000.02260B.01520A-.01520A0.0196-0.00151.10500.0002----7000https://www.global-rates.com/en/interest-rates/libor/american-dollar/american-dollar.aspx CAD/JPY109.04109.05109.09108.020.230.21%16:59:36USDCHF 11M FWD-312.43-295.43-306.73-306.89-4.7522:22:0027-Mar----0.000351.228250002.01780B.01080A-.01080A0.0148-0.00151.110.000050.00045-.00080B-.00080B7000USD6/23/236/22/236/21/236/20/236/19/23The US Dollar LIBOR interest rate is the average interbank interest rate at which a large number of banks on the London money market are prepared to lend one another unsecured funds denominated in US Dollars. The US Dollar (USD) LIBOR interest rate is available in 7 maturities, from overnight (on a daily basis) to 12 months NZD/JPY88.3588.3988.5987.48-0.03-0.03%16:59:34USDCHF 1Y FWD-330-328.75-329.82-328.899.722:12:0027-Jun----0.000651.234550000.01320B.00720A-.00720A0.0104-0.00141.1150.00010.001-.00200B-.00200B20 USD LIBOR - overnight5.0676%5.0690%5.0627%5.0753%- AUD/NZD1.0871.0871.09411.0864-0.0066-0.60%16:59:34USDCHF 15M FWD-405.99-380.99-395.67-392.7-1.0921:51:0027-Sep----0.00091.240850000.00890B.00440A-.00440A0.0066-0.00121.120.00030.0022-.00410B.00170A.00410B00 USD LIBOR - 1 month5.1484%5.1504%5.148%5.15414 %5.1627% GBP/AUD1.90391.90391.90981.88360.0170.90%16:59:41USDCHF 21M FWD-514.89-479.89-503.79-498.6912.5121:51:0027-Dec----0.00121.247250020.00550B0.00240.0024.00550B0.0038-0.00091.1250.00060.00440.0038.00490B.00320A.00720B16 USD LIBOR - 3 months5.5441%5.5419%5.540%5.52029 %5.5143% EUR/AUD1.63111.63111.63381.61890.00950.59%16:59:41USDCHF 2Y FWD-568.78-528.78-552.76-537.776.4221:50:0028-Mar----0.00151.25370000.00300B.00130A-.00130A0.0019-0.00071.130.00080.0075-.01120B.00600A.01120B00 USD LIBOR - 6 months5.6903%5.6826%5.682%5.67400 %5.6564% GBP/CHF1.1411.1411.14531.13810.00040.04%16:59:28USDCHF 3Y FWD-756.16-687.79-731-704.27.7421:40:0028-Jun----0.00181.26050060.00140B.00070A-.00070A0.0009-0.00041.1350.00110.0115-.01570B.00930A.01570B00 USD LIBOR - 12 months5.9253%5.9011%5.899%5.89786 %5.8951% EUR/NZD1.77311.77321.78121.7667-0.0003-0.02%16:59:30USDCHF 4Y FWD-925.44-833.8-921.92-838.4-3.5121:40:00010-.00045A-.00045A0.0004-0.00021.140.00130.016-.02040B.01330A.02040B00 AUD/CHF0.59920.59920.60590.5977-0.0053-0.88%16:59:41USDCHF 5Y FWD-1100.6-990.6-1064-990.6-1.8921:40:0060----0.00015-0.00011.1450.00140.0207-.02530B.01790A.02530B00 GBP/NZD2.06892.07052.08012.0590.00610.30%16:59:34USDCHF 6Y FWD-1275-1145-1234-1169-4921:32:0000----0.00005-0.000051.150.00140.0256-.03030B.02270A.03030B00CHFSARON (Swiss Average Rate Overnight) is an interest rate published by the Swiss SIX Index AG. SARON can be seen as the average interest rate for loans issued in Swiss Francs (CHF) with a maturity of 1 day (overnight). SARON is a reference rate (benchmark rate) and an alternative to Swiss franc LIBOR. USD/INR81.95881.95882.08481.910.0450.06%16:59:38USDCHF 7Y FWD-1671.46-1071.46-1404-1329-280.4621:03:0000----CAB-0.000051.1550.00150.0306-.03520B.02760A.03520B00SARON (Swiss Average Rate Overnight) USD/CNY7.17897.18097.18017.17950.00040.01%16:59:32USDCHF 8Y FWD-1622-1462-1572-1488-6121:10:0000----CAB01.160.00150.0356-.04020B.03250A.04020B00https://www.global-rates.com/en/interest-rates/saron/saron.aspx USD/SGD1.35191.35241.35321.34390.00750.55%16:59:55USDCHF 9Y FWD-1795-1620-1744-1652-6621:00:0000----CAB01.1650.00150.0406-.04520B.03750A.04520B00 USD/HKD7.83047.83057.83377.8276-0.00010.00%16:59:36USDCHF 10Y FWD-1966-1776-1914-1814-7021:20:0000----CAB01.170.00150.0456-.05020B.04250A.05020B00Current interest rates USD/DKK6.83466.83696.86856.79670.03840.56%16:59:54USDCHF 15Y FWD-2882.2-2811.1201-2882.0667-2882.1975-0.006622:24:0000----CAB01.1750.00160.0506-.05510B.04750A.05520B00 june 22 20231.4487% GBP/CAD1.67611.67611.68321.6739-0.0004-0.02%16:59:36USDCHF 20Y FWD-3733.71-3642.24-3733.592-3733.7129-0.006122:24:0000----CAB01.180.00150.0555-.06020B.05250A.06020B00 june 21 20231.4471% USD/SEK10.713510.716510.80810.6890.02450.23%16:59:56https://www.cmegroup.com/markets/fx/g10/japanese-yen.contractSpecs.html00----CAB01.1850.00150.0605-.06520B.05750A.06520B00 june 20 20231.4455% USD/ILS3.62383.62883.63433.58130.01090.30%16:47:01 june 19 20231.4421% USD/RUB84.784.784.839383.64780.650.77%16:50:00Swiss Franc Futures - Contract Specs june 16 20231.4405% USD/MXN17.15817.18917.265717.13710.00850.05%16:59:55 june 15 20231.4426% USD/ZAR18.730418.762518.775918.48490.23071.25%16:59:56CONTRACT UNIT125,000 Swiss francs june 14 20231.4459% CAD/CHF0.68070.68070.68320.67730.00030.04%16:59:34PRICE QUOTATIONU.S. dollars and cents per CHF increment june 13 20231.4439% NZD/CAD0.80970.80970.8140.8081-0.0027-0.33%16:59:36TRADING HOURSCME Globex: june 12 20231.4463% NZD/CHF0.55070.55150.55430.5488-0.0016-0.29%16:59:34Sunday - Friday 6:00 p.m. - 5:00 p.m. (5:00 p.m. - 4:00 p.m. CT) with a 60-minute break each day beginning at 5:00 p.m. (4:00 p.m. CT) june 09 20231.4454% BTC/USD30,682.0030,683.0031,430.0029,835.007312.44%19:00:42CME ClearPort: june 08 20231.4425% BTC/EUR28,124.8028,124.9028,835.0027,242.80827.73.03%18:57:56Sunday 5:00 p.m. - Friday 5:45 p.m. CT with no reporting Monday - Thursday from 5:45 p.m. - 6:00 p.m. CT june 07 20231.4466% ETH/USD1,890.791,890.801,936.421,861.0119.471.04%19:00:35MINIMUM PRICE FLUCTUATIONCME Globex: 0.00005 CHF increment = $6.25 Calendar spreads: 0.00005 CHF increment = $6.25SWISS FRANC LIBOR THREE MONTH RATE CME ClearPort:Website used to get CHF interest rateLinks to an external site. - LIBOR rates for CHF are no longer available. We can use this rate for this project 0.00001 per CHF increment = $1.25URLhttps://tradingeconomics.com/switzerland/interbank-rate PRODUCT CODECME Globex: 6S CME ClearPort: E1 Clearing: E1 LISTED CONTRACTSQuarterly contracts (Mar, Jun, Sep, Dec) listed for 20 consecutive quarters SETTLEMENT METHODDeliverable TERMINATION OF TRADINGTrading terminates at 9:16 a.m. CT, 2 business day prior to the third Wednesday of the contract month. SETTLEMENT PROCEDURESCHF/USD Futures Settlement Procedures POSITION LIMITSCME Position Limits EXCHANGE RULEBOOKCME 254 BLOCK MINIMUMBlock Minimum Thresholds PRICE LIMIT OR CIRCUITPrice Limits VENDOR CODESQuote Vendor Symbols Listing http://www.investing.com/currencies/streaming-forex-rates-majorshttps://www.investing.com/currencies/usd-jpyhttps://www.investing.com/currencies/gbp-usdhttps://www.investing.com/currencies/usd-tryhttps://www.investing.com/currencies/usd-chfhttps://www.investing.com/currencies/usd-cadhttps://www.investing.com/currencies/eur-jpyhttps://www.investing.com/currencies/aud-usdhttps://www.investing.com/currencies/nzd-usdhttps://www.investing.com/currencies/eur-gbphttps://www.investing.com/currencies/eur-chfhttp://www.investing.com/rates-bonds/forward-rateshttps://www.investing.com/currencies/aud-jpyhttps://www.investing.com/currencies/gbp-jpyhttps://www.investing.com/currencies/chf-jpyhttps://www.investing.com/currencies/eur-cadhttps://www.investing.com/currencies/aud-cadhttps://www.investing.com/currencies/cad-jpyhttps://www.investing.com/currencies/nzd-jpyhttps://www.investing.com/currencies/aud-nzdhttps://www.investing.com/currencies/gbp-audhttps://www.investing.com/currencies/eur-audhttps://www.global-rates.com/en/interest-rates/libor/american-dollar/american-dollar.aspxhttps://www.investing.com/currencies/gbp-chfhttps://www.investing.com/currencies/eur-nzdhttps://www.investing.com/currencies/aud-chfhttps://www.investing.com/currencies/gbp-nzdhttps://www.investing.com/currencies/usd-inrhttps://www.investing.com/currencies/usd-cnyhttps://www.investing.com/currencies/usd-sgdhttps://www.investing.com/currencies/usd-hkdhttps://www.investing.com/currencies/usd-dkkhttps://www.investing.com/currencies/gbp-cadhttps://www.global-rates.com/en/interest-rates/sofr/sofr.aspxhttps://www.investing.com/currencies/usd-sekhttps://www.investing.com/currencies/usd-ilshttps://www.investing.com/currencies/usd-rubhttps://www.investing.com/currencies/usd-mxnhttps://www.investing.com/currencies/usd-zarhttps://www.investing.com/currencies/cad-chfhttps://www.investing.com/currencies/nzd-cadhttps://www.investing.com/currencies/nzd-chfhttps://www.investing.com/crypto/bitcoin/btc-usdhttps://www.investing.com/crypto/bitcoin/btc-eurhttps://www.global-rates.com/en/interest-rates/saron/saron.aspxhttps://www.investing.com/crypto/ethereum/eth-usdhttps://www.cmegroup.com/markets/fx/g10/japanese-yen.settlements.options.htmlhttps://www.fxstreet.com/rates-charts/usdchf/forecasthttps://www.cmegroup.com/confluence/display/EPICSANDBOX/Swiss+Franchttps://www.cmegroup.com/rulebook/files/position-limits-cme.xlsxhttps://www.cmegroup.com/rulebook/CME/III/250/254/254.pdfhttps://www.cmegroup.com/clearing/trading-practices/block-trades.htmlhttps://www.cmegroup.com/rulebook/files/special-price-fluctuation-limits.xlsxhttps://www.cmegroup.com/tools-information/vendorSymbol.htmlhttps://www.global-rates.com/en/interest-rates/libor/american-dollar/usd-libor-interest-rate-overnight.aspxhttps://www.fxstreet.com/rates-charts/forecasthttps://www.global-rates.com/en/interest-rates/libor/american-dollar/usd-libor-interest-rate-1-month.aspxhttps://www.global-rates.com/en/interest-rates/libor/american-dollar/usd-libor-interest-rate-3-months.aspxhttps://www.global-rates.com/en/interest-rates/libor/american-dollar/usd-libor-interest-rate-6-months.aspxhttps://www.global-rates.com/en/interest-rates/libor/american-dollar/usd-libor-interest-rate-12-months.aspxhttps://tradingeconomics.com/switzerland/interbank-ratehttps://www.cmegroup.com/markets/fx/g10/japanese-yen.settlements.htmlhttps://www.cmegroup.com/markets/fx/g10/japanese-yen.contractSpecs.htmlhttps://www.investing.com/currencies/eur-usd I. Initial data DC1 Template for Part 1 of project Date when hedge is initiated Students' namesCheyenne Miller, Alicia Murray, Eduardo ZarateFriday, June 23, 2023 Garza-Gomez, Xavier: Must be a Friday (or Thursday if Friday was a holiday) 1. Currency to hedgeSwiss Franc 1a Currency pair formed with the US dollar (as seen in OANDA, FXCM)USD/CHF 1b Date when hedge (trading) will be closed and all the calculations doneFriday, June 30, 2023 GarzaX: Students must state the date when they will "close" the hedges/trades in this theoretical exercise. Hint: it is the Friday (or Thursday if Friday is a holiday) after the initial date 1c Is this a direct quote in the spot market? (Y/N)N 2. Exchange rate of the currency as typically quoted in FX markets. Use the SPOT price from the Investing.com siteBIDASK 0.89680.8972Any cell in this color must be updated by students, Using links is strongly recommended (instead of typing the values) 2a Express the price as a direct quote (value of one unit of that currency in dollars)1.1145787 garzax: Calculated cell If direct quote, then it's same as 2. If indirect, then must calculate the inverse.1.1150758 3. Three or six-month future rate as of this date (use whatever period covers the second data collection period) Report the settlement price reported on the CME Group website1.1244 Expiration month of future contractSeptember-23 . Specify the size of the future (option) contracts 125000 GarzaX: Look in Contract Specifications 4. Forward points observed on this date. Use the Investing.com website for information. Select data for 1 week (SW). Students doing USDJPY must use an appropriate factor to convert pips.BIDASK -7.54-7.46 Horizon for Fwd quotes1 week 5. Use the CME group website to obtain option price data. WEEK 5 JUN 2023 Garza-Gomez, Xavier: Must indicate week/month/year of the option used Specify the expiration week,month and year on the CME options that will cover the period you are interested in hedging Strike Garza-Gomez, Xavier: The distance between strike prices must be at least 100 pips Premium 5a. Choose a call option that is in the money and obtain its premium1.1150.0104 garzax: Previously quoted as cents. With exception of EURUSD which was quoted in dollars, the JPY which was quoted as 100ths of a cent and the MXN, quoted in 1000ths of a cent. Since 2023, CME shows premiums in dollars for all currencies 5b. Find the call option that is at the money and obtain its premium Garza-Gomez, Xavier: ATM strike price is determined using the futures contract price that is linked to the option 1.1250.0038 5c. Choose a call option that is out the money and obtain its premium1.1350.0009 6. Use the CME group website to obtain put option price data. Use the same expiration month you chose for call optionsStrikePremium 6a. Choose a put option that is in the money and obtain its premium1.1350.0115 6b. Find the put option that is at the money and obtain its premium1.1250.0044 6c. Choose a put option that is out the money and obtain its premium1.1150.001 7. Use the Global Rates and Trading Economics websites to identify the interest rates Interest ratesDescription USD rate (Overnight)5.0676% Garza-Gomez, Xavier: Make sure there is no space before the % symbol If there is, go to the source sheet and manually remove it Garza-Gomez, Xavier: Must indicate week/month/year of the option used Garza-Gomez, Xavier: Must be a Friday (or Thursday if Friday was a holiday) Garza-Gomez, Xavier: The distance between strike prices must be at least 100 pips Garza-Gomez, Xavier: ATM strike price is determined using the futures contract price that is linked to the option garzax: Previously quoted as cents. With exception of EURUSD which was quoted in dollars, the JPY which was quoted as 100ths of a cent and the MXN, quoted in 1000ths of a cent. Since 2023, CME shows premiums in dollars for all currencies GarzaX: Students must state the date when they will "close" the hedges/trades in this theoretical exercise. Hint: it is the Friday (or Thursday if Friday is a holiday) after the initial date garzax: Calculated cell If direct quote, then it's same as 2. If indirect, then must calculate the inverse. GarzaX: Look in Contract SpecificationsLIBOR - ON1 Foreign currency rate (shortest term found)1.4487%SARON1 https://www.cmegroup.com/markets/fx/g10/japanese-yen.contractSpecs.html Swiss Franc Futures - Contract Specs https://www.cmegroup.com/markets/fx/g10/japanese-yen.contractSpecs.html Source data for DC2 Forex RatesFuturesOptions Swiss Franc Futures - Settlementshttps://www.cmegroup.com/markets/fx/g10/japanese-yen.settlements.options.html#optionProductId=8123 http://www.investing.com/currencies/streaming-forex-rates-majorshttp://www.cmegroup.com/trading/fx/g10/japanese-yen_quotes_settlements_futures.html Sunday, July 2nd, 2023 after market closure PairBidAskHighLowChg.Chg. %TimeMONTHOPENHIGHLOWLASTCHANGESETTLEEST. VOLUMEPRIOR DAY OI EUR/USD1.09121.09131.09131.09090.00030.03%18:15:17September 231.120751.12811.11791.12620.00581.1263518,85237,210 USD/JPY144