Solution 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

Solution

Solution

1 (Solution) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53...

 Category: General Words: 1050 Amount: \$12 Writer:

Paper instructions

I need to do this homework on Excel. This this Finance course1 2 3 4 5 6 7 8 9 10 10 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 21 21 22 22 23 23 24 24 25 25 26 26 27 27 28 28 29 29 30 30 31 31 32 32 33 33 34 34 35 35 36 36 37 37 38 38 39 39 40 40 41 41 42 42 43 43 44 44 45 45 46 46 47 47 48 48 49 49 50 50 51 51 52 52 53 53 54 54 55 55 56 56 57 57 58 58 59 59 60 60 61 61 62 62 63 63 64 64 65 65 66 66 67 67 68 68 69 69 70 70 71 71 72 72 73 73 74 74 75 75 76 76 77 77 78 78 79 79 80 80 81 81 82 82 83 83 84 84 85 85 86 86 87 87 88 88 89 89 90 90 91 91 92 92 93 93 94 94 95 95 96 96 97 97 98 98 99 99 100 100 101 101 102 102 103 103 104 104 105 105 106 106 107 107 108 108 109 109 110 110 111 111 112 112 113 113 114 114 115 115 116 116 117 117 118 118 119 119 120 120 121 121 122 122 123 123 124 124 125 125 126 126 127 127 128 128 129 129 130 130 131 131 132 132 133 133 134 134 135 135 136 136 A B C D E F G H I J K L M N O P Q R Question 1 Bond Pricing a. Bond pricing with annual Interest YTM 0.04 Bond paying annual interest with the fol owing attributes: Method 3 NPV \$1,081.11 Use the NPV function Year Flow Maturity 10 Number of years 1 \$50 Coupon Rate 0.05 Annual rate in decimal form 2 \$50 Par Value \$1,000 3 \$50 Coupon \$50 Annual coupon 4 \$50 YTM 0.04 Annual rate in decimal form 5 \$50 Redemption 100 Nothing fancy, what value goes here? 6 \$50 Frequency 1 Recal : annual payments! 7 \$50 Price using PV and PRICE functions: 8 \$50 Method 1 PV (\$1,081.11) PV(. ) function. The prompt "rate" means YTM. The function returns a negative value. 9 \$50 Method 2 PRICE \$1,081.11 Here, "rate" means coupon rate. Function yields price per \$100 FV, so have to multiple by 10 10 \$1,050 You'l have to use two dates ten years apart, e.g. DATE(1,1,2014) and DATE (1,1,2024) Annuity and PV factors for this bond: Annuity fac. 8.110896 BKM p. 299-300. Some algebra. PV factor 0.675564 For "r" and "T" refer to appropriate input cel in above, not 0.05 and 10 Price using the annuity and PV factor PV of coup. \$405.54 BKM p. 299-300. Some algebra. PV of FV \$675.56 Method 4 Total PV \$1,081.11 YTM 0.04 b. Bond pricing with semi-annual interest NPV \$1,081.76 "Rate" is half-year yield An otherwise similar bond paying semi-annual interest: Half-year Flow Method 3 1 \$25 No. Periods 20 Number of half years 2 \$25 Coupon Rate 0.05 Annual rate 3 \$25 Par Value \$1,000 4 \$25 Coupon \$25 Semi-annual coupon in dol ar terms 5 \$25 YTM 0.04 Annual rate in decimal form 6 \$25 Redemption 100 Again, nothing fancy 7 \$25 Frequency 2 Now, this changes 8 \$25 Price using PV and PRICE functions: 9 \$25 Method 1 PV (\$1,081.76) The "rate" is now YTM per half year, so divide your input by 2. 10 \$25 Method 2 PRICE \$1,081.76 11 \$25 12 \$25 Annuity and PV factors for this bond: 13 \$25 14 \$25 Annuity fac. 16.351433 Tricky part: you need semi-annual 15 \$25 PV factor 0.672971 rates and the new number of periods. 16 \$25 17 \$25 Price using the annuity and PV factor 18 \$25 19 \$25 PV of coup. \$408.79 20 \$1,025 PV of FV \$672.97 Method 4 Total PV \$1,081.76 Question 2 Accrued Interest Settlement date 24-Oct-14 24-Nov-14 24-Dec-14 Maturity date 1-Sep-24 1-Sep-24 1-Sep-24 Annual coupon rate 0.05 0.05 0.05 Yield to maturity 0.04 0.04 0.04 Redemption value(% of par) 100 100 100 Coupon payments per year 2 2 2 Flat price (% of par) 108.072 108.015 107.959 Use the PRICE function Days since last coupon 53 84 114 Use COUPDAYBS Days in coupon period 181 181 181 Use COUPDAYS Accrued interest 0.732 1.160 1.575 Some algebra Invoice price 108.804 109.175 109.534 Simple addition Question 3 Cal able Corporate Bond Yield to: Maturity Cal Settlement date 15-Oct-14 15-Oct-14 Maturity/cal date 15-Oct-44 15-Oct-24 Annual coupon rate 0.08 0.08 Bond price 115 115 Redemption 100 110 Par Value \$1,000 \$1,000 Semiannual coupon \$40 \$40 Yield 0.0682 0.0664 Use the YIELD function. Hint: what is the redemption value for the cal able bond? Question 4 Perpetuity Q 4.1 \$100/year for 100 years at 10% \$999.93