• Home
• Blog
• Using Excel VBA to Calculate Items for The Stock Option Worksheet

# Using Excel VBA to Calculate Items for The Stock Option Worksheet

Here is the assignment, I have also uploaded the Excel file, please submit an Excel file to me with the answers of all the requirements below:

 Consider the below Equity Linked Note: Tenor 1 Month + 2weeks Trade Date 03 Oct 2017 (Tue) Issue Date 03 Oct 2017 (Tue) Final Valuation Date 17 Nov 2107 (Fri) Maturity Date 17 Nov 2107 (Fri) Denomination USD 100K Currecny USD Put Strike (K) 93% Underlying Stock A (US stock) Initial Spot – S(0) (the stock price at which the initial Delta is executed) USD 50.00 Final Performance Stock Price at Final Valuation Date / Initial Spot Final Redemption at Maturity If Final Performance >= K, 100% of Denomination Othwise, Denomination / [K*S(0)] shares of Stock A Issuer/Distributor Margin 0.30% Market Parameters: Interest Rate (30/360) 2.00% Volatility of Stock A 35% Dividend rate (continuous, Act/365) 3.50% Repo rate (continuous, Act/365) 3.00%
 Market Parameters: Interest Rate (30/360) 2.00% Volatility of Stock A 35% Dividend rate (continuous, Act/365) 3.50% Repo rate (continuous, Act/365) 3.00%

Use Black-Scholes formula and Excel-VBA to calculate:

 1) Issue Price in % of Denomination 2) Delta in % of price 3) Vega in % of price (for 1% vol change) 4) Rho in % of price (for 1 basis point rate change)