Answered step by step
Verified Expert Solution
Link Copied!

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

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed

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

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

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Finance questions