Please give best answers to all questions on the exam with use of Excel, Excel sheets need to be attached when you submit the exam. The exam is open material, but you may not consult in any way with others.
-
Please write the formula you use in excel and also attach the excel answers.
-
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 |
-
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 |
Payment |
Interest |
Principal |
|
1 |
||||
|
2 |
||||
|
3 |
||||
|
4 |
||||
|
5 |
||||
|
6 |
||||
|
7 |
||||
|
8 |
-
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 |
-
Statistics and Regression Analysis
-
Please calculate average, variance and standard deviation for annual return column.
|
Date |
Price |
Dividend |
Annual |
|
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 |
-
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 |
||
-
Bond
-
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 |
-
Compute the price for following bond with YTM of 5%.
|
Date |
Payment |
|
1 |
80 |
|
2 |
80 |
|
3 |
1,080 |
|
Bond price |
-
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 |
Attachments:
Final Exam SU….docx