 Personal finance practice problem using Excel. Tax equivalent yield. 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 tab, and essence being an answer key. Let's take a look at it now. Information on the left calculations on the right we're calculating the tax equivalent yield useful because taxes complicate things all the time. And if we're comparing investments, for example, bonds, which have a tax benefit on the one hand versus bonds that do not have a tax benefit, we would like to be calculating the tax equivalent yield so we can be comparing like to like apples to apples as they say. The second tab is gonna have some pre-formatted cells on the right so you can focus in on the tax equivalent yield calculation and less on the formatting in Excel. The third tab, we're basically gonna build from a blank sheet only having the data on the left hand side. If you don't have the data on the left hand side, you can open a blank sheet and just add it. I would first select the entire worksheet if it were a blank sheet, right click on it, for lay down my baseline formatting, which would go down to format cells. I usually start with the currency, bracketed numbers, the symbol, a none for the dollar sign and no decimals. I'm not gonna hit okay here because I already have this. I'm gonna X out up top, then add your data, convert to percents as needed for this cell and this cell with the percent item here, make a skinny C column and then you're ready to roll. And that's where we will roll here, we're rolling now. So we've got the marginal tax rate. Now, when we talk about the tax rate and investments, taxes are confusing in terms of the US taxes, United States taxes and our income tax is what we're focusing in on here because we have a progressive tax system, meaning we don't have the same rate for all levels of our income. It goes higher and so later dollars we can assume has a higher tax rate. So we have two tax rates you can kind of consider for estimates, an average tax rate and a marginal tax rate. The marginal tax rate is our highest tax bracket. The average tax rate is kind of the average of what we're paying on average. The reason we use the marginal tax rate is because the next decision we make from an economic standpoint, when we make a decision on the margin from this point forward, it's gonna have an impact on our last tax dollars at the margin, the marginal rate. So we're gonna use our highest rate in essence. And of course, you can imagine that the highest rate will be higher for higher earners and therefore you would imagine that people that are more well off, have bigger earnings or more earnings would get more benefit from investing for example in investments that have tax benefits in that case. So we have the municipal bond. So we're gonna assume we have a bond here that has a 5% yield on the bond. Now that's gonna be a municipal bond. So we're gonna assume that it's not gonna be subject to tax. If I was to compare it then to other bonds like corporate bonds which are subject to tax, then what would be the tax equivalent yield be? Calculation formula will be down here. We got the tax equivalent yield. The tax free bond yield divided by one minus the tax rate. So once we do the tax equivalent yield, you can also solve for the tax free bond yield going basically the other way if you so choose by basically solving for the tax free bond yield if you so choose using your algebra. And you can also of course figure that out. And the easiest way to kind of figure that out, figure that formula is to use an example. Assume the bond is $1,000 and then you can kind of make some scenarios which we will also do shortly. Okay, so I'm gonna start with the title. It's gonna be the taxable equivalent yield. Now you can do this in terms of a formula just write down the formula and write it out. I'm gonna make the formula into a table. I think this is a useful tool to do to visualize things in a table format. It's also useful to take a formula and be able to put it into a table so that you can make basically worksheets for yourself and possibly for others to simplify the process. So I'm gonna widen the cell out a bit. I'm gonna widen it from D all the way over here. I'm gonna make these three cells up top our headers which I'm gonna go to the home tab font group. We're gonna go to the bucket dropdown making that black and white, black and white. That's what we do with our headers. So we're gonna say, okay, we start off with our formula. The tax-free bond yield. So I'm gonna pick up the tax-free bond yield. I'll call it the tax-free bond yield. And I'm gonna put this in the outer column because I'm gonna have a subcategory of this numerator. And so I'm gonna put this in the outer column and I'm gonna say this is gonna be equal to the 5%. That's for the tax-free bond and the municipal bonds that we don't have to pay taxes on. We're talking about the interest that we don't have to pay the interest. The interest is earnings. So we're gonna then say, which would be taxable typically for income taxes because it's income, but we might get a tax break because it's a municipal bond. So we go to the numbers. I'm gonna make that a percent, percentifying it so we can recognize it. And then I'm gonna say now we're gonna do the bottom which is gonna be one minus the marginal rate. So I'm gonna say this is gonna be one. I'm just gonna type in what I'm gonna do minus the marginal tax rate. I'm gonna put a colon because I'm gonna put this in the inner column. I'm just gonna take one as the numerator. I'm just gonna type in one. And then the marginal, which I can just pull this from my data. This equals the marginal tax rate, our highest tax bracket that we're paying for income taxes, 30%. You might be considering whether or not you get the tax benefit for the Fed and the state possibly depending on your state circumstances and whether or not it be taxable there. So take that into consideration. Home tab, number group, percentifying it, font group, gonna underline it. And so that's gonna be then, I'm gonna copy my thing up top here. I'm gonna put it down here. I'm gonna double click on it and delete the colon. And then I'm gonna indent it. So this is me doing this indentation to show this inner column calculation. I'm gonna go up top, home tab, alignment indent. I'm gonna indent it one more time. So that's the sub calculation for the denominator, which I'm now gonna pull to the outside, which is gonna be equal to one or 100% minus the top tax rate, 30% equals, we gotta put percentify it to recognize it. Home tab, you wanna recognize number, then percentify. And then I'm gonna go to the font group and underline it. And so that's gonna give us then what we're looking for. We're gonna find the exact thing that we set out to look for, which is good. So then we're gonna have this. So now this is gonna be equal to, and this is the numerator up top, the 5% divided by the denominator according to our formula. And again, I can't recognize it. Why? Because you've got to percentify home tab, number, percentify. I'm gonna add some decibels to get it a little bit more precise. Two decibels out, it could go more out, but I'm gonna take it two decibels out. That's all the specificity that I need. We don't need to be more specific than that, but notice that number does represent a longer decibel because it's Excel, and that's how Excel works. Let's make this blue in brackets. We're gonna go to the home tab font group. Let's hit the drop down on the bucket here. I'm gonna make it that blue. If you don't have it, you can go to the more colors. Standard tab, that blue right there, that's the one we're using. We're gonna say okay, doke. You could just say okay if you want. I added a doke just to add a little more spice to it. So just okay, I put a doke on it. And then I'm gonna select these two and let's make this a little skinnier. Let's skinerize those, thin them up, thin them up. Okay, so now we could check that. Let's put a check on that. Let's just assume that we had a $1,000 investment and see if this makes sense. A little abstract when we're just looking at percentages. We're gonna say okay, what does that mean? Well that means if I had a tax free bond yield of 5%, I would need an equivalent taxable yield for like corporate bonds of 7.15 to get the same benefit from the bonds because the one has a tax advantage and the other doesn't. Well what does that mean? Let's do it. Let's try to think about it this way. Let's put a skinny column to G. I'm gonna go to the home tab. Let's put a paint brushy to make a skinny G column like the C column. We're gonna copy with the paint brush. This is gonna be our check figure. I'm gonna select three cells up top, putting that into our header formatting, home tab, font group, drop down, black and white header formatting. And this is gonna be, let's kinda check this. We'll start with our earnings before taxes and put a colon there. And so let's assume we have an investment in the standard investment in bonds. I'm gonna make this sell a little wider. It is $1,000. So we'll just say like $1,000 investment that we put in place. And then we're gonna say tax equivalent yield. So the tax equivalent yield, we just calculated here, I'm gonna pull it in. I'm gonna say equals and notice if you just type 7.14% then it won't be exact because this one's actually using the actual number. So just be aware of that. It's not really, even though it says 7.14, we saw that it's actually longer than that. In other words, you can't see it now a bit, but it's more than any case. You know what I'm talking about, I hope. So we're gonna go to the home tab, a number group and percentify that. Let's add a couple of decimals. This is what I mean. If I go more than two decimals out, it's really looking like that number, but we round it to two decimals there in appearance only because it's actually the big number or the one with more than two decimals. Okay, earnings before taxes then. I'm gonna put two, or a space, so it's not a colon. Outer column now, multiplying this out. That means we would have the 1,000 times the 7.14. Now we're gonna assume this one is taxable, so there's gonna be a tax consequence here, right? I'm gonna, this one I'm gonna add some decimals. So it's 71.43%. So if I had a $1,000 investment, then I'd earn the 71.43, but then I'd have to pay the government on it because they want some of that money because I didn't invest in their bonds. So then they'll say, well, we want some of that money. And so now we're gonna multiply that, I'm just gonna say equals times our tax rate, which is the highest tax rate, not the average tax rate, the marginal tax rate, because this is all happening at the margin, as economists say, at the next step, the next thing that's happening from this point forward. I need to recognize it, so let's go, it's a percent, so we gotta percentify if we wanna recognize. And then this is gonna be the tax then. So we're gonna have tax consequences, 71.43 times the 30, so we're gonna have to pay 21. Let's add some decimals, like 21.43 in taxes. So what am I earning then after taxes? Earnings after taxes means it's gonna be equal to what I earned before, 71.43, but then I gotta pay the government 30% of it. Are you kidding me? Are you kidding me? Let's add some decimal, I think that's even. And so there we go, it's just absurd, 30%. What is going on around here? And then I'm gonna say, if I compare that to the original investment of the thousand, let's put an underline here, home tab font group and underline, that's gonna give us our municipal bonds yield, which is our, basically let's say it's gonna be our tax-free bonds yield, which is gonna be equal to the 50 over the thousand, making that a percent, percentifying it, and there's our 5%, five even. So there's our 5% here. So if you actually use an investment typical number, make up a number of 100, but a thousand is often the case when you're thinking about units of bonds, then that's a way that you can play with these numbers and make these abstract percents make more sense and kind of prove to yourself that what is going on here. So hopefully that does that. So let's go, let's do some indentation. So I'm gonna select these. I'm gonna go to the home tab, alignment, let's indent, and let's indent this one again, home tab, alignment, indent. Okay, so now, and let's make it blue and bordered. Let's make it blue and bordered while we're at it. Don't stop now, because we're at it. And while we're at stuff, let's finish, let's do some other stuff while we're at it. I'm gonna make this blue and then bordered. Let's make it a little skinnier from INJ. They can be a little skinnier. Okay, that's enough. I don't care if we're at it. We gotta be at something new now. I'm at something new. So now let's make another one. Let's say, well, now I can solve for the tax bond yield. So we can say, well, what if I go the other way and I'm saying, I know the tax-free bonds yield and I wanna get to the tax, and I wanna get to, I'm sorry, I wanna know the tax-free bonds yield because I have the tax-equivalent yield. So let's go back up top and say, imagine that's our situation. I need to make a skinny K. Let's make a skinny K, home tab and paintbrushy. And then we just put that there and it makes it skinny automatically. It's magic. This paintbrush is like a magic paintbrush. So this is gonna be tax-free bond yield calculation. I'll make this a little bit wider over here and it's gonna be three columns again. So I'll put three columns to do our black and white, home tab, font group, drop down, black and white. And so this is, I'm gonna start with our tax-equivalent. Let's just say this is gonna be equal to the tax-equivalent yield, which is this one we calculated. So I'm gonna start with the tax-equivalent, saying we know that. We know what the tax-equivalent yield is on like the corporate bonds, the ones that are subject to tax. And then I'm comparing it to the municipal bonds or the ones that don't have the tax. I can't recognize, I can't recognize. That's okay. You've just got a percent ties, number, group, percent. And then let's add some decimals. Okay, then we're gonna do our, I'm just gonna copy the same one minus, the same thing right here. One minus the tax rate. So I'm just gonna equals, equals, I'm gonna copy that down two more cells and it'll just copy that down, put my cursor on the format or the fill handle and just copy that down like right there. So there it is. And then I'm gonna say this is gonna be one, the marginal rate is equal to, that's our highest tax bracket rate again, this 30%. We need to recognize, so we're gonna percent ties, home tab, font, group, percent ties, underline it while we're at it. Don't get into that again while we're at it. You just do all this crazy stuff. You don't have all day while we're at it. One minus the 30% is gonna be home tab and then number, group, percentifies that one. So 70%, let's put an underline here and that's gonna give us our tax free bond yield. And so this is gonna be equal to, this is gonna be this times this and let's make that a percent and then add some decibels. So now we get to that 5%, right? So now we started here and we got to the 5% and you could basically use your algebra to re-algebraic whichever one you're looking for. Whether you have the tax three bond yield or the tax equivalent bond yield to kind of figure that, but still either way it's a little bit abstract when you're just looking at the percents and either way it's useful to assume that you have a $1,000 bond and kind of prove it to yourself by taking into consideration what you're gonna earn on it in either situation as well as any tax consequences if there are any. Let's make this blue and bordered. Let's make it blue and bordered while we're at it. Oh, here we go again. While we're at it. You just tack things on all day. It's like job creep. I started with one task and now you've got me working here like into the wee hours of the morning. I'm gonna make this one a little bit skinnier. Make that one a little bit skinnier and then let's go to the, let's check our spelling. Spell check, perfect. Check the spelling, yield, yield. I know how to spell the yield. I just, my fingers just got mixed up. That was, it's not my spelling. It wasn't my spelling's fault. And then I'm gonna indent here. We're gonna go to the alignment and indent and let's indent this one again to make it look nice while we're at it. Oh my gosh. Okay. I think that looks good.