In the tables below, cells that have a white background are to be hard coded with data. Cells with a yellow background must be populated with formulas. You are given a 30-year bond with 4 years remaining to maturity. The Par value is $1,000 and the coupon is 9% paid annually.

In Part 1 You see you can buy the bond today at a price of $1,104.00, follow the directions in column D to calculate the yield to maturity for this bond today with 4 years remaining.

Once you calculate the yield to maturity in Part 1, in Part 2, you will calculate the price of the bond using the same yield to maturity at different points in time over the life of the bond.

Starting in cell D25, use the PV function to calculate the price of the bond at each of the maturities listed using your yield to maturity from Part 1.

Lastly, in Part 3, in J23 use the Vlookup function to allow a user to enter a number of years to maturity in Cell I23 and return the price of the bond calculated in Part 2.

File Home Insert Page Layout Formulas Data Review View Help Share Comments X Calibri 11 A A 4. abe General AY Insert 9X Delete Format Paste BIU a.Ar $ %, 80 Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Analyze Data Sensitivity Clipboard Font Alignment Number Cells Editing Analysis Sensitivity E1 X f A D E F G H J K L M N O 1 2 3 B Excel Assignment 2 In the tables below, cells that have a white background are to be hard coded with data. Cells with a yellow background must be populated with formulas. You are given a 30 year bond with 4 years remaining to maturity. The Par value is $1,000 and the coupon is 9% paid annually. In Part 1 You see you can buy the bond today at a price of $1,104.00, follow the directions in column D to calculate the yield to maturity for this bond today with 4 years remain Once you calculate the yield to maturity in Part 1, in Part 2, you will calculate the price of the bond using the same yield to maturity at different points in time over the life of the Starting in cell D25, use the PV function to calulate the price of the bond at each of the maturities listed using your yield to maturity from Part 1. Lastly, in Part 3, in J23 use the Vlookup function to allow a user to enter a number of years to maturity in Cell 123 and return the price of the bond calculated in Part 2. 4 5 6 7 8 9 10 11 Part 1: Calculating Yield To Maturity (YTM) Yield to Maturity Par = Par/face value of the bond. Coupon rate (%) = Coupon rate in % TTM (Yrs) = Time to maturity in years Price = Bond value or price 12 13 14 15 16 17 18 Coupon payment ($) = Coupon payment per period ($) = Par * Coupon rate YTM = Use Rate Function to Calculate Yield to Maturity. File Home Insert Page Layout Formulas Data Review View Help 3 Share Comments X Insert Calibri 11 A A 4. ab General 47 0 Paste BIU CA = = $ %) 2X Delete Format Conditional Format as Cell Formatting Table Styles Styles Sensitivity Sort & Find & Filter Select Editing Analyze Data Analysis Clipboard Font Alignment Number Cells Sensitivity E1 X f A B D E F H J K L. M N 19 20 21 22 23 24 25 26 27 28 29 Part 2: Calculating Bond Price with different Time to Maturity (TTM) YTM 0% Par 0 Coupon Years to Maturity Price 0% 1 Use -PV Function to Calculate the bond price with different years to maturity, but the same YTM calculated in Part 1. 0% 2 0% 4 $ 0% 5 0% 7 0% 10 0% 15 0% 20 0% 25 0% 30 30 31 32 33 34 35 36 File Home Insert Page Layout Formulas Data Review View Help Share Comment Insert Calibri v 11 AA General ce IT f 28 O 9X Delete Paste BIU A += = $ % Sensitivity 40 000 Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Analyze Data Clipboard Font Alignment Number Cells Editing Analysis Sensitivity E1 fo A B D E F G H 1 K L. M N 0 35 36 Part 3: Looking Up a Bond Price From Part 2 Using An Input Time To Maturity TTM (Yrs) Price Years To Maturity Use the VLOOKUP function to find the price of the bond from Part 2 by looking up the number of years 37 38 39 40 41 42 43 44 45 46 47