1. 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.
|
Year
|
Principal
beg. year
|
Payment
end year
|
Interest
|
Principal
|
|
|
|
|
|
|
|
1
|
|
|
|
|
|
2
|
|
|
|
|
|
3
|
|
|
|
|
|
4
|
|
|
|
|
|
5
|
|
|
|
|
|
6
|
|
|
|
|
|
7
|
|
|
|
|
|
8
|
|
|
|
|
2. 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
|
3. Statistics and Regression Analysis
a) Please calculate average, variance and standard deviation for annual return column.
|
Date
|
Price
|
Dividend
|
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, s2K
|
|
|
|
|
Standard deviation of return, sK
|
|
|
|
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
|
|
|
|
|
|
|
4. 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
|
|
5. 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
|