money and banking assignment 3
The Answers should be submitted as a single spreadsheet file (Excel, Google Sheets) with separate tabs for each question (Q1, Q2, Q3, Q4). Your spreadsheet should show how you calculated each value.
Question 1 (20 points)
To answer all parts of question 2, prepare a spreadsheet for each question, showing:
â€¢ Three columns for each question
â€¢ The first column should contain the years (2020, 2021, 2022, 2023â€¦2038, 2039)
â€¢ The second column should contain cash payments (undiscounted) in each year
â€¢ The third column should contain present value (ie: discounted) of cash payments in each year
â€¢ At the bottom of the third column, provide the total present value of all cash payments
â€¢ A statement at the bottom of each table with the answer to the question (Example for 1A: â€œThe present discounted value of this lottery prize is _____.â€)
AWhat is the present value of a lottery prize paying $25,000 each year for 25 years (first payment in 2020, last in 2044), discounted at a rate of 5 percent?
BWith an interest rate of 3.3 percent, what is the present value of a security that pays $1,769 next year (2020) and $10,422 fifteen years from now (2034).
CWhat is the present value of a security that pays $500 in 2020 and $1,000 in 2021, $1,500 in 2022, $2,000 in 2023 and $2,500 in 2024with an interest rate of 7 percent?
DOn January 1, 2019 you purchased a bond with a face value of $3,000,000 that provides an annual coupon (paid on January 1) of 2.75 per cent and a yield to maturity of 4.0 per cent. The bond matures on January 1, 2039. Assuming you paid no more or less than the present value, how much did the bond cost you?
EOn January 1, 2019 you purchased a bond with a face value of $2,500,000 that provides an annual coupon (paid on January 1) of 7per cent and a yield to maturity of 1.5 per cent. The bond matures on January 1, 2024. Assuming you paid exactly the present value, how much did the bond cost you?
Question 2 (10 points)
You sell each of the bonds in (1D) and (1E) on January 2, 2020 (after you have received one coupon payment the day before). Each interest rate has fallen by 1 per cent, for what price did you sell each bond? What rate of return did you earn on each bond?
To answer question 3, prepare a spreadsheet for each of the bonds, showing:
â€¢ Three columns for each question
â€¢ The first column should contain the years (2021, 2022, 2023â€¦2038, 2039) ÃŸNote that for this question, the first year is 2021!
â€¢ The second column should contain cash payments (undiscounted) in each year
â€¢ The third column should contain present value (ie: discounted) of cash payments in each year
â€¢ At the bottom of the third column, provide the total present value of all cash payments
â€¢ Calculation of the rate of return when each bond is sold on January 2, 2020
â€¢ Four statements with the answers to the question (Price of bond 1D on January 2, 2020, rate of return on bond 1D, price of bond 1E, rate of return on bond 1E)
Question 3 (20 points â€“ show tables for each)
What is the duration of:
Bond AA 20 year bond (2039) with a face value of $3,000,000, coupon rate of 5.5 per cent and yield to maturity of 3 per cent?
Bond BA 6 year bond (2025) with a face value of $5,000,000, coupon rate of 5 per cent and yield to maturity of 8 per cent?
Bond CA 10 year bond (2029) with a face value of $7,000,000, coupon rate of 2 per cent and yield to maturity of 10 per cent?
To answer question 3, prepare a spreadsheet for each bond, showing:
â€¢ Year (2020, 2021, 2022, 2023â€¦2038, 2039)
â€¢ Cash payments in each year (coupon and/or face value)
â€¢ Present value of cash payments in each year (coupon and/or face value)
â€¢ Total Present value of all cash payments
â€¢ Share of total present value in each year (present value of that yearâ€™s cash payments divided by the total present value of all cash payments)
â€¢ Weighted maturity for each year (year times the share of total present value in each year)
Whatâ€™s the duration of a portfolio holding all three bonds?
â€¢ Find the total present value of the entire portfolio of three bonds
â€¢ Find the share of the portfolioâ€™s total present value represented by each bondâ€™s individual present value
â€¢ Take the sum of:
(Bond Aâ€™s share of portfolio present value * Bond Aâ€™s duration)
+ (Bond Bâ€™s share of portfolio present value * Bond Bâ€™s duration)
+ (Bond Câ€™s share of portfolio present value * Bond Câ€™s duration)
Question 4 (50 points â€“ 2 points each)
Copy and paste this table into a spreadsheet. Show your calculations. Assume all face values = $100
– To find the price use the table format from question 1
– To find the duration, use the table format from question 3
– To find the coupon for R, youâ€™ll have to do some algebra. Hereâ€™s a formula (C is coupon, P is price, F is face value of $100 and n is the number of years to maturity)
– Finding the value of the interest rate requires some algebra too. You figure it out!
Fill in the blanks marked with letters (â€œA = ______, B = ______, etc.). Assume the year is 2019 and coupons are paid annually (so the next payment is Jan. 1, 2020).
Issuer 
Redemption 
Coupon 
Price 
Yield 
Duration 
Belgium 
2021 
1.50 
A 
0.63 
1.99 
Belgium 
2047 
1.60 
126.00 
0.59 
B 
Canada 
2022 
0.50 
C 
1.57 
2.98 
France 
2020 
0.25 
100.88 
D 
1.00 
France 
2025 
0.50 
106.17 
0.51 
E 
France 
2048 
2.00 
F 
0.57 
23.44 
Germany 
2020 
0.25 
100.95 
0.69 
G 
Germany 
2025 
0.50 
H 
0.72 
5.93 
Germany 
2048 
1.25 
I 
0.02 
25.29 
Greece 
2025 
3.00 
J 
1.45 
5.60 
Italy 
2021 
0.35 
100.98 
0.14 
K 
Italy 
2028 
4.75 
L 
0.73 
7.76 
Italy 
2048 
3.45 
135.12 
1.87 
M 
Japan 
2022 
0.10 
N 
0.31 
3.00 
Japan 
2023 
0.60 
103.71 
0.32 
O 
Japan 
2028 
1.90 
P 
0.26 
8.42 
Japan 
2047 
0.80 
113.70 
0.29 
Q 
2021 
R 
101.80 
0.59 
1.99 

UK 
2025 
2.00 
S 
0.40 
5.73 
UK 
2028 
6.00 
T 
0.52 
7.57 
UK 
2047 
1.50 
110.61 
1.06 
U 
US 
2020 
1.75 
99.82 
V 
1.00 
US 
2023 
2.25 
102.15 
1.69 
W 
US 
2028 
5.25 
X 
1.76 
7.61 
US 
2047 
2.75 
111.19 
2.21 
Y 