In this workout, we've been asked to calculate the yield to maturity of a coupon bond. And as we've learned, it's not possible to calculate the yield to maturity directly for bonds with multiple coupon payments but instead, we have to use iteration. In other words, we have to change the desired rate until the calculated bond price matches the given bond price. Unfortunately, we won't have to do the iteration by hand but we can take advantage of Excel's Goal Seek function here. But first things first, let's build the simplified bond pricing tool using the PV function. And the face value, again has to be set at 100% as we want to calculate the bond price in percentage terms. Number of periods this time is 10, as we are looking at a five-year bond, but with semi-annual coupon payments. So there are 10 payments in total. The desired rate, we can leave blank in this case as we're effectively looking for it. And the payment is linked to the 4% coupon rate. But we have to remember to divide the coupon rate by two as we need the payment per period. And a period in this particular case is half a year. And now let's apply the PV function as we've done before. So negative PV. Then we need the desired rate, which is blank. The number of periods is 10, the payment per period and of course, the face value and the bond price in this case is calculated at 120%, which makes sense because if you leave the cell of the desired rate empty, Excel automatically interprets this as a desired rate of 0%.

The next step is to calculate the yield to maturity that gives a bond price of 103.45%. And as mentioned earlier, we're going to use goal seek for that. So let's select Data, What-If Analysis and Goal Seek. And this window should pop up. And what we want Excel to do is to set cell C13, which contains the bond price to a value of 103.45%, which is a bond price that have been given to us in this workout. And the whole thing should be achieved by changing cell C10, which is the cell that contains the desired rate. Hit OK and Excel iterates the desired rate for us. As a side note, if the goal seek result does only get close to 103.45% when you do this, you might have to increase the number of iterations in Excel. And this could be done under File, Options, Formulas. So we calculated the desired rate to be 1.62%. Now, to get from the desired rate to the yield to maturity, there's only one step left. And that is to multiply the desired rate by two as the desired rate is given per period and yields are expressed as per annum rates. And there we go. The yield to maturity for this particular bond should be 3.25%.