Calculating the Yield of a Coupon Bond using Excel
In this article, we're going to talk about how to calculate the yield of maturity for a coupon bond. For a coupon bond, we're talking about a bond that's going to pay periodic interest payments. So it's different from a zero-coupon bond where you just pay a price and then you get the face value back later and it's for a larger amount than it was discounting. We're talking about interest payments every six months or every year and that's going to be in addition to the face value of the bond. That you get back later so we'll talk about that in our example.
What is Yield?
So just to remember a yield to maturity in case you haven't watched our last article on zero-coupon bonds, the yield of maturity is the rate of return that you would receive on a bond if you buy it at the current price and then hold it until the bond matures. So that rate of return is called the yield or yield to maturity.
An example of the Yield of a Coupon Bond:
So let's walk through an example to make it a little bit easier for you to understand. So let's say that you bought a five-year bond with a coupon rate of 4% and let's say that it made annual interest payments this isn't some kind of semi-annual thing. So that 4% what you do is multiply that by the $100,000 (which is the face value of the bond) and that means that there's going to be an annual interest payment of $4000, right? 
The price of your bond when you buy it on the market you're paying $92,227. So what you want to say what you want to find out here is what's the rate of return if I pay this $92,227 amount and then I get the interest payments + that face value back in five years? What is my rate of return on that investment?
The Yield of a Coupon Bond Formula:
So you could come up with a really complicated formula basically where the bonds price is a function of basically the coupon payment and the face value and we talked about how the yield to maturity fits into this. 
So we'd have the bond price over here $92,227 and then we've got the coupon payment of $4000 then we're going to multiply that by (1 over the yield of maturity) and then in parentheses here we've got this big thing we've got 1 minus and then we've got a fraction with 1 over (1 + the yield to maturity) to the fifth power because there are five periods. So that's the (n) is n is the number of periods and then this FV we're talking about the face value.
So we're just taking the face value and then discounting it. So basically when we have this piece here we're just discounting the face value of that payment of $100,000 that we are going to get 5 years from now, we're discounting that back.
Then over here we're discounting the annuity right this is an annuity we're calculating the present value of the annuity of the interest payments. So basically we have to solve for this yield to maturity which you see comes up several times in this formula.
Microsoft Excel Formula:
This is going to be very hard to solve mathematically now if you want to solve it you can just use Microsoft Excel. Excel has a really nice function. If you're trying to solve this problem you would just type it into a cell in Excel,
you type in equals rate and then you just type it exactly like this. Now that five is the number of periods so if this was what you said I want to see if there's a 7-year bond you put in seven instead of five. So it's just a number of periods and then you've got the amount of the coupon payment which is four thousand dollars and then you've got the upfront investment that's negative bond price, right? That's what you're paying out that's a cash outflow so it's a negative number here and then what you get back the face value later you're getting a hundred thousand dollars. So you just use this formula right here use this Rate formula in Excel and that will tell you that the yield to maturity on this bond is 5.84%.
