 Personal finance practice problem using Excel. Dabentures that are callable 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, in essence, answer key, let's look at it now. We've got a debenture type of bond. We're gonna be calculating the price on it as per usual. But this time added wrinkle, added problem, added issue, it's callable, it has a callable component to it. What does that mean? That means that the corporation or issuer of the bond, typically a corporation oftentimes has the capacity to call the bond at that amount. What's that going to do to the bond price when we're thinking about the market price or us on the investment side of things? The market price is generally gonna basically be capped at that callable amount because you would assume that we're not gonna be paying more than the callable amount if the bond could be called at that amount, so it's kinda acting as a ceiling. The second tab here, the practice tab, having pre-formatted cells on the right so you can work the practice problem with less Excel formatting. The third tab just has the information on the left. If you only have a blank sheet, you can just lay down your baseline formatting and add the data on the left. I would do that by hitting the triangle, right-clicking the selected area, format the cells. I'll typically be choosing the currency, brackets, and then no dollar sign, no decibel. I'm not gonna hit okay because I already have this. I'm just gonna X out of it. Then add your data on the left, adjusting cells as necessary, such as the percentages, make a skinny C column, and we're good to go. So we got the debentures. We got the face amount, $1,000. We got the coupon rate, which is the 14%, the yield to maturity, or in essence, market rate, the rate that's not on the bond determined by the market for kind of related bonds, we would assume 8%, due in years 15, but it's callable at the 1,000, 4,10, and they are semi-annual bonds. So basically that callable feature, we can think about it as saying, okay, well, that's gonna have an impact when we do a comparison, we're gonna try to compare to relatable bonds and judge what we think the rate would be, but then we got that kind of ceiling that we would expect to be having in terms of we're not gonna be paying more than the callable amount because the corporation can call it back at that amount. So we just do our normal kind of price calculation then, and then apply the ceiling concept. So we're gonna say the price, let's go ahead and make that black and white, home tab, font, group, black and white. We'll do our normal bond calculation, which is gonna be the present value of interest and the present value of the face amount at maturity. At maturity. Let's make this a little bit wider and we've done this before, so I'll do it fairly quickly here. So we're gonna say negative present value shift nine rate that we want, that's gonna be the market rate or the yield to the maturity. That's the yearly yield, however, and this are semi-annual, therefore I'm gonna divide it by two. And then I'm gonna say comma, we got the number of periods, the number of periods is gonna be 15 years, but we need semi-annual periods, six month periods, half year periods. So we're gonna multiply it times two to get to 30, not divide, multiply, just multiply. That would get us to the same spot, times two. And so then comma, the payment amount would be the thousand dollar amount here times the coupon rate, but that's usually expressed in terms of a yearly rate and we're gonna get semi-yearly payments. So we're gonna divide that by two and enter. And then we'll do the present value of the face amount, bringing that back to the current period. Present value shift nine, the rate is gonna be the 8% once again, the yield market rate. We're gonna divide it by two though, because that's a yearly rate. We need the semi-yearly and then comma, the number of periods. We're gonna say it's 15, but once again, we wanna multiply that times two this time to get to 30, half year periods, 36 month periods, 30 semi-annual periods, comma. No payment this time, because we're looking at not an annuity, going to the future value, that being the $1,000. And okay, let's sum it up equals the S to the U to the M. Sum, give me an S, give me a U, give me an M. So, sum sum, let's go. That's not how you spell prime. Anyways, we're gonna make this black. We're gonna do our formatting here. Home tab, font group, blue. If you don't have that blue right there, it's in the more colors. Give me some more, let me see that color wheel. Let me see that color wheel standard. We're gonna go to that one right there, okay. And then we're gonna go to the font group, hit that drop down and put the all borders. Give me the borders on everything. Where do you want the border to go? I want it on everything, all the borders. Everything has a border. So basically then we're gonna cap that price, price, let's call this price before call. And then we're basically gonna cap it, right? Price after call and we're gonna say, well, if that comes out to something over this number, then we're gonna cap it at that number in essence, because the company can call it back there. But if it's under that number, then we'll keep the price what it is. So we can use then kind of a logic function then if this number is greater than this number than that number, right? So we can do something like that. I'm gonna use a logic like this. There might be an easier one to do, but I'm gonna say this equals if brackets, if this number is greater than this number, then that's a comma, then what do you want it to do? Well, then I want you to take the smaller number, but comma, if it's not true, then I want you to take this number, meaning if that's smaller. So I think that'll work for our logic test might not be the cleanest or fastest way to do that, particularly, because if they're close together, we could do a min calculation just so you know, like I could do it this way. I was thinking just for Excel formatting purposes, you could say, well, if that was the price before, and this is the callable number, hold on a second, this was the callable number, and you were using fancy Excel-ness and you wanted it to do automatic calculations, you can put them on a side by side basis, one next to the other or on top, on top below basis, you can do it on a top below basis, and then take the min of those two. So that's an easier function to use. So that's another option that you could opt for. You can opt for that option if you want. So just for Excel formatting purposes, that's why I'm showing you. Font group, brackets, put some underline, put an underline right there. We can say that this one, put here, and then, and so then if this amount, the callable was less than, let's test out our formulae. If this was like 1200, then it would pick out the lesser of here, and I've got to go over. If this was like 16 call option, then it picks this one. It's working as it should, as far as I can tell. Let's put an underline here. Now, if you wanted to add like a text into your thing, you might type out something like this and say, here's my little blurb on Y, and it's all in one cell, you know? So then you can try to expand it to multiple cells. So you might try to like expand it out here, and you could merge the cells, and then you might want to put it up on the top here, and then I'll left align it, and then we'll wrap the text. So that's one way that you can add basically the text to it. I don't always like doing that unless I have to, because now you got that big cell which kind of messes everything up, but we'll put it there for now. So there's our little blurb, if you wanted to say a blurb. The call price will keep the bonds from getting too much over that price. Investors will not generally buy the bonds at the market price when there's a risk they will be called away at the lower price is our explanation for the action taken.