Hello i have a finance assignment that includes excel which i know nothing about excel id really appreciate it if someone would help me with it
| Q1: You have been asked to evaluate AAPL 6.25 maturing at 15th of November 2020, 2022,2024, 2029, 2034. 2039, 2044, and 2049 all with yield to maturity 2.188%, use the following excel table to draw them. | | | | | | | | | |
EHW5 Bonds Yields (1).xlsx - Trio Office Calc File Edit View Insert Format Styles Sheet Data Tools Window Help M & Abc FIA AR 1 Calibri 11 a a a a - % 00 . .00 .00 0 all A5 h = J L L A B D E F G K 4 5 6 16.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 7 maturing November 15, 2020 maturing November 15, 2022 maturing November 15, 2024 maturing November 15, 2029 maturing November 15, 2034 maturing November 15, 2039 maturing November 15, 2044 maturing November 15, 2049 Formula in column 8 9 Settlement date 15/08/2019 15/08/2019 15/08/2019 15/08/2019 15/08/2019 15/08/2019 15/08/2019 15/08/2019 =DATE 2019,8,15) 10 Maturity date 15/11/2020 15/11/2022 15/11/2024 15/11/2029 15/11/2034 15/11/2039 15/11/2044 15/11/2049 =DATE 2020,11,15) 11 Annual coupon rate 6.250% 6.250% 6.250% 6.250% 6.250% 6.250% 6.250% 6.250% 6.250% 12_Yield to maturity 2.188% 2.188% 2.188% 2.199% 2.188% 2.188% 2.188% 2.188% 2.188% 13 Redemption value % of face value) 100.000 100.000 100.000 100.000 100.000 100.000 100.000 100.000 100.000 14 Coupon payments per year 2 2. 2 2. 2 2. 2. 15 Face Value $1,000.00 2 $1,000.00 2 $1,000.00 $1,000.00 By Default $1,000.00 $1,000.00 $1,000.00 $1,000.00 =1000 16 By Default 17 18 Flat price (% of par) 104.978 112,672 120.039 137.112 152.425 166.159 178.478 189.526 PRICE B9,810,311,312,313,814) 19 Days since last coupon 92.000 92.000 92.000 92.000 92.000 92.000 92.000 92.000 =COUP DA YES (B 9.B 10 2.1) 20 Days in coupon period 184.000 184.000 184.000 184.000 184.000 184.000 184.000 184.000 =COUP DAYS 19 B10.2.1) 21 Accrued interest 1.563 1.563 1.563 1.563 1.563 1.563 1.563 1.563 =B19/B20) "B11100/B14 22 Invoice price of par) 106.540 114.235 121.601 138.674 153.988 167.722 180.040 191.089 =B18+821 23 Invoice price $1,065.40 $1,142.35 $1,216.01 $1,386.74 $1,539.88 $1,677.22 $1,800.40 $1,910.89 =(B22/100)*815 24 25 Bond Pricing 26 27 $2.000 28 29 $1,800.00 30 31 $1,600.00 32 $1.600.00 33 34 $1,200.00 35 $1,000.00 36 37 38 39 40 S4000 41 42 $200.00 43 44 01/01/2020 01/01/2022 01/01/2014 01/01/2026 01/01/2020 01/01/2030 01/01/2032 01/01/2034 01/01/2036 01/01/2038 01/01/2000 01/01/2042 01/01/2044 01/01/2046 01/01/2018 45 111 |||| Bond_Valuation Yield Bond Valuation_Explained Yield_Expained PageStyle_Bond_Valuation_Explained Sheet 3 of 4 English (USA) + 60% 2:16 PM 6/13/2020 Type here to search e a :)) ENG 9+ EHW5 Bonds Yields (1).xlsx - Trio Office Calc File Edit Edit View Insert Format Styles Sheet Data Tools Window Help Abc FIA M oo & HHH Calibri 11 a a a % 00 . .00 .00 =0 all A5 h = E J K L L 92.000 184.000 1.563 138.674 $1,386.74 F 92.000 184.000 1.563 153.988 $1,539.88 G 92.000 184.000 1.563 167.722 $1,677.22 H 92.000 184.000 1.563 180.040 $1,800.40 92.000 184.000 1.563 191.099 $1,910.89 -COUP DAYBS (B 9,8 10 2,1) ECOUP DAYS 09 810,2,1) =B19/B20) B11100/B14 =B18+821 =B22/100]*B15 A B D 19 Days since last coupon 92.000 92.000 92.000 20 Days in coupon period 184.000 184.000 184.000 21 Accrued interest 1.563 1.563 1.563 22 Invoice price (% of par) 106.540 114.235 121.601 23 Invoice price $1,065.40 $1,142.35 $1,216.01 24 25 Bond Pricing 26 27 27 $2,000.00 28 29 $1,800.00 30 $1,800.00 31 32 $1.0000 33 34 $1,200.00 35 $1,000.00 36 37 38 39 40 S4000 41 42 43 44 01/01/2020 01/01/2022 01/01/2004 01/01/2026 01/01/2020 01/01/2000 01/01/2002 01/01/2034 01/01/2008 45 46 Face Venice price 47 48 Q2:Based on your evaluation, What is the relationship the invoice price and time to maturity? 111 01/01/2013 01/01/2000 01/01/2042 08/01/2014 01/01/2046 08/01/2018 1949 - 49 50 51 03:What does it mean that AAPL is paying the same coupon rate over different matureites? 52 53 54 55 + Bond_Valuation Yield Bond Valuation_Explained Yield_Expained PageStyle_Bond_Valuation_Explained Sheet 3 of 4 English (USA) + 60% 2:16 PM 6/13/2020 Type here to search e a :)) ENG EHW5 Bonds Yields (1).xlsx - Trio Office Calc File Edit View Insert Format Styles Sheet Data Tools Window Help M & Abc FIA AR 1 Calibri 11 a a a a - % 00 . .00 .00 0 all A5 h = J L L A B D E F G K 4 5 6 16.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 6.25% coupon bond, 7 maturing November 15, 2020 maturing November 15, 2022 maturing November 15, 2024 maturing November 15, 2029 maturing November 15, 2034 maturing November 15, 2039 maturing November 15, 2044 maturing November 15, 2049 Formula in column 8 9 Settlement date 15/08/2019 15/08/2019 15/08/2019 15/08/2019 15/08/2019 15/08/2019 15/08/2019 15/08/2019 =DATE 2019,8,15) 10 Maturity date 15/11/2020 15/11/2022 15/11/2024 15/11/2029 15/11/2034 15/11/2039 15/11/2044 15/11/2049 =DATE 2020,11,15) 11 Annual coupon rate 6.250% 6.250% 6.250% 6.250% 6.250% 6.250% 6.250% 6.250% 6.250% 12_Yield to maturity 2.188% 2.188% 2.188% 2.199% 2.188% 2.188% 2.188% 2.188% 2.188% 13 Redemption value % of face value) 100.000 100.000 100.000 100.000 100.000 100.000 100.000 100.000 100.000 14 Coupon payments per year 2 2. 2 2. 2 2. 2. 15 Face Value $1,000.00 2 $1,000.00 2 $1,000.00 $1,000.00 By Default $1,000.00 $1,000.00 $1,000.00 $1,000.00 =1000 16 By Default 17 18 Flat price (% of par) 104.978 112,672 120.039 137.112 152.425 166.159 178.478 189.526 PRICE B9,810,311,312,313,814) 19 Days since last coupon 92.000 92.000 92.000 92.000 92.000 92.000 92.000 92.000 =COUP DA YES (B 9.B 10 2.1) 20 Days in coupon period 184.000 184.000 184.000 184.000 184.000 184.000 184.000 184.000 =COUP DAYS 19 B10.2.1) 21 Accrued interest 1.563 1.563 1.563 1.563 1.563 1.563 1.563 1.563 =B19/B20) "B11100/B14 22 Invoice price of par) 106.540 114.235 121.601 138.674 153.988 167.722 180.040 191.089 =B18+821 23 Invoice price $1,065.40 $1,142.35 $1,216.01 $1,386.74 $1,539.88 $1,677.22 $1,800.40 $1,910.89 =(B22/100)*815 24 25 Bond Pricing 26 27 $2.000 28 29 $1,800.00 30 31 $1,600.00 32 $1.600.00 33 34 $1,200.00 35 $1,000.00 36 37 38 39 40 S4000 41 42 $200.00 43 44 01/01/2020 01/01/2022 01/01/2014 01/01/2026 01/01/2020 01/01/2030 01/01/2032 01/01/2034 01/01/2036 01/01/2038 01/01/2000 01/01/2042 01/01/2044 01/01/2046 01/01/2018 45 111 |||| Bond_Valuation Yield Bond Valuation_Explained Yield_Expained PageStyle_Bond_Valuation_Explained Sheet 3 of 4 English (USA) + 60% 2:16 PM 6/13/2020 Type here to search e a :)) ENG 9+ EHW5 Bonds Yields (1).xlsx - Trio Office Calc File Edit Edit View Insert Format Styles Sheet Data Tools Window Help Abc FIA M oo & HHH Calibri 11 a a a % 00 . .00 .00 =0 all A5 h = E J K L L 92.000 184.000 1.563 138.674 $1,386.74 F 92.000 184.000 1.563 153.988 $1,539.88 G 92.000 184.000 1.563 167.722 $1,677.22 H 92.000 184.000 1.563 180.040 $1,800.40 92.000 184.000 1.563 191.099 $1,910.89 -COUP DAYBS (B 9,8 10 2,1) ECOUP DAYS 09 810,2,1) =B19/B20) B11100/B14 =B18+821 =B22/100]*B15 A B D 19 Days since last coupon 92.000 92.000 92.000 20 Days in coupon period 184.000 184.000 184.000 21 Accrued interest 1.563 1.563 1.563 22 Invoice price (% of par) 106.540 114.235 121.601 23 Invoice price $1,065.40 $1,142.35 $1,216.01 24 25 Bond Pricing 26 27 27 $2,000.00 28 29 $1,800.00 30 $1,800.00 31 32 $1.0000 33 34 $1,200.00 35 $1,000.00 36 37 38 39 40 S4000 41 42 43 44 01/01/2020 01/01/2022 01/01/2004 01/01/2026 01/01/2020 01/01/2000 01/01/2002 01/01/2034 01/01/2008 45 46 Face Venice price 47 48 Q2:Based on your evaluation, What is the relationship the invoice price and time to maturity? 111 01/01/2013 01/01/2000 01/01/2042 08/01/2014 01/01/2046 08/01/2018 1949 - 49 50 51 03:What does it mean that AAPL is paying the same coupon rate over different matureites? 52 53 54 55 + Bond_Valuation Yield Bond Valuation_Explained Yield_Expained PageStyle_Bond_Valuation_Explained Sheet 3 of 4 English (USA) + 60% 2:16 PM 6/13/2020 Type here to search e a :)) ENG
