 Personal finance practice problem using Excel. Graphing bond price part number three. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to it, that's okay because we'll basically build this from a blank sheet. But if you do have access, there's three sections we've been going through in prior presentations, part one, part two. This presentation, part three. Three tabs in part three. Example tab, practice tab, blank tab, example, answer key. Let's look at it now. Information on the left, calculations on the right. We're gonna be looking at the bond price as time passes. As we get closer to maturity, we're gonna be graphing that out, looking something like this. This is a great practice problem for graphing, understanding bonds, understanding time value of money. Also note that the bonds have two cash flow components to it. We've got the interest payments that are happening, which is an annuity kind of structure. And then we've got that one lump sum that happens at the end when we get the face amount. So that could be a little bit confusing when we think about what's gonna be the impact on the price as we get closer to maturity. Because as we've seen in prior presentations, we broke this out first looking at a situation where we don't have any interest payments, but just that lump sum at the end and looking at the structure of the graph. In that case, as we get closer to maturity, you would think we would pay more because we're gonna get that one lump sum payment at the end. And then in part two, we grabbed an annuity situation, which is similar to a bond that doesn't have, if it didn't have that lump sum at the end and just gave the annuity payments. And in that situation, we'd get a graph looking more like this because as you get closer to the end of the annuity, you would think the annuity would be worse less because you don't have as much of a stream of payments from the annuity payments. And now we're gonna combine those together and basically graph those two things out together for a bond that has annuity payments and that lump sum at the end. Okay, so the second tab has some pre-formatted cells. So you can work through this practice problem with less Excel formatting. If you so choose the third tab, basically a blank tab with the information on the left. If you just wanna open up a new Excel sheet, you can do that and then I would select the whole sheet with a triangle, throw down your baseline formatting by right clicking on it. I would usually go to the format of the cells and then I typically go to currency, brackets, no dollar sign, no decimals. I'm not gonna hit okay, because I already have this, I'm just gonna X out of it. Then put in your data formatting cells as needed, such as the percent here and here, make a skinny C column and then we're good to go. So we've got a bond here, face value 1,000 years, 30 years. We're gonna say the rate is 10%. The discount rate is gonna be 9.5. So let's first calculate the bond price as our typical scenario, bond price. What would be the bond price given this information? Let's calculate the coupon payment down here. Coupon payment just to get that defined. That would be the 1,000 times the 10%. So we're gonna be having a coupon payment, an annuity payment $1,000, $100. Okay, let's make this black and white up top. We're gonna go to the home tab, font group, make this black and white with the bucket drop-down and the letter thingy. There we have that. Gonna make this a little bit wider and this is gonna be the present value of the interest payments. We're calculating the bond price by taking the present value of the interest payments, which would be the annuity portion, $100 for 30 years. It's annual, not semi-annual, so a little bit easier that way. Negative present value, shift nine, rate is gonna be the market rate, which we're gonna say is 9.5, comma, number of periods is gonna be 30. There's no semi-annual, so it's straightforward 30, comma. The payment is gonna be that what we calculated 1,000 times 10%, or just the 100 down here, down below and okay. And then we've got the present value of the face amount that we're gonna get at the end. Negative present value, shift nine, rate 9.5%, comma, number of periods is gonna be 30 years, comma. We don't have a payment because we're not talking annuity this time. We're going to the future value, that being the $1,000 and okay. And so, or enter, enter is okay. And so that gives us the bond price. If we sum this up, we've done this calculation before. I know I'm doing it quickly because we've seen that before. We can kind of double check it by saying, let's make this 10%, you should get the same $1,000 when you do that. So there we've got that. So let's put an underline here. Let's do some formatting action, selecting these items. Let's go to black and let's make this bordered and blue. If you don't have that blue, I'm gonna go to the color wheel. The wheel of color, color wheel, looks more like a honeycomb color wheel, but it's not all yellow like a honeycomb because honey's usually yellow. Anyways, so now we're gonna calculate, let's do that same thing. Let's make this a little smaller. Let's save some room. Let's save a little room here. Excel isn't endless for crying out loud. Let's select this one and go home and make a paint brush it, paint brush it and put that on the F to make a skinny F and we'll calculate that same bond price. Let's say year price for each year as we get closer to 30 years. So I'm gonna start at zero, one, two. We're gonna select those three and let's bring that on down for 30 years. 30 years have passed and still, and still you don't seem to, I'm still not doing too great here, but that's okay. We're gonna, I'm still here. 30 years have passed and I'm still here. So I'll tell you that that's good sign. Home tab, font group. We're gonna hit the black and white and we're gonna make this one a little skinnier like so. All right, let's calculate that one again but in such a way that I can copy it down now for 30 years. So this is gonna be negative present value shift nine. Let's pick up the rate is 9.5. That's outside of my table. So I gotta make it absolute F4 on the keyboard because I don't want it to move down. When I copy it down, I only need a mixed reference by the way, but an absolute one works and it's easier to do. So then we're gonna go to the number of periods. Number of periods is gonna be 30, but instead of picking up that 30, this is where the tricky business comes in. We're gonna take this 30 on down below minus zero up top, which is still 30. But when I copy it down, I want it to be 30 minus one or 29, 30 minus two or 28. That means the 30 can't move even though it's in the same table, can't move it. But the other one, I do want it to move down. So the G32 representing the 30 needs to be absolute F4 on the keyboard, dollar sign before the G and the 32 so that it will not move down, but the zero will move down to one making it 30 minus one or 29. Okay, so then comma, the payment that we're gonna have, the payment is going to be the $100. Now we also have to add the second component to it. So we've got to have another item here. So I'm gonna close up the brackets and I'm gonna do the second calculation just right inside here in the same place. I'm gonna start it with a negative again. So I'm gonna say minus another negative because that flips the sign to a positive on both of these. So then I'm gonna say present value shift nine. The rate is this time is gonna be the 9.5% again comma the number of periods is gonna, I'm gonna do the same tricky business. It's gonna be the 30 minus the zero. Same kind of starting point. This needs to be absolute because that's the same 9.5 that's outside of my table. Therefore F4 on the keyboard, F4. And then we're gonna say this one, we need to do the same business that represents 30. This represents the 30. So therefore F4 because I don't want it to move down and then comma. So there's no payment on this one because we're only looking future value comma again to get to the future value of 1000. That's also outside of our table. Therefore I need to make it F4 absolute dollar sign before the B and the two. So don't move down when I copy down. Whew, that was long. Let's see if it did the right thing. I gotta close up the brackets. That's okay, it's still good. So then we copy, so that looks good. If I copy it down here, does it do what we think it should? It doesn't. Why? Because this 100 moved down. So I missed an absolute. I'm gonna double click on it and say, where is that 100 right there? I believe, because that's B9, B9. So I'm gonna make that absolute F4 on the keyboard dollar sign before the B and the nine enter. Let's try it again, ultra base por favor. And now it's copying it down properly. I think it didn't change the number yet. So now it's changing. Okay, I think that's right. So let's just review it one more time. We've got these two formulas together. So I said negative present value. So here we're taking the rate, which is gonna be that 9.5. We took the 30, which didn't move because it's absolute, which is now minus the two, which is 28, which is the G4. And then we picked up the payment, which is 100. We closed up the brackets. We made another present value, calculating the face amount in the same formula. We took the rate, which is absolute ties, the 9.5. We took the 30 minus the two again, the 30 represented by the G32, G4 represented the two. We didn't have a payment this time. We got the future value of the 1000 absolutized. And there we go. So now we can copy it down and we can see that we have this change. And you've got these two kind of, remember you got these two kind of forces happening when you go down. One is that as we get closer to that imbalance here, as we saw here, when we get closer to that lump sum, you would think that the price would go up because the lump sum payment we're gonna get at the end would be more valuable. But also we've got the annuity component, the fact that basically when we, if we just had an annuity payment, the series of payments would be less as we get closer to maturity. So you'd think that would basically drop the price. Notice as you're further away from that in lump sum payment, it's not worth as much. It's not as big of an impact. And so those are kind of the forces that are at play here. So let's take a look, let's subtract this out. Let's make the difference or change. So let's say, let's say change, let's call it change. I shouldn't change. And this will be equal to this minus this. And I'll just copy that down. We could add some decimals, but I'll copy it down. So now you can see the change that is happening as we go down. All right, let's make this a little bit smaller. I'm gonna make this black and white. Let's try to graph this out on a year by year basis. I'm gonna make this blue and bordered before we do that. Let's just take, don't get ahead of yourself. You're getting ahead of yourself. I hate waiting for myself. I'm so slow. I'm so slow. It's okay. All right, are you ready, self? We're going forward now. We're going forward. I got the J column J. Let's make that a skinny. We're gonna go on column F, home tab, paint brush and make that a skinny J. And then we'll try to graph this out thusly. So I'm gonna say here, let's do our normal kind of calculation for a bond if I was to break out the payments. So I'm gonna put the total here this time so we could see the total. And typically we would do something like zero, one, two. I'd bring that out to 30. I'm gonna select those two. I'm gonna bring it on out to 30 periods on out into the future. 30 years have gone by. And we're gonna say home tab stuff's just the same. Nothing's changed. Here we go, we're gonna say black and white and black and white. And then this is gonna be the bond interest, bond interest and the face amount in terms of cash flows. We've seen this in prior presentations. So in terms of cash flows, we've got the $100 that's happening repeatedly, repetitively from an annuity standpoint in an annuity kind of basis. I'm just gonna say equals the one before it and copy that one on out to the right. Copy it on out, boom. So that happens. And then we've get the one lump sum payment that happens all the way out 30 years from now. We're gonna get $1,000, which will be worth like a penny by that point given the inflation, given the poor caretaking of the money supply that's currently in place. All right, don't, I'm not complaining about the money supply here. This is not your job right now. It's just ridiculous though. I'm telling you, it's just ridiculous. I'm gonna put zeros here. Okay, we're not doing the money supply. We're not doing that. We're doing a bond. So we're gonna say this is gonna be the total then. So the total cash flow. I'll sum these up equals the sum of these two. We'll copy that across. Copy that across. And so there we got that lump sum at the end. Let's put an underline on all of these ones. Put an underline there. So we could see it. I'm gonna make these a little skinnier too. Skinnier, let's make them thinner. So there we go. Something like that. Okay, so then we're gonna, so then we could take the cash flow or the present value. So write the present value of the cash flow, which would look something like this. Negative present value, shift nine. The rate would be over here at the 9.5. I'm gonna say F4 on the keyboard so that doesn't move when I copy it across. Comma, the number of periods is gonna be, I'm gonna be picking up the one this time. Comma, comma, and I'm just taking that $100 and present valuing it back to the current time period and enter. So we get the 91. I think everything works out there. I'm gonna copy that all the way across like so. And so there we have it. Let's go ahead and sum this up. Equals the sum here and we're gonna get our sum all the way across of the one. So there's that same calculation of the 1049. Let's bring the totals all the way up this way too. I'll total it up that way. So we've got, we're gonna receive 3,000 in interest, the face amount 1,000 at the end for 4,000 cash flow, but present valuing it, we get to the discounting at that 9.5. We get to the 1049. So now let's do this again, but in such a way so that we can see what would happen if we bought the bond a year from now and two years from now and so on. So we'll get a little bit more complex. This is great practice for your Excel tables and formatting and stuff. So let's make this blue and border. You're getting ahead of yourself. Stop getting so excited about this stuff. This is gonna be great. Okay, so we're gonna have the year here and then I'm gonna say this is the price and then I'm gonna put my years here again, one or zero, one, two. I'm gonna select those three. I'm gonna bring it out to 30 again. So we're gonna bring that out to 30 again. I'm gonna go to the home tab. Let's center that. Let's make it black and white for the header. Let's make these two black and white, like so. And then I'll bring the years on down this way too. So this is where it's getting tricky. So like this is different than what we did last time. It's gotta start at zero, one, two. Then we'll select those three. We'll bring that down 30 years, 30 years later. 30 years later, center it, make that black and white. So there we have it. So now we're gonna do our calculation here in period one. We're gonna do a similar calculation, but I wanna do it in such a way that I can copy it down. And I'm gonna be focusing when I do this on this line again, the cash flow line. So I'll make that yellow up top. I'll make that a different color so we can focus on it. So we're gonna do the same calculation we had up here down below, but we want to do it in such a way that I can not only copy it to the right, but also down. So we're gonna throw in a bit of a twist here. So this is gonna be negative, present value, shift nine. We're gonna go to the left and pick up that 9.5, F4 in the keyboard, making it absolute. That's the same because I want to be able to, it not to move when I go right or down. And then comma, this is where it gets a little tricky. I'm not just gonna pick up the number of periods with the one. That works when I copy it to the right, but I wanna be able to copy it down as well. So I'm gonna pick up the one minus the zero. And that still comes out to one. When I copy it to the right, I want it to be two minus zero, three minus zero, and then I also wanna be able to copy it down. So this one right here, which is the one, I want it to be flexible to move to the right, but I don't want it to move down. Therefore the numbers, I need a dollar sign in front of dollar sign in front of the seven. That's called a mixed reference. This one over here, I don't want it to move to the right, therefore I have to freeze it from going to the right. So I need to put a dollar sign before the letter. I do want it to move down, therefore no dollar sign before the eight. So mixed references in place. And then I'm gonna say comma. I don't have a payment because we're talking no payment here because it's not an annuity. And another comma, I'm gonna pick up that same hundred dollars here. I want that to move to the right when I go to the right. So I don't want to put anything messing up the N, but I do not want it to move down when I copy it down. Therefore dollar sign before the four, that's a mixed reference and then close that up. Now if I did that right, I should be able to copy it to the right like so. And then I should be able to copy it down and get that same 91. Now note, you could do this in a little bit of a tricky kind of way, or another way that might be a little bit easier. Like you could say if I already calculated it up here, I could copy that across and then I can copy the pattern this way by starting here and then copying that pattern to the right that way. That's another way that you can do it. But we're gonna do it with the formulas and the cells here because that's a little bit more fancy. So let's copy this to the right now. So I'm gonna copy that to the right. So there we have it. And so same numbers. I could copy it down now like this, copy it down and just double check it. So if I check it out, if I delete this one, because now I'm gonna imagine we start in on the second year and we go for 29 years up until we get to the end. Let's check our totals here. If I sum this up equals the sum of these items, we're gonna sum this up. We get to the total of that looks right. And then if I copy that down, if I copy that down, I think that's still right. If I copy it down again, let's do it again because there's not a big change. I'm gonna copy it down like so and then delete that 100 and copy this down. So now we should come down to the 1098, right? Which is this one. So I'm just gonna copy this down and then I gotta delete this cell so we can see this kind of pattern that will happen. So let's copy this all the way down for 30 periods. So we'll copy it down. I'm gonna go too far and then I'll delete some of it and then go all the way down here. I didn't go too far. Let's go two more down, two more down. Boom, like so. And then I'm just gonna delete. This is a bit of a tedious process. So I'm just gonna delete everything that's under the 91s. So I'm gonna start deleting. I'll just do it this way. I'll delete here, delete here, delete here, delete there, and then I'll delete all this stuff from here down, boom, and then I'll do it over here. Everything under the 91s. I'm gonna delete these. I'm gonna delete these, these, these, these, these, these, these, these, these, these, these, these. And then I'll delete everything underneath here. Boom, and then everything under the 91s from here, here, just trim it down. Just give us a little haircut on the bottom here. It's getting a little shaggy down there. Get rid of the shag. I want a cool little triangle thing happening. So there we've got it. So I'm gonna delete this last bit here and then I'll copy it down this way, copy our totals down, copy the totals down like so. And so there we have, so there, so there we have it. That last one is gonna be a thousand and that formula is a little messy. We'll keep it, we'll just put it at a thousand so it matches our table on the left here, right? So that matches out. Hold on, I shouldn't have done that. I'm gonna undo that. So that's the 1005, so there's the 1005 and the last bit should be a thousand, should be a thousand on the last bit. So we'll just put, I'll put a thousand on the last one just to match it out here to tie it out. So there we go. So I think this match it out to what we've got here. So now we can see it on a stream basis. So now we've broken out our cash flows if we started in the first period and then if we started in period two, period three, period four on out to the 30 periods. Okay, let's go ahead and make this blue and bordered. Blue and bordered, this fancy table. Border blue that one. So I'm gonna go border blue and then let's insert our graph here. So we'll insert, let's use this to insert the graph. So I'm gonna select these two right here. Gonna select these two and we're gonna go then to the insert and we're gonna go to a charts and I like the line graph because I wanna be picking my X and Y axes. So I'm gonna say that's the one and let's put it over here inside here in such a way that it doesn't mess anything up. So we'll bring it out a bit like right there maybe. And then I'm gonna get rid of a header and I'm gonna put the labels and hit the plus button, access labels. So we want then this axis to be the price. So I'm gonna say this is gonna be equal. So you put your cursor right on it and just say equals and it'll put a little equals up top so you can see there's the equals happening and then we'll pick up the price which I can pick up. Let's do it over here though. Let's pick up that price right there. You can see it formatting up top and then we're gonna say down here we're gonna say this axis equals so you can see the equal sign and then we're gonna pick up the year, enter. So now we have that and then I can adjust this. Let's stop it at 30 because it's not going past 30. And you also might wanna check your data by the way. I usually like checking my data by going to the select item and saying okay where's the data coming from? Here's my data series. If I edit it, it's taking the x axis which is the years that looks good. The y axis is the price. So that looks right. So I'm gonna say okay, okay. And then maybe adjust this down here. I'm gonna double click on it and we've got the items here. I'm gonna stop it at 30 for the maximum. Let's stop it at 30, tab. So it gives us a little bit wider of a chart and let's make the intervals. I think we've been making them two instead of five. Let's make the intervals two just to switch it up. And so there we have it and we'll say close that out. So I think that looks pretty good. So now we can see we've graphed the three charts. We've graphed it in terms of one, a situation where we only have that end balance that we're gonna get at maturity. Just the face amount at maturity without the interest payments, like a zero coupon bond for example. And then here we calculated an annuity kind of what the graph would look like. And then here we have those two things kind of combined here with the bond which has an annuity component to it and that value of one at the end. And of course then you can adjust your rates if you so choose. So if you wanted to make right here we've got the market rate less than the rate. So you could make it at like 15%. So now the market rate is higher than the coupon rate, the rate on the bond and you've got something that looks like this. You could then adjust these on your prior to look at the components of it as well. And so if you made this out to be 15, 15, then it would look something like this. And if you made this one out to be 15, then it would look something like this and you can kind of get a feel for what is going on with them. So you can then adjust it down below. What if it was like two or something like way down here? And then you could adjust the rate. So now it's looking more like this. And you can kind of compare that and adjust your rate over here and try to get a feel for basically what's going on with those two kind of components happening which are kind of working in opposite to each other. One being that lump sum payment at the end. The other basically being an annuity stream when you're considering the cash flow and the valuation of the bond. Okay, let's go back on over. We'll bring it back. Let's bring it back to the 9.5 across the board. So we'll say 9.5. You can also of course adjust other factors as well. If you wanted to try to calculate, adjust the coupon rate and so on. Once you've got the tables, it's put into place. Let's put it back at the 9.5 for now though. Don't mess it up. Don't mess it up. But play with it. You can play with it, but don't mess it up because I don't want it to be messed up. Let's put a border and blue around. Let's put that bordered. And let's do a spell check on it. Quick spell check. Okay, looks good.