60.1K

Verified Solution

Link Copied!

This assignment is to be done in excel only; if you could, please include any formulas or necessary excel info, thank you

A put option in finance allows you to sell a share of stock at a given price in the future. There are different types of put options. A European put option allows you to sell a share of stock at a given price, called the exercise price, at a particular point in time after the purchase of the option. For example, suppose you purchase a six-month European put option for a share of stock with an exercise price of $26. If six months later, the stock price per share is $26 or more, the option has no value. If in six months the stock price is lower than $26 per share, then you can purchase the stock and immediately sell it at the higher exercise price of $26. If the price per share in six months is $22.50, you can purchase a share of the stock for $22.50 and then use the put option to immediately sell the share for $26. Your profit would be the difference, $26$22.50=$3.50 per share, less the cost of the option. If you paid $1.00 per put option, then your profit would be $3.50$1.00=$2.50 per share. Managerial Report 1. Build a (spreadsheet) model to calculate the profit of this European put option. (20\%) (Hint: Follow the above to create the parameters and the formulas for Model) 2. Construct a data table that shows the profit per share for a share price in six months between $10 and $30 per share in increments of $1.00. (25\%) The point of purchasing a European option is to limit the risk of a decrease in the pershare price of the stock. Suppose you purchased 200 shares of the stock at $28 per share and 75 six-month European put options with an exercise price of $26. Each put option costs $1. 3. Build a (spreadsheet) model to show the value of the portfolio with options and without options. (20\%) (Hint: Follow the above to create the parameters and the formulas for Model) 4. Use data tables to shows the value of the portfolio with options and without options for a share price in six months between $15 and $35 per share in increments of $1.00. (25%) 5. Discuss the value of the portfolio with and without the European put options. Which one (with or without) is more profitable? (10\%) A put option in finance allows you to sell a share of stock at a given price in the future. There are different types of put options. A European put option allows you to sell a share of stock at a given price, called the exercise price, at a particular point in time after the purchase of the option. For example, suppose you purchase a six-month European put option for a share of stock with an exercise price of $26. If six months later, the stock price per share is $26 or more, the option has no value. If in six months the stock price is lower than $26 per share, then you can purchase the stock and immediately sell it at the higher exercise price of $26. If the price per share in six months is $22.50, you can purchase a share of the stock for $22.50 and then use the put option to immediately sell the share for $26. Your profit would be the difference, $26$22.50=$3.50 per share, less the cost of the option. If you paid $1.00 per put option, then your profit would be $3.50$1.00=$2.50 per share. Managerial Report 1. Build a (spreadsheet) model to calculate the profit of this European put option. (20\%) (Hint: Follow the above to create the parameters and the formulas for Model) 2. Construct a data table that shows the profit per share for a share price in six months between $10 and $30 per share in increments of $1.00. (25\%) The point of purchasing a European option is to limit the risk of a decrease in the pershare price of the stock. Suppose you purchased 200 shares of the stock at $28 per share and 75 six-month European put options with an exercise price of $26. Each put option costs $1. 3. Build a (spreadsheet) model to show the value of the portfolio with options and without options. (20\%) (Hint: Follow the above to create the parameters and the formulas for Model) 4. Use data tables to shows the value of the portfolio with options and without options for a share price in six months between $15 and $35 per share in increments of $1.00. (25%) 5. Discuss the value of the portfolio with and without the European put options. Which one (with or without) is more profitable? (10\%)

Answer & Explanation Solved by verified expert

See Answer