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

Using Excel VBA to Calculate Items for The Stock Option Worksheet

0 comments

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)

About the Author

Follow me


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}