Spreadsheet Exercise: Problem 14.20 row Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. John has been following the stock market very closely over the past 18 months and has a strong belief that future stock prices will be significantly higher. He has two alternatives that he can follow. The first is to use a long-term strategy - purchase the stock today and sell it sometime in the future at a possibly higher price. The other alternative is to buy a three-month call option. The relevant information needed to analyze these alternatives is presented below: Current stock price Desires to buy one round lot Call strike price Call option premium $49 100 shares $51 S2 To Do a. In scenario one, if the stock price three months from now is $58: 1. What is the long-position profit or loss? 2. What is the breakeven point of the call option? 3. Is the option in or out of the money? 4. What is the option profit or loss? b. In scenario two, if the stock price three months from now is $42: 1. What is the long-position profit or loss? 2. What is the breakeven point of the call option? 3. Is the option in or out of the money? 4. What is the option profit or loss? Solution a. What is the value of this corporate bond today? 1. What is the long-position profit or loss? 2. What is the breakeven point of the call option? 3. Is the option in or out of the money? 4. What is the option profit or loss? Long Position No Option 3-Month Call Option on the Stock Today: Lot market value Lot value at strike price Call premium on the lot Scenario 1: $58 Stock price three months later Expected lot market value Lot value at strike price Call premium on the lot Breakeven point Call option is Profit (Loss) Holding period return In scenario two, if the stock price three months from now is $42: 1. What is the long-position profit or loss? 2. What is the breakeven point of the call option? 3. Is the option in or out of the money? 4. What is the option profit or loss? $42 Scenario 2: Stock price three months later Expected lot market value Lot value at strike price Call premium on the lot Breakeven point Call option is Profit (Loss) Holding period return Spreadsheet Exercise: Problem 14.20 row Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. John has been following the stock market very closely over the past 18 months and has a strong belief that future stock prices will be significantly higher. He has two alternatives that he can follow. The first is to use a long-term strategy - purchase the stock today and sell it sometime in the future at a possibly higher price. The other alternative is to buy a three-month call option. The relevant information needed to analyze these alternatives is presented below: Current stock price Desires to buy one round lot Call strike price Call option premium $49 100 shares $51 S2 To Do a. In scenario one, if the stock price three months from now is $58: 1. What is the long-position profit or loss? 2. What is the breakeven point of the call option? 3. Is the option in or out of the money? 4. What is the option profit or loss? b. In scenario two, if the stock price three months from now is $42: 1. What is the long-position profit or loss? 2. What is the breakeven point of the call option? 3. Is the option in or out of the money? 4. What is the option profit or loss? Solution a. What is the value of this corporate bond today? 1. What is the long-position profit or loss? 2. What is the breakeven point of the call option? 3. Is the option in or out of the money? 4. What is the option profit or loss? Long Position No Option 3-Month Call Option on the Stock Today: Lot market value Lot value at strike price Call premium on the lot Scenario 1: $58 Stock price three months later Expected lot market value Lot value at strike price Call premium on the lot Breakeven point Call option is Profit (Loss) Holding period return In scenario two, if the stock price three months from now is $42: 1. What is the long-position profit or loss? 2. What is the breakeven point of the call option? 3. Is the option in or out of the money? 4. What is the option profit or loss? $42 Scenario 2: Stock price three months later Expected lot market value Lot value at strike price Call premium on the lot Breakeven point Call option is Profit (Loss) Holding period return
