Money and Banking & Present Discounted Value Questions

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? Order now and Get 10% Discount! Use Code "Newclient"
[promo2]