Answered step by step
Verified Expert Solution
Question
...
1 Approved Answer
Data has been entered into Excel confused on what I am doing wrong for the last part of part (a) $3.50$1.00=$2.50 per share. European put
Data has been entered into Excel confused on what I am doing wrong for the last part of part (a)
$3.50$1.00=$2.50 per share. European put options with an exercise price of $27. Each put option costs $1. $1.00. (b) Discuss the value of the portfolio with and without the European put options. $1.00. of values and formulas must be constructed. Determine the formulas with cell references to calculate the portfolio values and costs, with and without options. \begin{tabular}{|c|c|c|c|} \hline & A & B & C \\ \hline 1 & \multicolumn{2}{|r|}{ European Put Option } & \\ \hline \multicolumn{4}{|l|}{2} \\ \hline 3 & Parameters & & \\ \hline 4 & Cost of Put Option & $1.00 & \\ \hline 5 & Exercise Price & $27.00 & \\ \hline 6 & Time (months) & 6 & \\ \hline \multicolumn{4}{|l|}{7} \\ \hline 8 & Price per Share & $31.00 & \\ \hline 9 & Share Price - End of 6 Months & $23.50 & \\ \hline \multicolumn{4}{|l|}{10} \\ \hline 11 & Model & & \\ \hline 12 & Number of Shares Purchased & 310 & \\ \hline 13 & Number of Puts Purchased & 130 & \\ \hline \multicolumn{4}{|l|}{14} \\ \hline 15 & Value of Put Options & \begin{tabular}{l} =IF(B5>B9,B5B9,0) \\ $3.50 \end{tabular} & \\ \hline \multicolumn{4}{|l|}{16} \\ \hline 17 & Portfolio Value with Options & =B9B12+B13B15=B9B12+B13B15 & \\ \hline 18 & Cost of Portfolio with Options & =B12B8+B13B4=B12B8+B13B4 & \\ \hline 19 & Profit with Options & =B17B18 & \\ \hline \multicolumn{4}{|l|}{20} \\ \hline 21 & Portfolio Value without Options & =B9B12=B9B12 & \\ \hline 22 & Cost of Portfolio without Options & =B12B8 & \\ \hline 23 & Profit without Options & =B21B22 & \\ \hline \end{tabular} The formulas and values are below. Refer to your Excel spreadsheet. Follow the process to use the data table option. Select cells A28:C49. Click the Data tab in the Ribbon. Click What-If Analysis in the Forecast group. Select Data Table. new portfolio values. Enter the values (in dollars) of the portfolio with and without put options. Enter the values (in dollars) of the portfolio with and without put options. \begin{tabular}{|c|c|c|c|} \hline & A & B & C \\ \hline 27 & Share Price & Value with Options & Value Without Options \\ \hline 28 & & \begin{tabular}{l} =B17 \\ $7,740.00 \end{tabular} & \begin{tabular}{l} =B21 \\ $7,285.00 \end{tabular} \\ \hline 29 & $15.00 & $ & $ \\ \hline 30 & $16.00 & $ & $ \\ \hline 31 & $17.00 & $ & $ \\ \hline 32 & $18.00 & $ & $ \\ \hline 33 & $19.00 & $ & $ \\ \hline 34 & $20.00 & $ & $ \\ \hline 35 & $21.00 & $ & $ \\ \hline 36 & $22.00 & $ & $ \\ \hline 37 & $23.00 & $ & $ \\ \hline 38 & $24.00 & $ & $ \\ \hline 39 & $25.00 & $ & $ \\ \hline 40 & $26.00 & $ & $ \\ \hline 41 & $27.00 & $ & $ \\ \hline 42 & $28.00 & $ & $ \\ \hline 43 & $29.00 & $ & $ \\ \hline 44 & $30.00 & $ & $ \\ \hline 45 & $31.00 & $ & $ \\ \hline 46 & $32.00 & $ & $ \\ \hline 47 & $33.00 & $ & $ \\ \hline 48 & $34.00 & $ & $ \\ \hline 49 & $35.00 & $ & $ \\ \hline \end{tabular}Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started