 Personal finance practice problem using Excel, bond purchased on margin leverage investment. 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 left, calculations right. We're going to be imagining that we purchased a bond in the past. However, instead of paying just cash for the bond, we basically purchased it on a margin. We took out, in essence alone, we leveraged in order to purchase it. And we're imagining then that we're going to sell the bond in the future and what will be the impact of basically that leveraged or purchased on a margin investment. So the second tab here, the practice tab will have some pre-formatted sales. So you could work the practice problem with less Excel formatting. Third tab, we're going to actually work the problem and do the Excel formatting. If you don't have anything, you can open up a blank worksheet. I would throw down the baseline formatting by going to the triangle up top, right-click in the selected area, format the sales within it. And then I typically start off with currency, bracketed numbers, no dollar sign, no decimal. I'm not going to hit okay because we already have it here. We're going to X out of it, then add your data on the left formatting sales as needed, such as the percentages, make a skinny C column and we're good to go. So we're going to have the information on the left. We've got the bond is at the par value, $1,000 bond. The coupon rate we're going to say is the 10% maturity years, 20 years. We're going to assume that we purchased the bond at a price of $1,135.55 and we paid 20% for it. That's kind of the tricky thing here. We didn't purchase it just for cash. We basically purchased it on margin in essence, taking out a loan for the purchase of it and paying only a portion, in this case, 20% of the cash. We borrowed or bought on margin, use interest payments on bond to pay the interest on the loan. So we can imagine if we took out a loan in order to buy the investment, then we're going to have to pay back the principal of the loan and we're going to have interest to pay on the loan that we took out. So we're going to imagine that we're going to be using the interest payments from the bond that we purchased, which pays us periodic interest payments to pay off the loan interest payments that we took out in order to buy the bond, hoping that the bond goes up in value. If the bond goes up in value, we will have a gain that will be higher than it otherwise would be given the fact that we purchased it on margin. We purchased it by leveraging it. However, if it goes down, it will also amplify the loss that we'll have as well. And you can see this is typically seen for individual investors in like a home because when you purchase a home, most people purchase the home and they take out a loan on the home, a mortgage on the home. They do that for necessity purposes, not just for investment purposes because they want to use obviously the home. But the same thing is in effect there. Oftentimes if the market goes up for the home, then that could affect you quite positively given the fact that you purchased it on the margin. You invested like if you invested $200,000 that you didn't have, right? You only had 20% of that and now the whole $200,000 is going up in value because you purchased it basically on the margin, right? You leveraged the investment. However, if it goes down, that causes problems. So that's the issue here. Bottom line, you need to be careful with like leveraged investments. So in any case, we got the market rate of the 7%. We're going to say we got to pay 11.01 in order to take out the loan just to get an idea of the loan. Okay, so let's say that we're going to sell this thing at this point in time and calculate what the current price is for us to sell the bond. So we're going to sell it at this point in time. We bought it at this amount. Now we're going to imagine it's a later date and we're going to basically sell it based on this information. Let's calculate the current price we think we could sell it for. So we got the current bond price, we're going to say. Let's put some black and white headers, home tab. We're going to go to the font group and make that black and white. And this is going to be the present value of interest. And this is the present value of the, let's say the face amount. Let's say face amount at maturity. So those are standard present value calculations that we've done in the past. I'll do this fairly quickly. We're going to say negative present value shift nine rate is going to be the market rate, which we're going to say is that 10, 7% down here. And we're going to assume it's a normal annual, an annual instead of semi annual. So we don't have to do any funny business by dividing by two or anything, comma number of periods is going to be 20. And once again, we're going to assume it annual. So they will keep it at that comma. We're going to have a series of annuity payments that we're going to be receiving. But as we receive them, we're going to be using them to pay off the loan. So that's going to be the 1000 times the 10%. So that's what we're going to get. We're going to say enter. And then we've got the face amount that we're going to get at the end if of the bond or whoever owns the bond will be getting $1,000. So negative present value shift nine, the rate market rate. Once again, 7%, comma number of periods is once again 20, comma this time, not a payment because not an annuity to commas were in the future value component, taking that $1,000 and bringing it back. So that's the 258. So this is going to be the bond price, which is the sales price, right? Sales price at this point in time equals the sum of those two. Let's add some decimals on it. Let's add some decimals and we're going to go number, add a couple of decimals. Let's put an underlying font group underline. Let's make it blue and bordered selecting these items go to the font group. We're going to go to the bucket dropdown. If you don't have that blue, it's in the color wheel going into the color wheel. The wheel. Oh, color. I just need one color. Just that color. That's the only one I use. Home tab font group. I use the other ones sometimes, but not as not that much any case. Then we can also think that that the loan that we took out. Let's just consider what happened in the past when we considered purchasing this. We said, okay, in the past, we're going to purchase it for 1,135, 55, because we think it's going to go up in value. And then we're going to be planning on selling it. I'm going to make a skinny G over here and put that information on the right. So I'm going to put my cursor on the skinny C home tab format, pan it and make a skinny G. And then let's just think about that. So that means I took out a loan. Let's just imagine the loan that we took out. What's really happening when we buy it on the margin and let's put some, uh, some black and white. Let's go to the font group. Let's make this black and white. So we're going to imagine we took out a loan for 80% of this amount. Right. We're going to say, well, we bought it for 1,135, 55, and we took out a loan for 80, 80% of it. So we bought 80% of it with that much of a loan. Let's say that the rate on the loan is 11%. And oftentimes when we take out a loan on the personal side of things, imagine paying it back in monthly installments that are even like a mortgage, which has principal and interest, but you could also imagine structuring the loan in other ways, such as I'm only going to be paying you the rent on the money similar to a bond. And I'm just going to be paying you the interest. And then at the end, when it, when the loan is done, I'll pay you the principal back at that point in time. So we just have to pay the interest payments. Then when I sell the bond or when it matures, whatever, then we can pay back the loan. So we're going to say, let's say the rate then is at the 11%. So we've got to borrow at 11%. We're going to say and 11.01. So if I go number group, I'm going to percentify that 1101 about. Let's put an underline here. That means that we're going to be paying interest payments, payments equal to the 908 times the 11%, which I made that equal to $100. Why? Because we borrowed what we could borrow that we think that we can pay off the rent on the loan that we took out with the coupon payment by of the bond, because the bond's going to be paying us $1,000 times 10%. It's going to be paying us 100. And we're going to use that to pay off the loan because we're hoping that the bond goes up in value. And then we make a gain by basically selling the bond that we had leveraged. So let's make this a percent, add a couple of percents. And so you can see that in this case, then we really only paid for the bond, even though it costs $1,135. We only paid 20% of that, which is the 227. And then we took out a loan, which we're just going to be paying off with the interest on the bond. And if the bond goes up, then we're going to make out because we're going to get a gain and we really only spent $227 would be the idea, right? So let's make this black and white or blue and bordered, blue and bordered. Okay. So then we're going to say the sales price is here. We're going to say that we bought it for here. So normally we would say, okay, what's the dollar profit based on the current price? So generally the profit calculation. I'm going to select these and I'm going to make this black and white. Let's make this black and white. And we're going to say that the price of the bonds were or the sales price. We're going to say is equal to the 1,017. 82. Let's add some decimals, number group, add a couple of decimals. And we purchased it for the 1,135, 55. But we didn't actually pay that much because we bought it on, we took out a loan. So I'm going to say number, let's add a couple of decimals. So that means the profit from a conventional sense profit would be the difference between the two. This would be equal to the 1,317 minus the 1,135. Let's add a couple of decimals. Let's put an underline here, font group underline. And then if we compare that profit to what we purchased it for, let's say purchase price here. Let's compare that to the purchase price. We're going to get then the profit percent, profit percent, percent profit, which is going to be equal to the 1,8227 divided by this number. Add some decimals, home tab number, add some decimals. Let's put some decimals on this one. We're going to go, we're going to add a couple of decimals here. Let's put an underline font group underline. I don't really need this cell to be black. I'm going to go up top and say, let's format paint the one above it to this one. And let's just make this a little bit longer so it fits our header in it. And then I'll make this blue and border. So make this blue and bordered font group border blue. So there we have it. Now there's our profit percent, but really we can compare it to the actual cash that we paid because actually we only paid 20% of this amount. So if I compare it to the, to like on a cash flow basis, this is the percent return, percent return on cash investment. Let's think about it that way and select these two items, font group. Let's make this black and white. And then let's say, okay, well the amount paid in cash that we invested is really the price that when we purchased it. The purchase price is equal to the one, one, three, five, 55, adding a couple of decimals, number group, couple of decimals, but we only paid a percent, which is 20%. So equals the 20%. Let's make that a percent number group, percentizing it, font group underlining it. And so we're going to say, let's copy the amount here, copy and paste. I'm going to remove the colon at the end. Removing the colon, put this in the outer column. This equals the 135, one, one, three, five, five, five times 20%, adding a couple of decimals, home tab, number group, decimalize, 227, 11. Let's put some indentation here, selecting these three, home tab, alignment, indent. I'm going to go alignment and dent again. So we really only paid 227, 11 in terms of cash flow, right? Because we took a loan for the rest of it. So the profit, so if I compare that to the profit, the profit that we had, which is, I'll just say this is equal to this equals to 182.27. We calculated up top, adding some decimals, number group, couple of decimals, putting an underline underneath it. That's going to give us our, let's call it percent return on cash investment. And so if I divide this out, the 127, 11 divided by the 182.27, we're going to go to the number group percent here. So there we have that. Hold on a second. Let's do that the other way around. This should be the profit. 182.27 divided by the amount of cash paid the 227.11. Now we can add some decimals. So if I think about it at the actual cash flow that we paid and compare the profit to that, we're at 80.26, which is obviously a much higher profit percent than the 16.05. And that's of course due to the leverage. So we made really the amount of cash we paid the 227.11 go a lot further in terms of cash flow purposes because we leveraged it and we happened to have a gain. Now again, if you had a loss, that's gamble and that's a bit more, there's a lot more risk involved in that because if there's a loss, it could amplify the losses as well. But that's the general idea. We'll take a look at that in a second. Let's just consider it one other way. I'm going to put my, let's make this blue and bordered blue and bordered. So you might think about it this way too. I'm going to put my cursor on the skinny G and make a skinny J, skinny G, home tab, format painter making a skinny J. Let's just think about it from a cash flow, cash flow perspective. And I'm going to select these two items. We're going to go to the font group, make that black and white. Let's make this one a little bit larger. So we've got the bond sales price. So when we sell it, we're going to be selling it for this amount. So that's going to be the cash inflow that we would have. And then we have the cash outflows, which means we're going to pay off the loan that we took out because we've been paying the interest payments. Every time we get an inflow of the interest payment, we're assuming we were using that from the bond to pay off the interest on the loan. So those two, I'm just assuming kind of net out here, right? So then when I get the money from selling the bond, I'm going to be paying off the loan, which was at the 10844. And then we also paid out the 20%. When we purchased the bond, I'm going to say negative this 1135 when we purchased the bond times the 20%. So that's the cash outflow. Let me pull that to the side. And then that's the cash paid for the bond. So if I put the underlying here, that's going to give us the net cash flow equals the sum of these. And that's going to be the 182, right? So there's the 182, basically profit that we calculated. And we compare that to the original loan amount that we put down, which is going to be the loan. I'm sorry, to the original cash that we paid of the 227. That's how much we put down up front. Home tab font group underline. So that's going to give us the percent return on cash. So another way, you know, get into the same bottom line. So we're then going to say number group percent. Let's add a couple of decimals. Let's add some decimals to this whole thing. Number group add a couple of decimals. Let's put some blue and border around this. Home tab font group border blue, border blue. So there we have it. So, so again, the idea then being, okay, we're going to try to buy the bond here, but we're going to leverage it. And we're going to be able to do so by taking out a loan sufficient that the interest on the bond can pay off the loan, that we leverage the loan. Therefore, we can go further with less cash and if things go positive, then we're going to amplify our gain with relation to the actual cash outflow. But of course, if I go the other way on this and say we had a loss, let's change the market rate down here. And let's say the market rate went to like 12% or something like that. So now I've got a sales price of 850-61 if I was to sell it. So if I was to sell it, now I've got a loss of the 284-94 which would be a loss of 25% compared to the original purchase price. If I compare that loss to the cash flow, now I've got a loss of 125-46% comparing it to the amount of cash paid in that scenario. So you can see how that leverage component, when you're thinking about leverage, we might talk more about leverage in the future. It's great. It can amplify the upside if things go well, but however also it can amplify the downside as well. We really want to be able to use a leverage appropriately and consider the risks that are going to be involved and have an optimal leverage level or use it in an appropriate fashion. So we might dive more into that in future presentations.