In this workout, a bond with exactly five years to maturity and annual coupon payments of 2.4%.

Currently trades at a price of 99.86%.

What is the bond's yield to maturity? Well, let's have a quick think about this. High level.

Here are the cash flows, and we're going to receive 2.4% every single period.

That's the coupon rate.

In the final period, we still get that 2.4%, but we also get the par value back of 100.

Great. So what kind of yield are we earning? Well, it's obvious that we're earning, at the very least a yield of 2.4%, but there's a second return or yield. Investors are receiving.

We're buying the bond for 99.86%, but then at the end of the five years, we get to sell it for a hundred.

That increase in the price there, that's a second return to us, that's a capital return.

So the 2.4%, that's pretty close to the yield to maturity, but we also need to include this capital return.

There are two ways we can do this.

The first way is to use the rate function.

So I'll go down here, let's do the rate function, and it asks me firstly, for the N per.

Now, N per is the number of periods we're going to have five.

It then asks me for the PMT or the payment, the payment, each period is the coupon 2.4%.

It asks me for the present value of the bond, IE, how much am I buying it for? Well, that's going to be a negative 99.86 'cause it's a cash outflow.

And then it asks me for the fv, the future value, how much will I receive back at the end? And that's the 100%.

So if I close the brackets, ah, the maturity is slightly higher than that 2.4% we saw earlier.

It's 2.4301.

Now there's a second way to do this and it's using goal seek.

What we need to do is we need to present value all of these cash flows.

And if we then sum 'em up, it should give us the price of the bond.

Luckily, we already know what the price should be. It should be 99.86%.

So what we could do if we already know the price it should be, is we need to work out the discount rate, the discount rate to use when we're present valuing and that discount rates, that's the yield to maturity.

So let's start doing the discounting. I'll take the cash flow and I'll divide it by one plus.

Oh, but I don't have a discount rate. So we're Gonna gonna link to this empty cell.

It's got zeros in it at the moment, and I'll lock onto that.

We'll come back to it in a second.

Close brackets to the power of the period that we're in.

So at the moment, there won't be any discounting happening.

Copy that down and then sum it up.

Ah, unfortunately it comes to a little bit greater than the 99.86 that I was hoping for.

So what I could do is I could start iterating.

I could start putting in yield to maturities in here. So maybe 1% and I'll see what happens to this orange price.

See if it comes down to 99.86%. Let's presser.

Oh, it did come down, but unfortunately not enough.

So I'll try a higher yields maturity of 2 cents comes down again.

Great. Getting a bit closer now, I'll try 3%.

Oh, I've overshot a little bit too far.

So I'm going to use golde on this cell here.

And what it will do is it will put in lots and lots of guesses into this cell and then try and get this orange cell to be exactly 99.86%.

It will then work out the perfect yield to maturity.

So I can get goal seek by going to data what if analysis, and then goal seek.

Now it's asking me what cell I want to set.

I want to set this orange cell here.

What value do I want to set it to? I want it to set it to this 99.86%. So that's going to be 0.9986.

By changing which cell? Well, I want it to change this yield to maturity.

At the moment, I've just hardcoded a 3% into it.

Excel will now try loads of numbers in there.

So when I press, okay, it's going to change this 3% and try and get this orange cell down to 99.86%.

So let's press, okay, fantastic.

It's got the 99.86%.

And what discount rate or yields maturity did we use? R 2.43 cents, exactly the same as the rate function.

So two ways for us to get to the yield to maturity.