Posted: November 18th, 2015

Finance Assignment Using Excel

Finance Assignment Using Excel

1.    John invested $40,000 in Google, $60,000 in IBM, $20,000 in Devon Energy, $20,000 in Starbucks, $10,000 in Waste Management and $20,000 in Abbott on January 1, 2010. [8 points]
a)    Using daily stock returns, find the VaR of John’s portfolio at 99% confidence level over a day between January 1, 2010 and December 31, 2014.
b)    Replicate the same exercise for 3 days.  Show your work.

2.    The data for US Treasury Strips are given as of November 15, 2014. Assume that time zero is November 15, 2014. Please calculate and draw the yield curve (6 months, 1 year,, 2 years,.., 30 years) [ U S TREAS NT STRIPPED]. [12 points]
a.    Using these securities, find the implied forward rates between maturity dates of these securities (i.e., from 0 months to 6 months; 6 months-1 year;…).
b.    Calculate the three duration models for each security. For each type of duration, draw the graph of duration vs. time to maturity. Show your work in excel.
c.    Assume that US Treasury issues a new 2 year bond with 5% semi-annual coupon payments. What is the fair price of this new bond? What is the duration of this bond?
Issue
Price
Coupon(%)

Maturity
U S TREAS NT STRIPPED PRIN PMT 15-May-2015
99.950    0    15-May-15
U S TREAS NT STRIPPED PRIN PMT 15-Nov-2015
99.860    0    15-Nov-15
U S TREAS BD STRIPPED PRIN PMT 15-Nov-2016
98.200    0    15-Nov-16
U S TREAS BD STRIPPED PRIN PMT 15-Nov-2018
93.420    0    15-Nov-18
U S TREAS SEC STRIPPED INT PMT 15-Nov-2023
74.810    0    15-Nov-23
U S TREAS SEC STRIPPED INT PMT 15-Nov-2033
46.240    0    15-Nov-33
U S TREAS BD STRIPPED PRIN PMT 15-Nov-2041
32.380    0    15-Nov-41

3.     Use Excel to draw separate profit diagrams of the following portfolios in increments of$0.01 for spot prices between $10 and $40. Find numerical values for the kinks, flat profits (if there is any), and the breakeven points. Label them on your graphs. Effective annual interest rate is 8.33%. All options will expire in 3 months. Current stock price is $20.[12 points]
a.    Portfolio 1: Buy four20-strike calls and sell three 25 strike calls. When will this strategy be beneficial?
b.    Portfolio 2: Buy  two20-strike put, buy two 20-strike call. When will this strategy be beneficial? What is the name of this strategy?
c.    Portfolio 3: Buy stock at $20 and buy 20-strike put. What is the name of this strategy? What is the purpose?
d.    Portfolio 4: Short stock at $20 and buy 20-strike call. What is the name of this strategy? What is the purpose?
Strike    Call Premium    Put Premium
15    2    0.5
20    1    0.75
25    0.5    1.25

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET A GOOD DISCOUNT 🙂

Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00
Live Chat+1-631-333-0101EmailWhatsApp