Finance using excel

Name_________________________________________

Please give best answers to all questions on the with use of Excel, Excel sheets need to be attached when you submitted. Please write the formula you use in excel and also attach the excel answers.

a) With interest rate 8%, calculate the PV of following cash flows.

Year	Payment	PV
1	1,000
2	1,000
3	1,000
4	1,000
5	1,000

b) You borrow $10,000 today with interest rate 7%, and you plan to pay the bank back with 8 equal annual payment. Please fill up the following table.

Principal

Payment

end year	Interest	Principal
1
2
3
4
5
6
7
8

1. a)Please calculate NPV and IRR for both projects, and fill the blank which project you decide to say yes.

Discount rate	12%
Year	Project A	Project B
0	-1000	-800
1	500	420
2	500	420
3	500	420
4	500	420
5	500	420
6	200	300
7	350	300
8	360	300
9	200	450
10	200	260
11	200	260
NPV
IRR

Which project will you make a decision? Project A or Project B?

b)Please calculate the following cash flow and show us your decision about which project should be chosen and why.

Date	Cash flow A	Cash Flow B
1-Jan-02	-10,000	-13000
1-Jul-02	3,000	3500
1-Jul-03	3,000	4000
1-Jul-04	3,000	3500
1-Jul-05	3,000	4000
1-Jul-06	3,000	4000

2. Statistics and Regression Analysis

a) Please calculate average, variance and standard deviation for annual return column.

Annual

return
31-Dec-98	34.13	0.92
31-Dec-99	30.81	0.96	-8.89%
29-Dec-00	26.25	0.99	-12.59%
31-Dec-01	30.10	1.01	18.51%
31-Dec-02	34.27	1.01	17.21%
31-Dec-03	38.08	1.01	13.06%
31-Dec-04	44.66	1.01	19.93%
30-Dec-05	43.22	1.06	-0.85%
29-Dec-06	50.06	1.14	18.46%
31-Dec-07	52.43	1.20	7.14%
31-Dec-08	42.73	1.30	-16.02%
Average return, E(rK)
Variance of return, σ2K
Standard deviation of return, σK

b) Please show your regress analysis table and fill form for slope, intercept and R-square.

	Return for the month
Date	S&P 500	IBM
3-Jan-07
1-Feb-07	-2.18%	-6.98%
1-Mar-07	1.00%	2.42%
2-Apr-07	4.33%	8.44%
1-May-07	3.25%	4.70%
1-Jun-07	-1.78%	-1.27%
2-Jul-07	-3.20%	5.14%
1-Aug-07	1.29%	5.83%
4-Sep-07	3.58%	1.95%
1-Oct-07	1.48%	-1.42%
1-Nov-07	-4.40%	-9.10%
3-Dec-07	-0.86%	2.78%
2-Jan-08	-6.12%	-0.92%
1-Feb-08	-3.48%	6.71%
3-Mar-08	-0.60%	1.12%
1-Apr-08	4.75%	4.83%
1-May-08	1.07%	7.66%
2-Jun-08	-8.60%	-8.42%
1-Jul-08	-0.99%	7.97%
1-Aug-08	1.22%	-4.51%
2-Sep-08	-9.21%	-3.92%
1-Oct-08	-16.83%	-20.51%
3-Nov-08	-7.48%	-11.74%
1-Dec-08	0.78%	3.13%
2-Jan-09	-8.57%	8.90%
Slope
Intercept
R-squared

3. Bond

a) Please calculate YTM.

Date	Bond cash flow
15-Dec-09	-1,000.00
15-Dec-10	80.00
15-Dec-11	80.00
15-Dec-12	80.00
15-Dec-13	80.00
15-Dec-14	80.00
15-Dec-15	80.00
15-Dec-16	1,080.00
YTM of bond

b) Compute the price for following bond with YTM of 5%.

Date	Payment
1	80
2	80
3	1,080
Bond price

4. Stocks Valuation

ABC company’s current FCF is $2,000,000, it will grown at 20% for the first 4 years and back to a steady growth rate 7% after 4 years. The WACC is 10%, outstanding shares is 4,000,000. Please use FCF model to estimate the value of their stock. Show you steps and calculations in Excel. Assume all FCFs happen at the end period.

Current FCF	2,000,000
Anticipated growth rate, years 1-4	20%
WACC	10%
Long-term growth rate, after year 4	7%
Number of shares outstanding	4,000,000

5. Please fill the following chart.

Suppose you write a IBM call which was sold for $7.00 on Apr 9 2013 with exercise price X=$100, on Expiration date July 19,2013, given different possibilities of market prices, please fill the following chart. Hint: Profit/ loss is not payoff.

ST: Market price

of IBM

Will call

buyer exercise

Dollar

profit/loss

to call writer
0
80
90
100
110
120
130
140
150
160
170
180
190