Please answer only question b and please show the formulas used in Excel.
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.
Get Answers For Free
Most questions answered within 1 hours.