Problem:
Construct a spreadsheet that can be used for calculating Black-Scholes call option prices. Before proceeding, verify your model using the following parameters:
St = $60.00
K = $60.00
rf = 0.025
T = 0.5 (6 months)
σ = 0.35
Ct = $6.2523
1. Using the values of K, rf, T, and σ specified above, tabulate and plot call prices and hedge ratios for values of St ranging from $50.00 to $70.00 in $1.00 increments.
2. Using the values of St, K, rf , and T specified above, tabulate and plot call prices for values of σ ranging from 0.10 to 0.50 in 0.01 increments.
3. Using the values of St, K, rf , and T specified above, use your spreadsheet and trial and error (or Solver) to estimate the implied volatility (accurate to four decimal places) of a call with a price of $8.2568.
4. Using the values of St, K, rf , and s specified above, tabulate and plot call prices for values of T ranging from 0 to 52 weeks using 2-week increments (assume 1 week = 7/365 years).
5. Using the values of St, K, σ , and T specified above, tabulate and plot call prices for values of rf ranging from 0.01 to 0.2 using 0.01 increments.
6. Modify your spreadsheet to calculate put prices. Using the values of K, rf, T, and σ specified above, tabulate and plot put prices for values of St ranging from $50.00 to $70.00 in $1.00 increments.
Note: There are several basic ways to approach this project. Depending on your spreadsheet modelling skills, these include writing macros, using VBA, using Data (sensitivity) Tables, or simply copying and pasting formulas.