 Personal finance practice problem using Excel. Coupon rate, current yield, yield to maturity, and market price for a discount bond. 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 to three tabs down below example, practice blank, example, and answer key, let's take a look at it now. Then on the left related to a bond outstanding, we're going to calculate the coupon rate. The current yield will calculate the market price a couple different ways, and then we'll do the calculation in a bit more extended format to get a better understanding of how to get to that market price. Let's go to the practice tab which has some pre-formatted cells so you can work through the practice problem with less Excel formatting. The blank tab, basically a blank sheet with just the information on the left if all you have is a blank sheet. You can start out, I would start out by throwing down or putting down or laying down the baseline formatting. I do that by hitting the triangle up top, right-click, and then go to the format cells, only do this if you don't have the worksheet, right, and then I would start off with the currency, the bracketed numbers, no dollar sign, no decimals as the starting point. I'm not going to hit okay because I already have this. I'm just going to X out of it, then add your data on the left, make a skinny C column, and we're ready to go. So we've got the bond outstanding. We've got a par value of 1,000. We've got the annual interest payments are going to be 70. We've got the market price at the 840, that market price less than the bond par value. Therefore, it's issued at a discount, and the maturity is 10 years. So let's first think about the calculation for the coupon rate. That's the easiest thing to do first. Let's calculate the coupon rate. Coupon rate. I'm going to make this a little bit wider here. We're going to adjust this to a black and white heading. We're going to go to the home tab font group bucket drop down, making it black and white as we typically do for our headings. We've got the annual interest payments. I'm just going to take the interest payments because that's what we know. We're assuming this time that they are annual, not semi-annual, which is a little bit of a simplification. In some cases, it could be a little bit more confusing when you get the semi-annual, but we have some examples of that as well. So it's going to be the 70. And then we've got the par value. The par value is going to be just the 1,000. The 1,000. Let's put an underline here, home tab font group underline. And that's the coupon rate we're calculating. We're going to divide that out. This is going to be equal to up to, that's the 70 divided by the 1,000. We got to recognize that cell. And to do so, we're going to percentify home tab number group percentification of the cell. And let's add some decimals here. We don't really need any, but let's add a couple decimals just for the fun of it. I'm going to make that blue and bordered. So I'm going to put some formatting around this. To do so, I'm going to go to the font group, bucket drop down. That's the blue we're going to use or I'm going to use. You can do whatever you want. But if you want to use the same blue, it's in the color wheel here. If you check that out standard, there's the blue right there. If you want to use that blue and then font group drop down, we're going to go all borders. Okay, so the coupon rate is not as useful here when we're trying to be comparing the bonds because we issued it basically at a discount. So we're also going to be wanting to calculate the yield to maturity. Could be a useful calculation. We can also calculate the current yield. So let's calculate the current yield, which might be a better tool for us to be doing some comparing and some contrasting and still a fairly simplified calculation. So I'm going to make this black and white up top. Let's go to the home font group, black and white for the header. And then for this calculation, we're going to take the annual interest payment again, annual interest payment. But this time, I'm going to be comparing it not to the 1000 face amount, but rather to what we paid for it, which is kind of like, kind of like the return and the current, you know, time period. Although it's a simplified thing because it doesn't have a consideration of the time value of money and the fact we're going to get the 1000 at the end of the 10 years and so on. But could be useful calculation current yield. We're going to go to the font group, underline it here. And we're going to just call this the current yield yield currently. You must yield currently 70 divided by 180. And let's recognize with a percentify home tab number group percentification. We're going to add some decimals on this one. So it's 8.333 on and on, but we're just going to keep it at the two threes there. So obviously, we have a bit different number given the fact that we've got it issued at a market price, which is at a discount notice. Obviously, you can change the data on the left hand side of set up properly. If we had it at a premium, for example, this greater than the 1000 like 1200, then we've got the 7% and the current yield at the 5.83 undoing that. We're now at the 8.33. So that's the difference between the premium. The discount will focus more on a premium situation in a future presentation. Let's highlight these. We're going to go to the home tab font group borders, drop down on the bucket and blue. And then let's make a skinny F column and start working on to the right here. Let's move on over to the right. So I'm going to just copy the skinny C by going to the home tab clipboard format paint brush and just paint brushing that format with one paint stroke. The whole fence is painted with one stroke. It's amazing with the format painter. Okay. So this is going to be let's call this the present value value of or what we're going to calculate is basically the bond market price. Let's say bond bond market price. And I'm going to do this a couple different ways here because I think most people are kind of more familiar possibly with calculating the market price because we talked about before that you calculate the market price with the present value of interest and the present value of basically the maturity value of the 1000. But now we know the market price which is 840 and I'm trying to back into the yield to maturity. We could use a rate function or formula to do that but if you know how to calculate the market price you can kind of back into it this way as well and we can use a tool called goal seek to do that and it might help intuitively to understand what's going on with it. So I'm going to I'm going to basically make this blue and bordered to do this or make this our header. No black and white not blue and bordered black and white and then we're going to have the present let's just call it PV present value of interest and then the present value of the face amount at the end and that's going to give us our market price. Now to do this I'm going to basically have a rate that we're going to use rate for the goal seek and I'm going to I'm going to use a thing a tool called goal seek goal seek in excel. So what I'm going to start with is just pick a rate and I'm just going to pick something like let's say let's say five percent point oh five percent and we'll make that a percent. I'll add some decimals to it here and then I'm going to calculate the bond price based on that five percent and then I'm going to come up to something that's different than the 840 because I don't know what the actual percent should be then I'll ask excel to change this number to get to that in number to where I know it should be which is the 840 with just trial and error. This is a useful tool that you can use in excel and it kind of helps you to kind of back into things in a sort of an algebraic way but instead of solving algebraically we just use brute force to solve for the unknown right and excel can do that quite quickly. So let's try this out see how it looks we're going to say this is going to be negative present value we've seen these calculations before so I'll do them a little bit faster present value of the interest rates so this is going to be the rate is going to be the five percent comma number of periods I'm going to say is 10 this is not a semi-annual bond so we'll just keep it at 10 comma the payment is going to be that $70 we said $70 on the coupon so that's going to give us then the 541 present value the face amount that we're going to get after the 10 years negative present value shift nine the rate we're going to take that five percent comma number of periods is going to be the 10 I'm just reading along here at the bottom and then comma we don't have a payment because this is not an annuity so double comma comma comma chameleon and the future value which is going to be the 1000 we're going to get in the future and then enter so there we have that if we sum this up we should get to 840 but no it comes out to 1154 so now what I'd like to do is change this number I'll make it yellow change that number until it gets to this result of 840 just brute force so we could say well what if it was six okay what if it was seven and we could do that like eight and so on or we could use goal seek to do that so I'm going to go up top and say goal seek it's in the data tab I think and I don't need to be on the cell so I'm off the cell I'm over here I'm just going to say forecast what if scenario goal seek I have a goal that I'm seeking excel and I'd like your help with it and the excel is like what's the goal and I'm like we want this number right there to be and you got a hard-coded right here we want it to be 840 and we'd like you to get it there by changing this number so see it's kind of an algebraic solving for kind of like the unknown but doing it with brute force as opposed to working the math and so we'll do it we're gonna say okay do it excel and there there did it for us I'm gonna say okay so there's the 955 now the other way you can get to that which is basically this is the yield to maturity yield to maturity which is the ytm let's do it with a rate formula as well the rate formula is a little bit confusing and so that's why it's probably not maybe not as familiar with the with doing it this way so we're going to say rate formula brackets we're going to say the number of periods is going to be 10 years it's not semi-annual so that's straightforward comma the payment that we're going to have is the 70 dollars on the coupon comma the present value this needs to be a negative this is where one of the confusing components is needs to be a negative present value of the market price that's at the current point comma the future value is the bond power value and then boom we should get to that nine percent again if we recognize with a percentify home tab number group percentify you best recognize with percentify okay stop that recognizing thing home tab it's annoying just do the problem i'm going to make this blue and bordered i'm going to make this blue or just bordered and then this stuff blue i'll keep that one yellow okay so then i i might recognize again i might i might uh do this calculation again you know just so we can now we can see it again based on this rate right so i'll just do it one more time this kind of an overkill but i'll say this is the present value this is the present value this is the market price so all we did here negative present value shift nine the rate is now that nine point five five comma number of periods is now the ten comma the payment is going to be these seventy and there we have it negative present value shift nine the rate comma number of periods is ten comma comma cameleon because it's not a annuity future value one thousand and then we can sum it up and there we have the 840 again which is our price now we might want to just break out that 840 so you can see the stream of payments i think this is just a useful way to see why the bonds are a little bit confusing because they got that two different things going on you got an annuity thing and a present value of one thing anytime things are uneven and we want to kind of you know do this present value thing we could just list out the cash flows useful tool to know so i'm going to then blue and border this thing blue and border it poor favor let's make another skinny eye skinny eyes going to be skinny eyes that naturally skinny itself so we can make the column to match home tab format painter skinny eye painting that down i'm going to go to k we're going to say one two i'm going to bring this out 10 years selecting those two putting my cursor on the fill handle pulling that oh no to 10 years 10 years man it's a long time we've been struggling here we're going to it hasn't been that long that we've been doing this problem that's just the 10 years so we're going to say that and then this is going to be let's bring this a little bit larger so i've got the interest the interest is going to be 70 dollars per year so 70 dollars per year that we're going to get from our bond i'm going to go to year two and say that equals the prior year so i could just copy that across with the fill handle button fill handle drag it on out drag it on out and then we've got the face amount face value we're going to get at the end year 10 at the end year 10 that's going to be the 1000 so there's our there's our cash flows so the total cash flows we're going to be getting equals the sum of the 70 dollar payments we're going to get each year and then equals the sum of the 1000 we're going to get at the end and so 1700 so now we can we can we could sum up our cash flows so let's take the total cash flow per year per year cash flows equals the sum of the 70 i'm just going to bring that all the way across boom and then there's the total let's do some formatting so it looks a little nicer can we make this look a little nicer it's kind of it's kind of bothering me this is the header you should have a header this is going to be paintbrush black and white center it and then let's make this a little wider let's make this blue and bordered blue and bordered okay so now that now we're going to take the present value of the cash flows let's put an underline under these ones does need an underline before you do anything else okay okay so now we're going to present value each of the cash flows back each year your one year two year three year four and so on so negative present value shift nine the rate is going to be the current rate which we decided is the 9.55 comma number of periods i'm just going to pull up there for one it'll copy across when i pull it across and then comma not an annuity so two commas because we want just to bring that 170 dollars back one year and there's the 70 and okay i'd like to copy this to the right i can't do so because this cell is going to move to the right because it's outside my table so if i double click on that put my cursor on h7 that's outside my table actually this one's the one i picked up so therefore i need to absolutize it by selecting f4 and the keyboard that's one way you can do it put a dollar sign before the h and the 7 telling it not to move to the right when i copy to the right you only need a mixed reference but an absolute one is the easy thing to do so we're going to do the easy thing we're going to pull that fill handle to the right now and so there it is boom we could sum it up this way equals the sum like so and there's our 840 once again you might want to put the price down here somewhere though so that it's easier to see we could just say we'll put it right here so we could see we did the same thing equals the sum of these so there's the 840 so you could see this annuity payment we can break it out between an annuity because this is an annuity series of payments and this is a present value of one but anytime you have a more complex calculation of payment or cash flows into the future that you want a present value even if they're uneven you can just break it out year by year and then take the cash flow on a per year by year basis and that's a useful quite useful tool especially for like budgeting and long-term project kind of stuff so we're going to make this a little bit smaller let's make this a little bit smaller make it look nice this one needs some blue and borders blue and borders on that this one needs to have some blue and borders did you misspell anything did you misspell anything probably no oh i did yield you must yield to maturity never okay i think we're done i think that's it okay