FIN3310: INTERMEDIATE FINANCIAL ANALYSIS

SPREADSHEET ASSIGNMENT: Chapter 4

1. Retrieve the file HWChapter4 and solve Problem 4-4, Problem 4-6, and Problem 4-8, Problem 4-17, Problem 4-19, and Problem 4-21 using the organizational design displayed in the worksheet file. Do not modify the fonts, format, or cell addresses.

2. Enter numerical values in cells B8 and B9 and enter equations that solve for the values required in Cells D6, B15, B17.

3. Enter numerical values in cells B26 and B27 and enter equations that solve for the values required in Cells D24, B32.

4. Enter numerical values in cells C47:C50, C53, C54, and also enter these values in the BA II Plus calculator (or a similar financial calculator). Enter the results computed by the calculator in cells E51, E55.

5. Enter numerical values in cells C59:C79 and F59:F69 and enter equations that solve for the values required in cells F80, F81.

6. Enter or compute numerical values in cells C97 and D97 and enter equations that solve for the values required in cells C101:C105, D101:D105.

7. Enter equations that solve for the values required in cells D113, D114, F121, F124, F125.

8. Enter numerical values in cells C139:C142, C147:C150, and also enter these values in the BA II Plus calculator (or a similar financial calculator). Enter the results computed by the calculator in cells E143, E151.

9. Enter equations that solve for the values required in cells C156:C167.

10. Print the worksheet.

A Problem 4-4 4% 2% 4% so IP2 = (2% +4%)/2 = 3% so IP 3 = 4% 12 = Iz = MRP = DRP = LP = 0% since Treasury security since Treasury security IT-2 = IT-3 = ? ? r= r* + IP + DRP + LP + MRP IT-2 = IT-3 = 20 Problem 4-6 r* = 3% 3% 11 = 12 3% so IP2= 6.3% IT-2 = DRP= since Treasury security since Treasury security LP MRP = IT- = r* + IP + DRP + LP + MRP MRP2 = 35 Problem 4-8 36 Semiannual payments 37 20 $1,000 N*2 = M = Coupon rate= INT/2= 8% $40 $1,100 bond price = YTM = YTC= ? 46 Using the BA II Plus calculator: N = PV = PMT= FV= (I/Y)*2 = ? YTM = Call price = (Callable N)*2 = (I/Y)*2 = YTC= 58 Using Excel's EIRR Financial Function Command Bond price at T=0 Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Period 7 Period 8 Period 9 Period 10 A 58 Using Excel's EIRR Financial Function Command Bond price at T=0 Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Period 7 Period 8 Period 9 Period 10 Period 11 Period 12 Period 13 Period 14 Period 15 Period 16 Period 17 Period 18 Period 19 Period 20 Yield *2 Yield *2 YTM= YTC = 87 Problem 4-17 89 Bond C = coupon bond Bond Z= zero coupon bond 91 90 86 87 Problem 4-17 88 89 Bond C = coupon bond 90 Bond Z= zero coupon bond Bond C Bond Z N= M = YTM = Coupon rate = INT= $1,000 9.6% 10% $1,000 9.6% 60% 99 Using Excel's =PV Financial Function Command Year Price of C Price of Z 102 103 104 105 106 107 11 108 Problem 4-19 109 DRP = 0 and LP = 0 for Treasury securities. 110 111 r* = 3% 112 8% 113 12= IP2 = 114 Iz to Is = IPs = r2 = 10% 116 r's = 10% 117 MRP2 = 118 MRP, = ? 119 5% 4% 115 ? F. 119 120 r2 =s* + IP2 + MRP2 + DRP + LP 121 MRP2 = 122 123 124 rs =r* + IPs + MRP, + DRP + LP MRP5 = Thus, MRP5 - MRP2 = 125 126 127 135 Semiannual payments Semiannual periods in 8 years 128 129 Problem 4-21 130 131 Corporate bond 132 N= 10 133 M = $1,000 134 Coupon rate = 10% INT = $100 136 137 Part a. 138 Using the BA II Plus calculator: 139 N= 140 FV= 141 I/Y = 142 PMT= 143 PV = 144 145 Part b. 146 Using the BA II Plus calculator: 147 N= 148 FV= 149 I/Y = PMT= PV = ? Semiannual rate of interest Semiannual payments PV= Semiannual periods in 8 years Semiannual rate of interest Semiannual payments PV= A Problem 4-4 4% 2% 4% so IP2 = (2% +4%)/2 = 3% so IP 3 = 4% 12 = Iz = MRP = DRP = LP = 0% since Treasury security since Treasury security IT-2 = IT-3 = ? ? r= r* + IP + DRP + LP + MRP IT-2 = IT-3 = 20 Problem 4-6 r* = 3% 3% 11 = 12 3% so IP2= 6.3% IT-2 = DRP= since Treasury security since Treasury security LP MRP = IT- = r* + IP + DRP + LP + MRP MRP2 = 35 Problem 4-8 36 Semiannual payments 37 20 $1,000 N*2 = M = Coupon rate= INT/2= 8% $40 $1,100 bond price = YTM = YTC= ? 46 Using the BA II Plus calculator: N = PV = PMT= FV= (I/Y)*2 = ? YTM = Call price = (Callable N)*2 = (I/Y)*2 = YTC= 58 Using Excel's EIRR Financial Function Command Bond price at T=0 Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Period 7 Period 8 Period 9 Period 10 A 58 Using Excel's EIRR Financial Function Command Bond price at T=0 Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Period 7 Period 8 Period 9 Period 10 Period 11 Period 12 Period 13 Period 14 Period 15 Period 16 Period 17 Period 18 Period 19 Period 20 Yield *2 Yield *2 YTM= YTC = 87 Problem 4-17 89 Bond C = coupon bond Bond Z= zero coupon bond 91 90 86 87 Problem 4-17 88 89 Bond C = coupon bond 90 Bond Z= zero coupon bond Bond C Bond Z N= M = YTM = Coupon rate = INT= $1,000 9.6% 10% $1,000 9.6% 60% 99 Using Excel's =PV Financial Function Command Year Price of C Price of Z 102 103 104 105 106 107 11 108 Problem 4-19 109 DRP = 0 and LP = 0 for Treasury securities. 110 111 r* = 3% 112 8% 113 12= IP2 = 114 Iz to Is = IPs = r2 = 10% 116 r's = 10% 117 MRP2 = 118 MRP, = ? 119 5% 4% 115 ? F. 119 120 r2 =s* + IP2 + MRP2 + DRP + LP 121 MRP2 = 122 123 124 rs =r* + IPs + MRP, + DRP + LP MRP5 = Thus, MRP5 - MRP2 = 125 126 127 135 Semiannual payments Semiannual periods in 8 years 128 129 Problem 4-21 130 131 Corporate bond 132 N= 10 133 M = $1,000 134 Coupon rate = 10% INT = $100 136 137 Part a. 138 Using the BA II Plus calculator: 139 N= 140 FV= 141 I/Y = 142 PMT= 143 PV = 144 145 Part b. 146 Using the BA II Plus calculator: 147 N= 148 FV= 149 I/Y = PMT= PV = ? Semiannual rate of interest Semiannual payments PV= Semiannual periods in 8 years Semiannual rate of interest Semiannual payments PV=