Question

1. Suppose there is a 2-year bond that pays an interest payment of $70 at the end of the first year and then pays another interest payment of $70 plus the $1,000 face value at the end of year two when it matures. The interest payment is known as a “coupon” payment, and therefore, bonds that pay interest are known as “coupon bonds”. Since this bond pays its interest payments once per year, it is known as a 7% annual coupon bond. Notice that 7% of the $1,000 face value of the bond is equal to the $70 coupon payment.
   
   1. The yield to maturity of a bond is the interest rate that equates the present discounted value of a bond’s cash flows to its price. If this bond is selling for $950, find the yield to maturity of this bond algebraically.
      Answer to a: YTM = 9.88%
      
   2. Set up the cash flows for this bond in Excel. Then use Solver to check your answer to part b making sure to keep the Solver solution before you save and submit the Excel file. Please submit your Excel spreadsheet that has your Solver solution in a worksheet labeled “Q3”.
      Excel Note: Recall that Solver is an “Add-In” that may not be loaded into Excel on your computer.
      

Please answer only question b and please show the formulas used in Excel.

Answer 1

Step 1- Build a excel template as shown belowsStep 2- Enter the cash flows, for year 1 it is same as Coupon amount and for year two it is Coupon+ Maturity value

SStep 3- Calculate the DCF using the formula DCF for year 1 = Cash flow for year 1/(1+YTM)^1 and for 2nd year ash flow for year 2/(1+YTM)^2.

Step 4- PV equals to the total cash inflows in year 1 and year 2

Step 5- Calculate the PV using PV formula as shown below in the formula bar

Step 6- Validate cash outflow(price of the bond) is equal to total cash inflows

Step 7- You can also calculate the YTM by using the Rate formula in excel as shown in the formula bar

Step 8- Finally add solver to excel(go to file then option then add-ins then in Manage go to Excel Add-ins then tick Solver Add ins and click ok. Solver is available under Data tab under Analyze part. Click on the solver- under set objectives- add PV of the bond(next to the PV formula), in the "To" option, put -950(Cash outflow) and in "By Changing Variable Cells:" add YTM of the bond(Next to the Yeild). and then click solve

Now you can see, if you change the Price by any amount, your yield will change, PV will change and also cash inflow will change. But cash inflow will be always equal to the cash outflow.

That means value of a bond is always equal to the sum of discounted future cash inflows of the bond.