 Personal finance practice problem using Excel. Graphing bond price part number one. 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 a little bit more action going on down below. We got part one, part two and part three. But here we're focusing in on part one, which has three tabs. Example, practice blank, example, answer key. Let's look at it now. Information on the left, calculations on the right. We're gonna be looking at the relationship between the year or as time passes and the bond price. We're gonna be wanting to graph that out. Now with a normal bond, that gets a bit confusing because we have two things going on from a cash flow perspective. One, on annuity payment, the interest payments that we're receiving on a periodic basis. And two, the face amount that we're receiving at the end. So to break this out, first we're gonna look at, in essence, basically a bond, which is like a zero coupon bond. So that we can look at just the end payment that we're going to be receiving, graphing that out. And then in part two, in a future presentation, we'll basically look at an annuity, which is kind of like similar to a bond that just had basically the payments and not the principal at the end. So we can graph that stream of payments out, which is a little bit more complicated. And then we can put those two together and think about a bond, which has those two streams in place, graphing it out, looking at the relationship between as time passes, as we get closer to maturity and the price of the bond. Okay, so let's first go back on over to the example tab and then we're gonna go to the practice tab. This has some pre-formatted sales. So you could work through the practice problem with less Excel formatting. The blank tab is blanks, except for the data on the left. If you don't even have that, you could just open in Excel worksheet. You could select the whole worksheet. I would lay down the baseline formatting first by right clicking on it, format the sales. I would go then to currency, bracketed numbers, dropdown, no dollar sign, no decimals. I'm not gonna hit okay because I already have this. I'm gonna X out of it, then add your data, change the sales as necessary. For example, percentage sales here, put a skinny C down and then we're ready to go. So we've got a bond. We're gonna say the face amount is 1,000. It's a 30-year bond, annual payments. The rate is zero, basically a zero coupon bond. This is basically, and we might not always have zero coupon bonds that are that long, like a 30 year, but our idea here is to do this so we can compare it to like a long bond and we can have a graph comparing as time passes and what would be the impact on the bond price. So then we got the discount rate, in essence, the market rate. So typically, if we have a bond, then that doesn't have any interest that it's going to be paying out, then it's gonna have to sell the bond at a discount because that's what's gonna incentivize people to buy the bond so that they get the face amount at the end or maturity of the bond and no interest payments. So that means we're gonna put less money upfront and we're gonna get the full amount at the end maturity. Okay, so let's go ahead and first calculate the bond price. So I'm gonna go up in D here and say this is the bond price and let's just do it for year one this time. So if I was just to purchase the bond, I'm gonna make this a little larger. Let's make this black and white by going to the home tab, font group, bucket drop down, black and white and we'll do our normal calculations for the bond. We normally have the present value of interest, interest, interest payments, but we don't have any. So it's zero because we're not gonna get any because we're not gonna get any interest payments. All we have then is the second part of the bond price, present value of the face amount, which we're gonna discount back using the 9.5% way out 30 years out into the future. So it's gonna be pretty low. So we're gonna say this is negative present value, shift nine, the rate is gonna be that 9.5. It's not semi-annual, but annual. We don't need to do anything funny there, comma, number of periods. We're just gonna pick up the 30, comma, comma, because it's not an annuity. So we want the future value, which is the thousand dollars we're gonna get in the future and enter. That's the 66 dollars. So it's probably got some decimals. Does it have some decimals there? There we got, so we got the bond price is then for year one is the sum of these two, adding it up. So we'd have to put 65, 70 down or so in order to get the 1,030 years out into the future because it's way out there in the future and we're not getting any interest payments. So let's go ahead and make that bordered and blue. So I'll hit the dropdown. If you don't have that blue, it's in the color wheel. Standard, there's the blue that I'm gonna use. The standard blue. So now let's do that same thing, but now let's imagine we go from 30 years out and we bring it all the way down to each year closer to 30 years, 30 years. So I'm gonna then, let's make another skinny C column or make a skinny F that's like the skinny C. It's the skinniest C. So home tab font painter, format painter, skinny F, which is now skinny. And we're gonna say, let's say years, let's say year and price. We want year and price calculation. The price is gonna be easy to calculate because we just need to do this one here, the present value as the years pass as time goes by as we get closer to maturity. Why aren't you close to maturity yet? I don't, maybe I'm just different because whatever font group time passes and you're not getting any closer to maturity. Whatever, so any case, we're gonna say then this is gonna be zero, one, two. We'll take those three, we'll copy it down to 30, putting our cursor on the fill handle, bring that on down to 30 and then let's center that. And let's just do our calculation right here again. We just need to do this one, the second one here. So we can just say this is gonna be present value shift nine. The rates that 9.5 comma number of periods is gonna be 30 comma, wait a sec, number of periods, let's do the number of periods this way. I wanna get to that 30 but I'm gonna do it this way. I'm gonna go all the way down and say we wanna take 30 minus the zero, 30 minus zero because that'll allow us to copy it over, copy it down. So in other words, this first one is outside of the data, it's over here. I wanna make that absolute because I don't want it to move down when I copy it down. So I'm gonna say F4 on the keyboard, dollar sign before the B and the seven, you only need a mixed reference but an absolute one works. But this one, I want the 30 to remain the same subtracting zero and I want the zero to move down. So it would be 30, 29 and so on and so forth. So I'm gonna say this'll be F4, this one making it absolute minus the zero and then comma, comma to get to the future value and the future value is this 1000, that 1000's outside of our data set. Again, when I copy it down, I don't want it to move down. Therefore absolute reference dollar sign before the B and the two, you could do that with an F4, selecting F4, enter. So there we have it, we should get to the same number if I add some decimals. So we got the same number there. Let's just say, without year one, we'll say take that off. So we got period, at the first point of purchase, 65.70. So then I can copy this down. I could just double click on the fill handle button, see if it does what we think it should. So I'm gonna open this up a bit. So let's double click on the next one and check it out. So now we've got the negative present value. We've got the rate, which is that 9.5, it looks right. Then we've got the number of periods, which is now 30 minus the one, which is 29 periods. And then we've got the future value, which is the 1,000. So that looks good. So now as we get closer to maturity, because really, like if I was two years out, then I'm closer to getting that $1,000 at the end. So you would expect the price to go up. Because as I get closer to maturity, I'm willing to pay more for the investment, because I'm closer to getting that lump sum payment, the full amount at the end, the 1,000 at the end. So you can see, if you look at that kind of component of a bond, then we're willing to pay more and more. And by the time we get to period 30, we've got the full $1,000 that we're willing to pay because now it's at real time by the end. Okay, so let's graph that out then. Let's graph that out and see what it looks like on a graph. Is it a straight line kind of relationship? In other words, we could say, look at the difference here. The difference, let's make that black and white. Let's make that black and white. And I'll make that a little larger. And I could say, let's take a look at the change, which is gonna be the 71.94 minus the 65.70. Let's make that a little wider, or a little, add some decimals and copy that down. So here's the change that is happening. So you can see it's not exactly a straight line relationship we have here. So we might wanna graph it out. Let's graph it out, let's check that out. So I'm gonna select these two data sets here. Gonna select these two. And we're gonna go up top and go to the insert. And I like to choose the charts so that I can actually graph out the X and Y axes. And I usually choose this one that has little dots and the line. So let's pick that one. And so if I pull that over, let's say pull it over here somewhere. So now we can check it out. And so I'm just gonna delete the title so that we have a little bit more room. And then I like to look at the data just to see what the data, see if I got the right data set. So here's the data set. If I edit it, I could say, is that picking up what we want? We've got the X axis are the years. That makes sense. The years at the bottom, the Y axis is the price. The price related to the years, that looks good. I'm gonna close that out and say, okay, here. So that looks pretty good. I might wanna add the axis titles now. So I usually hit the plus button there. We're gonna say plus button. And let's add the axis titles. So now there's the axis. Now you can click on this axis right here. And you can't really tell right now, but it'll allow you to enter a formula by saying equals. And so you can see up top, you've got the equals now. And I want this to be the price. So now it's got this formula. If I hit enter, it'll populate there. So it's a little tricky to see it that it's working that way until you actually hit the enter. But this is the same thing. I'm gonna say enter or equals. There's the equal sign and this is the years. And enter. So there we have that. And then you can play with basically like this end, this end point right here. Maybe I don't want it to go up past 30 because I'm gonna stop at 30. So if I click on this item, we get the data on the right. We want the three bars. I'm gonna stop this at 30 and say tab. And so now you can see it widen out a little bit. So that looks pretty good. And then you can also adjust how many periods you want. Like so it's got every five here. You could say the major. Let's say we put it up to like every two years or something like that. And you can adjust the table thusly. But let's keep it at that too. We'll keep it at the two. And you could see closing this out, making this a little bit wider. That looks pretty good. And you could see that as we get towards the maturity, then the price is going up. But again, the relationship isn't basically a straight line as we're getting closer to the maturity. And that's assuming that we got the same basically discount rate that we're using the whole time. And we're just adjusting of course, the timeframe, the level until we hit maturity. So if we're at 10 years here then, if we're 10 years in, we're closer to that maturity date, which is why we would expect to pay more because we expect it to mature in less time than the 30 years that we had basically up top. Okay, so that's that relationship. Now also, when we look at the other kind of thing, which we'll take a look at next time, which will be the annuity payments like the interest payment portion of the bonds, you would expect the chart to look something like this, right? Because as time passes, you're going to get less interest payments if you're talking about a series of payments. And then you got to kind of combine those together to see what the relationship is with regards to the bond itself that has kind of both of those components combined within it. So you got to keep both those things kind of together when you're trying to think about what is the impact over time with the bonds? Because you got those two kind of cash flows that are taking place. Okay, so we'll do those in future presentations. Let's go ahead and put some brackets around this and make it blue and bordered. I'm going to make this bordered and blue. So that looks good. And then of course, you could adjust, say, the discount over here. You could say, well, what if it was like four or something like that? And you could see the impact on our curve here. What if it was zero? Then we'd have the straight line. If it was then eight, you could see the impact on the curve. So we're going to bring it back up to the, what do we have it at 9.5? I think it was 9.5. So there it is. Let's do a quick spell check. We're going to go to the review up top spelling and check out the spelling. Looks good. Okay.