Question

Please use Excel

1. Consider a 12-year ordinary annuity that pays $2,500 per month with the first payment made one month from now. If the appropriate discount rate is 14 percent compounded semiannually, what is the value of this annuity 3 years from now?

2. Consider the series of uneven cash flows below:

   End of Month	June	July	August	September	October	November
   Cash Flow	$230,000	$160,000	$275,000	$320,000	$25,000	$773,000

   If the effective annual rate (EAR) is 8.3 percent, what is the future value of the cash flows at the end of November?

3. A 5-year deferred annuity makes quarterly payments beginning 2¼ years from now. If the present value of the annuity is $5,900,000 and the discount rate is 3.17 percent compounded quarterly, what are the quarterly payments?

4. A 30-year 6.75 percent bond makes semiannual interest payments. If the bond currently sells for $1104.53, what is its yield to maturity (YTM)?

5. Audra purchased a 2.4 percent bond which settled on 4/6/2020 and matures on 4/6/2035. If the bond makes semiannual interest payments and currently sells for $859, what is its yield to maturity (YTM)?

Answer 1

1.

The annual equivalent rate of 14% semi-annual compounding is (1+0.14/2)^2 = 14.49%

Using an PV function in excel

nper = 144

rate = 0.1449/12

PMT =2500

We get PV = $170,275.19

The value of this annuity 3 years from now, we use FV function in excel

rate = 0.14/2

nper =6 ( Since value is to be found 3 years ie. 6 semi-annual compoundings)

PMT=0

PV=170,275.19

We get FV = $255,537.15

2.

Since, 8.3% is the effective annual interest, we convert it to monthly interest

0.083=(1+y/12)^12

Solving using excel solver, we get y=8%

Now,

	Month	End of the month cash flow	FV Factor	Value at November end
6	June	230000	1.033780751	237769.5726
7	July	160000	1.02693452	164309.5233
8	August	275000	1.02013363	280536.7481
9	September	320000	1.013377778	324280.8889
10	October	25000	1.006666667	25166.66667
11	November	773000	1	773000
			Total value	1805063.4

3.

In excel, we follow the following steps

i) Make 29 quarters column and enter a random number as an annuity after Quarter 9

ii) Make all the quarter equal to the quarter 9 column value

iii) Calculate the PV at Quarter 9, and then the PV from Quater 9 to PV today.

Using PV function, rate=0.0317/4, PMT=number of quarters

iv) In the solver function, Set target cell (Present value cell) equal to -5900000 and by changing cells ( Quarter 9 annuity cell)

v) After using the function, we get annuity = $343733.96

Present Value	-5900000
Quarter
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	343733.9665
10	343733.9665
11	343733.9665
12	343733.9665
13	343733.9665
14	343733.9665
15	343733.9665
16	343733.9665
17	343733.9665
18	343733.9665
19	343733.9665
20	343733.9665
21	343733.9665
22	343733.9665
23	343733.9665
24	343733.9665
25	343733.9665
26	343733.9665
27	343733.9665
28	343733.9665

($6,334,407.05)

Value of annuities at t=2.25

Hence, the quarterly payment = $343734

4.

Using the YIELD function in excel

Settlement_date = 04-Apr-20

Maturity_date = 04-Apr-50

Rate = 0.0675

pr = 110.453

Redemption = 100

Frequency = 2

We. get yield =6%

YTM on the bond = 6%

5)

Similarly to question 5, using YIELD function in excel

Settlement_date = 04-Jun-20

Maturity_date = 04-Jun-35

Rate = 0.024

pr = 85.9

Redemption = 100

Frequency = 2

We. get yield to maturity =3.63%