 Hello and welcome to the video and audio instructions for Excel Worksheet C. This worksheet is teaching you two things. First, it's teaching you about the concept of a fixed cell reference. That's the first tab. And second, it's teaching you about how to create a model that calculates the value of a coupon bond. You should be learning about coupon bonds in the chapter that you're covering presently. First, the idea of a fixed cell reference. What do we have? We have a little example. We have two stores. The store is selling candy, gum, cigarettes, lotteries, and magazines, and it's selling $151 worth of those. And the idea is that perhaps you're thinking about opening a second store. And you want to, you think that your second store is going to be in a much better location than your first store, and it's going to do more sales. And you don't know how many more sales, but you want to be able to think about what would be your total value if it's doing two or three or four or five times sales. And you want to be able to create a model that shows you that it can easily multiply the sales multiple just by typing a quick little number. So what you want is you would like to populate this column with the sales of store B as a multiple of the sales of store A, and you'd like the worksheet to be able to adjust that multiplier easily and quickly. So what can you do? Well, you need to create a formula that says, OK, so this cell reference up here is the multiplier. And we want it to say, OK, you want candy will be five times, in this case, the sales in store B will be five times the sales in store A. So we type equals, still candy, times five. That is going to work. Five times 25 is 125. Now here's the issue. What you want to probably do is this is not the right thing to do, but here's what you might want to do. You click on that, you copy it, and you think you're going to paste it down. And you go like this way, paste it down, and lo and behold, nothing happens. Well, why didn't it happen? Well, it's because you didn't understand the concept of a fixed cell reference. What you just did is this one worked because it's B12 times F8. But remember what happens when you copy and paste a formula in Excel is it moves the cell references wherever you move the formula. For example, if you copy this cell and you paste it over here, it moves everything down one cell. Notice B12, F8, B13, F9, continuing B14, F10. The cell reference is moving down this way as you copy. That works great for these over here because that's what you want. But that's not working over here. You would like a way to tell Excel, hey, listen, in my formula, sure, move down with me over here, but don't move with me over here. And the way that you do that in Excel is you use the dollar sign. So let me delete this, and I'll show you the correct way to enter the formula. You type equals this cell, and then you can click on this cell, but whoops, equals this cell, times this cell. But before you finish, go up here, put a dollar sign in front of the F and a dollar sign in front of the 8. That tells Excel to fix that cell reference. So when you copy and paste it, it will not move and it will just stay right where it is. Let's see. Now we can copy it, highlight, paste. There we have it. See Excel, if you move down, Excel has kept. I'm going to use the arrow key. Now keep your eye up here on the formula bar. Excel has kept the F8 the same, even though it's changing the rows as I copy and paste it. On the side note, if this confuses you, don't worry, you're not going to need to use it. But if you only put the dollar sign in front of one of those instead of both, say the F or the 8, it will fix either the row location or the column location. For example, if you put the dollar sign only in front of the 8, if you copy and paste up and down, it will fix the reference. But if you copy and paste left and right, the reference will still vary. In other words, it will vary along the column, but it will be fixed along the row. But just so you know, you can do that if you ever need to. Again, we can copy this here, paste that there. That's the idea of a fixed cell reference. Okay, coupon bonds. I want you to really understand how a coupon bond works. So we have one sheet that's going to show you how to understand the value of a coupon bond when you see each payment that the coupon bond is making. And then you have another worksheet that's a simplified formula for understanding the value of a coupon bond. So the long way though should get you to the intuition of what a coupon bond actually is. So in the instructions say, calculate the value of a coupon bond with 10 years to maturity given the face value of 1,000, coupon rate of 4%, and interest rate of 5%. So right off, we can type in the interest rate here, 5%, 0.05, coupon rate is 4%, the face value is 1,000. And this is the 10-year coupon bond. That means that we're going to get 10 payments. We're going to get one payment every year since, as you're going to learn in the chapter, since the face value is 1,000 and the coupon rate is 40, we are going to get a, I'm sorry, 4%, we are going to get a $40 coupon payment every year. We're going to get the coupon rate times the face value. So we can, the cash flow in the first year is going to be the coupon rate times the face value. It's going to be $40. And we can just type that in there. Although we'd like to make it a model, we'd like to make it work. So if we change the coupon rate, it works. So rather than just typing it in there, what we'll do is we'll type equals face value times the coupon rate. Enter. Now, remember, we want to, this is, as you're going to learn about coupon bonds, every year you're going to get the coupon payment. And we want to copy that down. But in order to make that copy and paste work, we should fix that cell reference. So we'll go up here. And a handy dandy trick for adding the dollar signs is just to type F4 in the F keys at the very top of your keyboard. F4 adds dollar signs like that. F4 adds dollar signs like that. Whoops, I messed that up. F4, F4, Enter. Now we can copy this formula down. And I've messed up the formatting. I don't like that. So what I can do here is I can copy this format and I'm going to teach you something else. Highlight the same range. And go to, sorry, Copy. Then right click. Then go to Paste Special. Then go to Formats. And then type OK. And that didn't work. Why did that work? It should have worked. Try that again. Copy. There. That worked. Paste to Formats. I don't know why it didn't work the first time. When you copy and paste, if you go to Paste Special, you can paste just the formatting of a cell, rather than the cell contents itself. It's often a helpful trick to get the formats looking right. OK. Now that we have the cash flows right, oh, we realized it's not quite right because when you're going to learn the present value, the cash flows from a coupon bond, you're going to get the interest payments every year. But in the 10th year, you're going to also get the principal back. So we need to adjust this formula. We need to add the face value. OK. So that's the cash flow from a coupon bond. So the total, if you add this row up, that would be the total cash flow that that thing's going to throw off. But in order to get the current value of the bond, we need to take the present value of each of those. So I'm not going to fill the rest of the table out for you, but what you need to do is you need to create a formula that calculates the present value of each of these cash flows given the year that they occur. Then, at the interest rate of 5%. Then, that should be a nice formula that you can copy and paste down, but you're going to have to use your fixed cell reference idea from before, and you should construct it so that it works with a copy and paste. Then you're going to get numbers in here, and each number is going to represent the present value of each separate cash flow. The total value of the bond is equal to the sum of the present values of each payment. So since you've discounted everything back to the present, you simply add all those things together, and that's going to give you the current value of the bond. So this, what's going to be in this cell right here is just the sum of everything that's in these cells. And these cells are the present value of each payment. The present value of the first cash flow after one year added to all these, for example, the present value of the seventh, of the $40 and seven years. And at the end of the $1,040 and 10 years, you take each of those present values, then you add them up, and that's going to give you the value of the bond today. Now, that's kind of long and tedious, but it's really important that you understand how the present value calculation works and why it is that, structurally, this is how a bond works, or a coupon bond anyway. The next section is going to get you to create a model that's not so tedious. You still have interest rates, years to maturity, coupon values and face value, coupon rates and face values in there, but there is a formula that works to calculate the price without going through all the steps of discounting each cash flow separately. In order to figure that out, you're going to need to use the formula, the appendix to chapter four. You're going to have to do a little extra thinking and reading on that. I have to tell you a clue. The formula to the chapter four is only part of the answer. It's not the whole answer, and let me give you an extra hint just in the audio tutorial. The formula for chapter four is going to give you the present value of each of the coupon payments because the formula at the end of chapter four is giving you the present value of a series of payments. What it does not do is give you the present value of the face value, that you have to add at the end. Now that's a big hint, so that's all I'm going to give you. Those are the instructions for this worksheet.