# Calculating the Yield of a Coupon Bond using Excel

**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?

**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:

**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?

**$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?

**$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.**

**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.

**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:**

**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%.**

Now that’s rounded so it might be a little off if you actually calculated by hand but what does this means is that if you bought this bond for this price and then you held it to maturity you held it the five years and got the interest payments got the face value and so forth that you would **earn a rate of return on that investment of ****5.84%.**