Bond valuation in Excel

- The value of any asset is simply equal to the present value of its expected future cash flows. Using the investor's required rate of return is the discount rate. Bonds are no different and we can use Excel to do quick valuations on them. Let me show you how. I'm in the 0303 Begin Excel sheet. Now what you see is we've just created very basic chart, showing some of the main features in bonds. Maturity, coupon, par, yield. Then the value. Based on the value, we're going to figure out what the price is. So because we haven't filled in any of our inputs yet, price doesn't have a value. Now let's just imagine hypothetically that we have a bond with 10 years to maturity. That bond, let's say, has an 8% coupon. Well if it has an 8% coupon, how much is it going to pay every year in payments? Well that's going to depend on the par. So it'll be 8% times the par value, which we'll add below. So I'm going to type equals par times .08. Now what's our par going to be? Let's just say $1000. And then finally we have the yield. The yield is the rate of return that we expect on these bonds. Let's say that it's 8%. Which I'll represent as .08. How do we figure out the value of that bond? Well, we can use the present value formula in Excel. Equals pv, we first have to start by putting in our rate. What's the rate return of this bond? Well it's the yield. So I'm going to include my yield for rate. My number of periods is going to be 10. My payment is the coupon that I get each year. And then the future value is 1000. And then finally there's one more option which asks, are the my interest payments received at the beginning or the the end of of the period? Generally bond coupons are paid at the end of so we'll put in zero. When we do that, what do we get for the value of the bond? Well, it's negative $1000. Now the reason it's negative is because Excel says this is a cash outflow of $1000. You're going to receive an inflow of 80 bucks each year plus receive $1000. So in order to receive that money you have to pay an outflow of 1000. Well I want my price to show up as a positive number so I'm going to multiply this whole present value formula by negative one. When I do that I get a price of 100. This bond is trading at 100% of par. Now what happens if we vary these metrics a little bit? Let's pretend the yield goes down to 6%. What happens to the value of the bond? Look at that. Now it pops up to 114.72. And I'll make this a little easier to read by putting my cursor in the cell and then hitting control plus one and changing this to be a number with two decimal points. So the value of the bond increased as the yield for the required rated of return decreased. What happens if yield increases? Let's say it goes from that original 8% up to 10. Well now the value of our bond drops to $877 or $87 and 71 cents. Now all of this assumed that we got paid a coupon once a year. Many bonds have coupon payments twice a year but instead of being paid a full coupon, we get paid half of the coupon. So rather than getting 80 up front once a year, maybe we 40 every six months. Well, in that case we'd have to go through and divide our coupon by two and we'd also have to double the maturity, making that 20, because now we have 20 6-month periods instead of 10 1-year periods. And our yield, which is our required rate of return, is also for six months instead of a year so it's half the size. So we put the yield formula in as 0.1 divided by 2 and now you see we get essentially the same price for the bond as we had before. So changing from a 6-month coupon interval to a 1-year interval doesn't really matter too much as long as the other figures stay the same. The other figures are much more important in deriving the value of that bond. Now you should be all set to use Excel to do some basic bond valuation on your own.