 Personal finance practice problem using Excel. Bond price calculation using Excel. 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 being an answer key. Let's look at it now. We've got the information on the left calculations on the right. We're looking to calculate the price of a bond practicing our time value of money calculations to do so, as well as thinking about our rates of returns, how the bonds work, which helps us to do some comparisons with other types of investments, which we'll look at in future presentations and practice problems. The practice tab, the blue tab, has some free formatted cells on the right side so you can work through the practice problem with less Excel formatting. The blank tab is basically blank with just the information on the left. If you don't have this worksheet, you could just set up the information on the left, which is good practice to do. I would start off laying down the foundation formatting by selecting the whole sheet possibly with the triangle, right clicking on the sheet and format those cells. I like to format it with the number group here. I'm gonna go down to currency. I would put brackets around the negative numbers, no dollar sign, no decimals. That's the starting point. I'm not gonna hit okay, but just X out of it because I'm already set up here. Then put your data on the left-hand side, formatting cells as needed, such as the percentification over here on the percents. So the information on the left, we've got the bonds. We've got the number of years. It's gonna be five years. We're gonna say they are SIMI annual payments. They pay every six months. In other words, the stated yearly rate, I'm gonna say is 7% and the market yearly rate, we're gonna say is 9%. So let's just kind of clarify the terms a little bit. The bonds are great practice because they have two kind of cash flow components to them that we can practice time value of money with. One is the fact that we're gonna get the 100,000 face amount of the bond back at the end or term or maturity of the bond. And the other is that we typically have those interest payments. So we can think of a bond as kind of like a loan. We're loaning money, say for example, to a government or to a corporation in the form of an essence of bond, but it's not structured the same way a loan might be structured when we borrow money, say for a mortgage or something like that, where we typically pay back in installments, fixed installments, the same number installments, both having a portion of interest and principal as we pay it back. We're structuring the bond typically in such a way that we're just gonna get the full amount back similar to like renting an apartment or something like that. We rented it out. We expect to get it back at the end of the period. And then we're gonna get the earnings kind of like the rent on it, the form of interest, not monthly, but oftentimes semi-annual. Now that semi-annual component kind of confuses things a bit when we're comparing to other types of investments and trying to compare the rate of return which might be on an annual rate of return versus semi-annual rate of return. So those are gonna give us a little bit of complexity when we start thinking about that and with regards to other kinds of investments. So oftentimes if you look like a book problem or something like that, they might give you like the yearly rate and then we'll have to break it down to a semi-yearly if you get paid like semi-yearly. So I'm gonna start off with that and note that when you hear interest rates, oftentimes the interest rate will often be a yearly rate because that's usually what we do because that's gonna be in the range of one to 100. Typically when we talk about monthly rates or something like that, those can get quite small and they can be confusing therefore because we're talking about small fractions of numbers. So we usually say like yearly rates but if you're talking about bonds that pay out semi-yearly every six months, they might give you the semi-yearly rate and then you've got this timing comparison of the yearly rate and the semi-yearly rate that we will have to deal with. So notice when you're talking about an investment, you wanna make sure that whatever rate that they're giving you, you understand the timeframe that's being given. Now again, here we're gonna start with a yearly rate so that we can then break it out to the semi-annual payments in our calculation. So then we've got the market rate and we're gonna assume the market rate is the rate that the market is wanting for similar types of bonds. Now, when the bond is issued, if it was a corporate bond or a bond that's coming out from the government, they would probably make the market rate upon issuance being basically the same as the stated rate or try to so that they could sell the bonds basically at the face value of the bond. But as time passes, there's gonna be changes to the market rates due to market conditions and expectations in the market and the stated rate will be the same on the bond. Therefore, you can have a difference. You can't change the rate if you want to buy or sell bonds on the secondary market but rather you need to sell them at a different price meaning you sell them at a discount, something less than the face amount or at a premium, something over the face amount. Okay, so given this information, let's value the price of the bond. The first way we could do it, the easiest way we could do it is just using present value formulas and then we'll build kind of a table out for it to see it in more detail. So this is gonna be the bond price and let's first think about it if it was annual because this will make it a little bit easier. So let's assume, let's pretend this semi-annual thing isn't here and they're just gonna pay us annual interest at the end of the year for five years, stream of payments and then we're gonna get the bond face amount back at the 100,000. How much then would we want to pay for the bonds? How much, in other words, is that future cash flow, that future stream of payments worth in today's dollars? So this isn't bond Jovi or anything. This is a bond. And Jovi everyone, a bond. Okay, so let's make this a little bit broader here or wider and then I'm gonna select the top two, home tab, font group, bucket, we're gonna make this black and white. And so this is gonna be first, I'm gonna call it the present value of the interest. So we're gonna get interest, we're gonna get a stream of interest. And if we looked at it from a yearly basis, we calculate the interest as the bond face amount, the value stated on the bond and then the rate that they're telling us. And if they said it was 7% per year, then that's the first assumption we're gonna make. It is semi-annual, we'll do that in a second, but let's pretend it was per year, they're gonna give a 7% at the end of each year and then the 100,000 back at the end of the term or maturity five years in this case. So first we're gonna do this way, I calculate the present value by saying negative present value because that's the easiest but not most proper way to do it. Brackets, the rate then that we're gonna calculate is the market rate. Now this is the key, we're picking up the market rate. That's an annual rate. That's what the market expects for similar bonds. I'm gonna not divide it by two, we're doing anything to it because we're gonna first assume it's just an annual rate. This will simplify it a bit. Comma, number of periods. The number of periods is gonna be five. Once again, we're just gonna use years here as the periods instead of semi-annual periods and then comma, the payment. This is an annuity payment. We're going to be receiving 100,000 times the 7% stated rate, the rate actually on the bond. So we're gonna pick up the 100,000 times the 7% and enter. So that's, if we were to present value that stream of annuity payments, the rents in essence on us loaning the 100,000 present value that at the 27228 rounded. So now we're gonna say present value, meaning we took the panties off because we rounded it. So present value of the face amount or the amount that we're gonna receive at maturity or the amount that we're gonna get back after the five years, the original amount that we loaned if we were to have loaned the full 100,000 which we won't because we're gonna price it at whatever the price is given this condition but the amount that we're gonna get at maturity. And so we're gonna say this'll be negative present value shift nine, the rate is once again, the market rate what we expect on the market, comma, number of periods is gonna be five again for the five years, comma, we're not gonna be using the payment because this is not an annuity but present value of one, therefore two commas picking up that future value, 100,000 which we're gonna get at the end of five years in order to get to the present value as of this point. So that's gonna be the 64993 that's the 100,000 worth five years later as we discounted back at the market rate 9%. So that's gonna give us the bond price if annual. So I'm gonna sum this up if it were annual then what happened? Okay, Pa, so we're gonna sum that up. Okay, so we've got 92, 221, let's put an underline here font group underline, let's make that a little bit larger. And so there we have it. So because the market rate, the market could invest we're assuming in similar bonds that could get a higher return. Therefore, if this bond was already issued with this amount on it, we can't change the rate we would have to change the price. We would be willing to pay less than for it because we can buy other bonds elsewhere and get a better return. If this was nine right here if these two were the same in other words you would expect that to be 100,000 and notice how we set up our data so that you could just change your information on the left hand side and see it populate and you can see what's gonna happen with regards to premium and discount. Let's go to the premium now 12%. So now the bond is paying more than you could get on the market for similar types of investments. Therefore, if you were to sell the bond you would be selling it for something over the face amounts of the bond and selling it for then a premium as they say. All right, let's bring it back to where we had it before, 7%. That's where it was. Let's make this blue and bordered and then we'll add a level of complexity. I'm gonna go to the font, put some brackets around this, go to the bucket up top, blue. If you don't have that blue it's down here and the more color standard on the wheel that's the one enter. So now let's do it and let's make it a little bit more complex. They gave us the yearly rate which again if it was actually on the bond and it was a semi-annual bond they might give you the semi-annual rate but we're gonna assume they give us we've got the yearly rate and we need to break it down to a semi-annual rate because the payments are gonna be given semi-annually. Okay, so we're gonna say let's say that we have the bond, the bond price semi-annual, semi-annual. All right. And let's make that black and white. So we're gonna go up top, home tab, font group, bucket, black and white. So we'll do the same thing, present value of the interest payments, the annuity, the stream of payments but now we're assuming we get the payments every six months, every half year instead of at the end of the year. So we're gonna say this is gonna be negative present value, shift nine. The rate is gonna be same market rate but now that's the yearly rate for the market. So I'm gonna divide that by two to get an idea of the semi-annual rate, comma. The number of periods would be five but that's in terms of years and so now we need to match up the rate to the number of periods. So if we've got five years and now we've got to double it times two to get to semi-years, there's 10 semi-years or half years, comma. And then the payment is now gonna be the 100,000 times the 7% but again, that 7% is a yearly rate. So the rent we're gonna get is the, every six months, 100,000 times 7% divided by two. So now I'm gonna get the 100,000 times the 7% divided by two. So now we're valuing that at the 27,695 that we're going to be receiving a stream of payments every six months, every half year for 10 periods or five years semi-annual. So then we got the present value of the face amount. So negative present value rate, we're gonna get the same rate, 9% but I'm gonna do the same thing. So I'm gonna say I'm gonna take that and divide it by two and use the same kind of period structure, comma. The number of periods is gonna be five but that's years, I'm gonna do the same period structure and so I'm gonna take that and multiply it times two. So 10 periods of six months, comma. There's no payment because we just have the one amount this time that we're pulling back to the current period that being the 100,000 and enter. So let's take that, that's gonna be the bond. I called it a band price, band price. We're having a party with a band, band price semi-annual, semi-annual and I'm gonna sum this up, summing that up and let's change this to bond. This is a bond, James Bond. James Bond. A bond. Not James Bond, just a bond, just bond. It's even blander than James Bond. Bond was supposed to be a boring name. We don't even have the James anymore. Can't say Bond, James Bond, it's just bond, bond. Anyways, so now let's kind of, well, hold on a second. Something went wrong here. Something went horribly, horribly wrong. Let's double click on this. We've got this divided by two. This, I said times 10, it shouldn't be times 10. It should be times two, to get to 10. All right, so I'm gonna say enter, so there we have it. So now we're at the 92.87. So that's the one we're gonna use and so this would be it for the semi-annual. Okay, so now let's kind of prove that to ourselves. I'm gonna put a table together and just break out the stream of payments so that we can get an idea of what's happening. I'm gonna make a skinny F by selecting the skinny C here. I'm gonna hit the paint brush and go on over to the skinny F. And let's think about it. I'm gonna put a years column and I'm gonna put a periods column here. So in periods, we have semi-annual periods. So it's gonna be one, two, and it's gonna go out to 10. I'm gonna select those two, pull my fill handle out to 10 periods, and then let's actually center that, center that, and we're gonna center it. And I'm gonna make this one dark blue a little bit different color and make it dark blue and white. And then I'll put the years up top, which I'm gonna say is one, tab, tab, two, tab, tab, three, tab, tab, four, tab, tab, five, tab, tab. And so, and then I'm gonna center these years up top because we got two six-month periods, which is one year. So I'm gonna go up top. We could do this by going to the home tab alignment and merge and center, but I don't like doing that because then I got these two cells that are like one cell. So I will typically undo that and do it this way, right click on it. And I'm gonna say I would like to format the cells. I'm gonna take these two, right click on it. Hold on a sec, my right click isn't where it, format the cells. And then I'm gonna go to the alignment horizontal, and I'm gonna center across the selection. So same thing, you could do it either way, but then that way I've got these two cells but it's centered across them. I'll format paint it, home tab, format paint. And I'll click right there, do the same thing. Format paint right there, do the same thing. Format paint right there, do the same thing. One more time, format paint right there, do the same thing. I'm gonna select all this top stuff and I'm gonna make that black and white. So I'll go, let's go black and white on the lettering. I might put some custom borders on that just to get fancy down here so we could see. I'm gonna make it the color of the custom borders white. I'm in the border area. And I'm gonna make a border around here and inside and then boom, so now you could see kind of that it's years up top. You might even do the same thing for these ones, same border option. So boom, or maybe I'll just make normal borders here. Normal borders, all borders. Okay, that's enough with the fanciness. Let's make this black and white, black and white. Okay, so now we've got the interest payments, interest payments. So from a semi-annual perspective, I'm gonna make this a little bit larger. We're getting interest payments, the rent, which is gonna be equal to the 100,000 times the 7%, the amount on the bond. This is how much we're actually gonna get paid, not using the market rate divided by two because we're gonna get them semi-annually and we're assuming this is a yearly rate. So there's the 3500. In the second period, I'm just gonna say equal the prior period instead of using absolute references and then just put my cursor on the fill handle, drag that to the right and there we have it. So there's our series. I could put the total out at the end here. Total and sum that up. So that's how much we're gonna get from rent on our loan in essence, the interest. And so we're gonna say boom, boom, let's format this. Okay, and then we're also gonna get our initial payment back. So this is gonna be the face amount at maturity that we're gonna get back at maturity. We're gonna get our money back, not exactly what we paid in essence, but the face amount because we're gonna possibly buy it at a discount or a premium or whatever. So at the end of the last period, period 10 or the end of the fifth year, we're gonna get our 100,000 back. So there is that. So our cash flows, if we think about this as a series of cash flows on a every six month frame period, I can sum this up, sum these two up, put my cursor here, fill handle and drag that to the right. And we could say there it is. I'm gonna drag this total down. So there we have it. So that's what we're gonna get. Now, if I present value each of those streams, present value, present value, let's just call it present value, then I can present value each of them back, discounting them back using, and I'm gonna use the market rate, but I'm gonna use half the market rate that's a yearly rate and divide it by two. So if I take each of these streams that I'm gonna get out in the future and try to discount it back, I could say negative present value, shift nine. The rate is gonna be the market rate. So if I get 3,500 in the future and the market rate is 9%, but that's a yearly rate and I divide it by two, then I'm gonna say comma. The number of periods is gonna be that number one period up top. So because I'm talking about six month periods in this case, and then comma, the payment is gonna be no payment because we're not talking about an annuity, just two commas, we're gonna go to the future value, which is at 3,500 and enter. So if I discount that back, we're at the 3,349. I would like to copy this across so it picks up the two, the three, and so on. I want this number nine, however, not to move when I copy it across. Therefore, I double click on it. This one that's outside my table I'm working in, which is that B6 right there. I wanna make it absolute. I'm gonna click on that B6, select F4 on the keyboard, putting a dollar sign before the B and the six. You only need a mixed reference, but an absolute one works. Telling Excel, don't move that cell to the right when I copy to the right. And everything else, I do want you to move to the right when it'll be copied to the right. So I'll say enter, put my cursor back on it, put my cursor on the fail handle, and then drag it on, drag it on over. And so now you can see that that stream of payments, which is basically an annuity, we could do the annuity calculation, which we did over here, right? We did it with an annuity calculation. If I add up that stream of payments, and I had one more, this one, see this is where it gets thrown off because now we have the end of that annuity and the 100,000. And that's why we have these two kind of things going on. So if I total this up, let's total this up. This way, we get to that 92087. And so there's the 92087, or I can do it one more time down here just so we could see it. So this is gonna be equal to the sum of the present values. So you get an idea of where that number comes from. So we're gonna say that then is what we would think the price would be because that's us present valuing the stream of future payments based on kind of the market rate. That's the general idea. Now again, it gets a little bit kind of confusing when we've got yearly rates and we've got semi-annual payments and trying to compare those. So we'll talk a little bit more about that in the future, but that's the general idea. And let's just get a quick little, I'm gonna pull this one down a little bit and let's just get a quick look of what kind of problems we kind of have when we start to think about yearly rates and semi-yearly payments, for example. So let's, for example, pretend we have yearly cash flows. What would my yearly cash flow be? Well, if I got paid the 3349 for two periods, my yearly cash flow would be equal to the sum of, I'm sorry, I got paid the 3500 for two periods. It would be the sum of those two at the end of year one, not period one, period two, but now we're saying per year. So I get 7000 per year, which I can also calculate at 7000 times the 7% here. So 7000, I'm gonna copy that. I'm gonna put that right here at the end of year two. I'm gonna put that right here at the end of year three. I'm gonna put that right here at the end of year four. And then I'm gonna put that right here at the end of year five. And then we also, of course, have the full amount that we got here, the 100,000 plus the 3,005, the 3,005, and the 100,000. So now if I sum that up, if I sum up this, we get to the 135,000 here. So we have that, but if I do the same kind of discounting here, if I take the present value, and now I'm gonna present value back one year instead of on a six month. So if I say negative present value, shift nine, the rate, I'm gonna take the market rate, which is 9%, instead of 9% divided by two, because now we're saying a year, comma, the number of periods is now, I'm gonna go up to one, up top one, which is in this cell, even though I put it kind of like in the middle, it's in that cell. And then comma, comma, the future value is that 7,000. Enter, I wanna be able to copy this across. So I'm gonna double click on it. And that nine right there, I don't want that to move. Therefore, I'm gonna put my cursor in B6, F4 on the keyboard, dollar sign before the B and the six, and enter. And then I'll just copy that across. Copy that across. Copy, paste, I'm just Ctrl V, Ctrl V, Ctrl V. I could sum it up on the left, on the left. And so there we have it there. I could sum it up down here. And let's say this would be the bond price for yearly, or let's just do it this way. This equals basically this number up top. And so I'm gonna say this equals the sum of these items. And so there we have it. And again, you can see that these numbers are not the same, right? So even though you would think they'd be somewhat similar, and that's because we've got this kind of compounding difference that's related to it, which is a little bit confusing given the fact that we have two streams of payments, one being the amount that you receive at maturity and the other that you're gonna get semi on a periodic basis. The other kind of issue that we can take into consideration when you're trying to figure out comparable rates is the fact that if I got paid in six months, this 3,500, that would be better than if I got paid 7,000 at the end of the first year. Because if I got paid at the end of the first year, then I can't do anything with that money until another six months has passed. So we also have kind of like this issue when you're trying to compare these two streams of payments or like what a comparable rate would be of the fact that that 3,500, if we got it, we might try to make the assumption that we could then invest that 3,500, possibly making a market rate of return of the 9%, which would give us a different, kind of a different cash flow perspective. So we've got some issues when we try to compare, say rates for example, and compare something that's you're getting semi-annual payments to annual payments and what's the semi-annual rate and so on and the annual rate. So just to highlight that a little bit here, we'll put that here. I'm gonna do some formatting and we'll talk a little bit more about those rates in the future. Let's make this blue and bordered. I'm gonna select this and say bordered and blue. And let's make this one a little, let's make this one down here border blue too. So that's border blue. And let's make this one yellow because this is us kind of switching it up a bit just to see some of those issues. I'm gonna go border and make this one yellow. And I'm gonna say let's make this one bordered and yellow and maybe I'll make this one yellow up top so that we could see that comparison. All right, let's do a spell check real quick spell check. Spell check, perfect. Semi-annual, I think sometimes they make two words. I don't know why if it matters if it's step two words or not. But spelling isn't my forte. I like forte, I want some tea, I don't know. But that's it.