 Personal finance practice problem using Excel. Zero coupon bond price calculation. 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, three tabs down below. Example, practice blank, example, answer key. Let's look at it now. Information on the left calculations on the right we're calculating the price of a bond. However, this time, unlike a normal bond, these are zero coupon bonds. So remember in a normal bond, we calculate the bond price thinking of the present value of the two streams of cash flows. One being the interest payments and annuity stream of cash flows. The second being that present value of one, the lump sum, the amount that we would receive at maturity. If we're talking about a zero coupon bond, we are eliminating then the interest portion, the annuity component of the cash flows. It would be kind of like we're loaning, for example, a company or government money and they're just gonna be paying us back instead of paying us like rent as we go, interest payments. They're just paying us the principal at the end. Now of course, given the fact that they're gonna be paying back the face amount at the end, that means that we're gonna still want a return on this. How are we going to be building in the return? Well, we're going to then purchase the bond at the beginning. We're gonna loan the money at the beginning for something less than the face amount and therefore they're gonna give us the face amount at the end and the difference is in essence, interest on the bond. So the second tab is going to be the practice tab where we have some pre-formatted cells so you can work through the practice problem with less Excel formatting. The third tab, we're gonna be doing the Excel formatting. We just have the information on the left. If you don't even have that, you can open up a blank Excel worksheet and just select the triangle up top, right click on that and lay down your baseline formatting so that you can build on top of it. Put down that foundation. We can put the currency down here. I usually go to brackets and then say no dollar sign, no decibels, I'm not gonna hit okay because I've already done this. I'm just gonna X out of it up top. Then you enter your data on the left-hand side. As you do so, format the cells as needed. For example, the percentages. And then I typically make a skinny C column and we're good to go. So let's do it. We'll do a couple examples of this. It should be actually easier than a normal bond because now we don't have two streams of payments. We only have that one lump sum we're gonna be receiving at the end, but we'll structure it just as we've done with other bonds so we can get a feel of the likeness of the differences. So we've got the face amount, the zero coupon bond. We're not gonna be getting the interest payments. The issue to yield is gonna be the 5% years to maturity are gonna be 15. And so let's just go ahead and put up top here. This is gonna be the price we'll calculate. Let's make this black and white. I'm gonna make it black and white. Black and white. Plain so we can see it. Present value of interest. Now we don't have any present value of interest. That's what we normally do with a bond because it's zero. And then we've got present value of the face amount amount that we're gonna get at maturity. And so that's all we're gonna calculate given the issue to yield in essence, the market rate. So we're gonna say negative present value, shift nine, which is gonna be the rate. We're gonna say 15. We'll say these are yearly bonds instead of semi-annual bonds. So we don't have to do any funny business changing the rate or anything like that. And we'll just say comma, number of periods is gonna be 15 years because we're gonna be talking in years not in terms of semi-annual business, comma. We don't have any payments because we're not getting any payments. We're not getting the interest payments. We're not dealing with an annuity. So two commas, comma, comma, we're going to the future value. We're gonna get that $1,000 at the end, the face amount at the end. If I'm gonna get $1,000 15 years into the future, how much am I willing to purchase the thing for now? We're talking $481. Therefore there's interest imputed in this kind of calculation because of course the difference between the 1,000 and the 181 that we actually are paying for is in essence the interest that we're kind of imputing into the calculation. So that means the bond price. So the price is of course the sum of these two, which is just there's nothing on the zero side of things because we're not doing anything with the interest payments. Let's make that black, I mean blue. Let's make it blue and bordered. We're gonna hit the bucket dropdown. If you don't have that blue, we go on the color wheel. The wheel of color, and I just pick the same color every time. I've got all these colors I can use, but I just use the same one with the blue font. We're gonna make that boarded. Let's do it again. Ultra vase pour favor with the second set of data down here. We just changed the interest rate to 9%. So we can see the interest rate climb in here. Let's calculate the price again and let's make that black and white. We're gonna do some format team home. We're gonna make that black and white on the head or, and then this is gonna be, I'll just say this equals the present value of the interest. This equals the present value of the face amount. We have no interest. I'm just putting that in place so you could see how we would normally calculate normal bonds and we're missing the interest component because it's a zero coupon bond doing our calculation here negative present value shift nine. We're looking at the rate. Now 9%, there's the change comma number of periods is still 15. We're gonna say comma, comma because this is not an annuity. We don't have any interest payments. We're just looking at that lump sum. We're gonna get at the end of the 15 years, discounting it back at the 9% $1,000 and enter. So we're now only willing to pay $275 for that bond at this point in time because you're not gonna give me that $1,000 face amount until 15 years have passed discounted at the 9%. If we price that out, sum that up equals the S to the U to the M. We get the same number, of course, because there's no, I'm putting some underlines here. Let's make that blue and bordered and then we'll do it. Uno, vase, mas, one more time. Por favor, I didn't hear no bell. I don't stop unless I hear bells, which happens randomly for some reason. I don't know why. I don't think bells actually ring. They just some chime in my head for some time telling me to stop. So I don't stop until I hear a bell. It's like Rocky, Rocky Balboa. No one even knows that movie anymore. What are you even talking about? Just do the bond calculation. So once again, we're gonna say zero. Now the interest rates up at the 12%, 12% negative present value, shift nine. The rate is gonna be now 12%, comma, number of periods is still at the 15, comma, because we're not talking annuity. We're talking about present value one. We're looking at that future value. We're gonna get 15 years later, this time discounted at 12%, which means we're only willing to pay upfront at this point to get that $1,000 15 years later, discounted 12%, $183. And that's gonna be the price that we're gonna sell. I'll give you $183 today for $1,000 15 years for now. It's kind of like wimpy. I'll give you a dollar today to pay me on Tuesday, except we hope that these people will actually pay us. We can trust these people. Trust is important in the transaction. You're gonna pay me 15 years later. Are you gonna be around? Are you even gonna be around 15 days? Yeah, they're gonna be okay then. You better not skip town. I want my $1,000 15 years from now. You can bet that I'll be knocking on your door 15 years from now, because I gave you $183 today. And you said you'd give me $1,000 in 15 years. So that's basically the idea of it. Again, it should be an easier calculation due to the fact that we don't have that two kind of cash flow streams with it, but just the one kind of cash flow stream with it. But you gotta keep that in mind when you hear that kind of bond structure, which is often commonplace if you're talking about very short-term bonds, but could also be there for other terms as well.