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
UK	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

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"