Finance Excel Assignment

01_assignment.xlsx

Data - Fact (prices & rates)

01 Question: What is the expected spot price for peanuts?				NAME:
Stephen: Stephen: The expected return is the sum of the probabilities * peanut prices	02 Question: What is the current spot price for peanuts?
Stephen: Stephen: According to CAPM the discount rate is the risk free rate + Beta * risk premium. The spot price is the present value of expected price for peanuts.	03 Question: Create a payoff table and payoff diagram for a long forward and a short forward on a peanut contract with a forward price of $65 and the possible peanut prices listed in the Data - Fact worksheet.
Forward Price	$65.00
	Future Possible Peanut Prices
States of Peanut Prices Stephen: Stephen: Use the transpose function to list the possible peanut prices.
Long Forward Payoff
Short Forward Payoff
Chart Here. One series for the long forward and the other series for the short forward.
04 Question: Create a portfolio consisting of a long position in peanuts and a short forward in a peanut contract with a forward price of $65. List the assets in the portfolio, the value of the assets and the total cost of the portfolio. Then create a payoff table showing the portfolio's final payoff in all possible states.
Assets in Portfolio	Long in Peanuts	Short Forward	Portfolio Value
Value of Portfolio Assets
	Future Possible Peanut Prices
States of Peanut Prices
Long Peanuts
Short Forward Payoff
Portfolio Payoff

05 Question: If the forward price is $ 65 then is there a risk free arbitrage opportunity? If there is an arbitrage opportunity then list the assets in the portfolio and detemine if you taking a long position in the asset or a short position. Determine the total cost of the portfolio. If you are recieving funds from the portfolio then make the cost negative.
Forward Price	$65.00		NAME
Assets in Portfolio		Portfolio Value
Value of Portfolio Assets
06 Question: Based on your answer from question 5 create a payoff table for your portfolio.
	Future Possible Peanut Prices
States of Peanut Prices
Portfolio Payoff
07 Question: Using Goal Seek determine the forward price that will cause the arbitrage opportunity to disappear. Copy your answer below and reset Questions 5's forward rate back to $65
Hard copy forward rate here.
08 Question: What is the forward price using the formula you learned in class?
09 Question: Does the forward price equal the expected spot price?
10 Question: Determine the Beta that will set the forward price equal to the expected spot price. Besure to set the beta in the Data-Fact worksheet back to 3 after answering this question!!. Stephen: Stephen: I would suggest using goal seek to answer this question.
Hard Copy Beta value here
11 Question: At what discount rate will the forward price on peanuts equal the expected spot price on peanuts. (Hint look at question 10) Stephen: Stephen: What is the discount rate when you use Question 10's Beta?