 Personal finance practice problem, use in Excel. Bond price calculation for multiple bonds. 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. 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, calculation on the right. We're going to be purchasing multiple bonds this time and we're looking to calculate the bond price for the multiple bonds. The second tab, practice tab, pre-formatted worksheet on the right hand side. So you could work through the practice problem with less Excel formatting. The third tab, just the information so we can practice the Excel formatting on the right. If you don't even have this, that's okay because you could just open up a blank sheet, lay down your baseline formatting on the blank sheet. I would typically select the triangle to do so and then right click, format the cells and then I usually go for the currency, bracketed numbers, no dollar sign, no decimals. I'm not gonna hit okay because I already have this. I'm gonna X out up top, then add your data, format the cells as needed. For example, percentages here and here. Make a skinny C column and we're good to go. So we got the bond purchase. We got the number of bonds. This is the new thing. We're purchasing multiple bonds. Now typically, if you purchase multiple bonds, they're gonna be the same in nature, oftentimes broken out into thousand dollar increments as far as the face amount of the bonds. So what we'll do typically is just calculate the bond price for one bond, for example, and then we will just multiply it times 120. That's usually the easiest way to kind of go about multiple bond purchases, seeing them as singular units or units of the same kind and multiplying them out. So the interest rate is 12% on the bond coupon rate, years to maturity, 20, it's a semi-annual bond, therefore pays out every six months, every half years semi-annually. We've got the market rate at the 9%. So let's calculate the bond price for a singular bond and then we'll simply multiply it out by the number of bonds that we purchased. So we'll put the bond price up top. We've done this a few times. So I'll do it a little bit more quickly here. We've got the price. Let's call it bond price, bond price, James bond price. Hold on a second, bond of bond, bond price. Okay, I don't know what else I was thinking there. And then I'm gonna select these two up top. We're gonna go to the home tab up top. We're gonna go down to the font group, hit the black and white here. We're gonna take the present value of the interest payments and the present value of the principal first assuming a singular bond. So we've got the present value of interest, present value of the print of the, let's just present value of the face amount at the end of the bond at maturity. Let's make this a little bit larger and we'll do our present value calculations, negative PV shift nine for the annuity, the series of interest payments. We're gonna be picking up the rate which is going to be the 9%, the market rate, but that's a yearly rate and these are semi-annual bonds. So I'm gonna take that, divide it by two for the semi-annual half-year rate, comma, the number of periods is gonna be 20 years, but these are semi-annual, therefore twice the 20 times two would be 40 and then comma, and then the payment that we're going to be getting is gonna be the 100 and, I'm sorry, the 1000 times the 12%, which is the coupon rate, but that would be a year because it's usually presented as a yearly rate. So we're gonna divide that by two because we're gonna get it on a semi-annual basis. And so there is that, let's say, enter. So there we have that. Then we've got the present value of the face amount, the 1000, we're gonna get at the end for a singular bond, negative present value shift nine. The rate is going to be then the 9%. Again, that's a yearly rate. So we're gonna divide it then by two to get the semi-annual six-month rate, comma, the number of periods is the 20 years, but we want them in semi-annual years. So times two to get to 40, six-month periods, comma, no payment because we're not talking annuity this time. So comma, comma, come merely on to the future value, the 1000, we're gonna get at maturity the face amount being returned to us at that point. And let's end that, there it is. We're gonna sum it up equals to sum it up. Little darling. And this is the bond price for one bond. Bond price for one bond. And so let's put an underline there. Let's add some decimals to make it a little bit more specific. Gonna select these three, go to the home tab, number group, let's put some decimals on it, decimalizing those numbers. And so then we've got the number of bonds that we purchased. How many bonds did we buy at this given these conditions? 120 bonds is how many we bought. So font group underline, if we multiply that out then one singular bond price, 1,276.02 times 120, that gives us the 153, 123 total, total price that we have calculated. So you could do this another way and you might see this in say book problems for example, we might say, well, this is the face amount for all bonds, all the bonds that we're purchasing. Let's make this cell A a little bit wider and that would be equal to the $1,000 face amount for one bond times 120 bonds that we are purchasing. So we could say, well then the face amount for all bonds kind of considering it or pretending as if through one bond, 120,000 when in reality we're buying 120 bonds at the 1,000. So if we did that calculation, same calculation here we're gonna say, all right, let's take the bond price, we'll call it method two, method number two and let's make this black and white down here. We'll make it black and white. Let's move it up a bit. I'll move it up right there. So there it is. Okay, so now we're gonna do the same thing. We're just gonna take the present value of the interest, present value of the face amounts. I'm just gonna say equal those numbers but this time I'm gonna be using the 120 instead of the 1,000. So first on the interest payments, negative present value shift nine. We've got the rate, it's gonna be the market rate. We gotta divide that by two because that's a yearly rate and these are semi-annual. We need the six month rate, the half year rate and then comma the number of periods. The number of periods is still gonna be 20 years but we need semi-annual periods or six month period, half year periods. Therefore, we're gonna multiply that times two to get to 40, comma the payment is gonna be now 120 for all the bonds times the coupon rate of 12%. But that would be the yearly payment. We need to be paying out every six months so we're gonna divide that by two. So there we have it and close up the brackets. You don't really need to, it'll close it up for you but I'll close it up there. And then present value of the face amount which is gonna be the rate once again, 9%. That's the yearly rate so we're gonna divide it by two to get to the rate on a per six month period, comma number of periods is gonna be 20 multiplied times two because we got six month periods of 46 month periods or half year periods for 20 years, comma payment. No, no payment because not an annuity this time, double, comma, comma, comma. And now we've got the future value which is gonna be the 120 that's coming back to us this time because it's all 120 bonds at the 100,000 we're gonna receive in the face amount four. So there's that. Let's put the underline underneath where it should be. The underline goes underneath and we're gonna sum this up. Little darling, sum it up. And then that's gonna be the total bond. So you can calculate it thusly as well. Thusly is also a method you can use. So let's go ahead and put some blue borders around this. We're gonna go home tab up top font group, hit the bucket drop down. If you don't have that blue right there it's in the color wheel, color wheel standard. There's the blue we want. Okay, okay. Home tab font group drop down on the borders. Let's put in all borders around that, around that little doggy. And then we're gonna go down here do the same thing. Now we're set up font border blue, border blue. And then these two we're gonna do that font group border blue. Let's do a spell check on it just to see if I didn't spell anything. It's a small sheet. I should have spell check, perfect. I got it. I got through that without spelling anything wrong. Easy, easy. So there it is.