 In this presentation, we will present value bond cash flows using Excel. Support accounting instruction by clicking the link below, giving you a free month membership to all of the content on our website broken out by category, further broken out by course. Each course then organized in a logical, reasonable fashion, making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems, PDF files, and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it. Now there's a few different ways we can do this present value calculations, but by far in practice using Excel is the preferred method because it's going to simplify the process and it'll allow us to do things in a few different ways a lot more easily than we would be able to do elsewhere. Of course, if we're taking a look at a book question or a test question, it's more common that we're forced to use tables in a very simplified calculator that doesn't have present value. And that can help us to kind of understand how tables work. It also might be the case that in a more of a math oriented class that we have to do the actual math, which could give us an idea of what the actual math is. So that they all have their place. If we go to Excel, however, and then of course it's a financial calculator that we could use all the same way to do the same stuff. The advantage of Excel is we can kind of formulate these in different ways and look at them in different formats. So I'll try to give an idea of why that is here, why that can be useful. And we'll give an idea of how to calculate within Excel and see that we want to know that it's all the same stuff basically want to know what are we doing, why are we doing it. And then note that if it's done in different formats that we're all doing the same thing and see how we can get information from it, how is it going to give us value about decision making. So we've got our bond information. The face amount is 100,000. The stated rate is the rate on the bond, 8%, the market rate is 10%. It's going to be a semi-annual bond. We pay it every two time periods and for two years. That means there's going to be four time periods, which is going to be because it's paid every six months for two years. So if we think about the cash flow in total dollars then we know that we have 100,000. We're going to get back at the end. And then we've got the interest, which is calculated as 100,000 times 0.08. That would be the interest for a year, but it's semi-annual. So we'll divide that by two, 4,000. So we're going to get 4,000 every six months where we're going to give 4,000 every six months if we're issuing the bond in interest. So, and then we're going to times that times 4 because there's two years times two times a year and that'll give us 16,000. So there's 16,000 cash flow of interest we'll pay in two years, four time periods on this bond plus the 100,000 original amount. That gives us the 116,000. So we have 116,000 cash flow in terms of just total dollars. Now what we need to do is present value that, which is of course going to be something less than the 116. So the first way we can do this is just to use straight present value formulas. And we're just going to say present value of one and we're going to take the bond amount. Remember, we're always breaking out the bond versus the interest payments. So that's why bonds are so great for present value formulas. Even if we don't deal with bonds, they're really good practice for present value because there are two cash flow methods are in the bond. So we could use a formula. The easiest way to learn the formulas is to go to the formula tab up top. So I'm in Y5 and we're going to go to formulas up top. And then I'm going to look for the formula. I'm going to go to FX over here and it'll give us a little thing on us finding formulas. So if I type in, I want the present value, then it'll give us these things here. And it says value. No, no, it's actually this one, present value returns the present value of an investment. That's what we're looking for, PV, present value. Probably just want to know that PV is going to be present value. Okay, if we say, okay, it'll give us a dialogue box. So this is the first way we want to do this and just kind of know the dialogue box. It'll give us a little thing down here to describe what we're putting into this little dialogue box. Now, the confusing thing about this one is we use the same dialogue box when we use the present value of an annuity and the present value of one. We're using the present value of one payment here. And so it's a little confusing, but it's nice once we get used to it that we're using the same present value because then we don't need two different formula things. So we'll have to just get used to that. So we're having present value of one. The rate is another kind of confusing piece because we're going to use the market rate. We know that, but we're not going to have two year time periods. We're going to have four time periods because it's paid semi-monthly. So we can't have two year time periods. We have to have four, and therefore we can't use the 10% because that's a yearly rate. And we need a rate per half a year. So we're going to take this rate and divide it by two. So it's 0.1 divided by two. So 0.1 divided by two. Notice it's building the formula over here in a linear fashion, which we could do. And it's faster if we get used to this formula. And then we'll go to the... This is number of periods, so total number of payment periods. Not payment amounts. This doesn't mean we're going to pay this amount each period. It's just the number of periods we're going to be covering, which would be two years, but we pay semi, so it's times two or four is going to be the number of periods. And then the payment, here's the tricky piece, is the amount paid each period. We're not going to pay an amount each period. This is just going to be the amount at the end of four periods that we want to bring back to the current day. So if we put 100,000 here, it's going to get all messed up because then it's going to think that we're making an annuity payment. And that's the difference between an annuity and Excel and a non-annuity. So even though this is bolded and looks like it's a required field, it is not. And that's the confusing thing here. So actually we're going to use that when we do the annuity. We won't use it here. What we want instead is this future value. And what that means is like we're telling Excel, hey, I know what it is at the end of the four year time period in terms of future dollars. It's 100,000. We were trying to bring that future value four time period back into the present day. So we're just going to type in the future value 100,000. Is that right? I don't have a comma there, 100,000. And then that will give us our amount of 82, 270. So we say okay. And there we have it, 82, 270. Now, if you want to flip the sign, there's a couple ways to flip it because we don't really need a negative number. I'd like to represent it as a positive number. So I typically just double click on it and I put a negative in front of the P. Some people will say the negative should go elsewhere, but whatever, it's going to go there and then enter. Okay, so note you could type it in there this way too, like you could just type it in just like this. So if we were to not use that formula box, we can say equals present value. Double click the present value and it gives you a nice little indicator here. So we're currently on this one. So we just say the rate is once again 0.1, 10% divided by 2 because it's every two time periods. Which will take us to the next argument and that's going to be number of periods. So there's going to be four periods, which we could say it's two years times two times a year or four, and then we're going to say comma. And then the tricky thing is that there's a payment. Remember there is no payment. So we could just put a zero there in comma, which I kind of like doing because it, but we can also just put two commas and that'll just mean there's nothing there and that'll say skip that function. I kind of, if you want to see it, you can say put a zero so it'll go to the next function and then we're going to say the present value or the future value is 100,000 and then close up the brackets and then we got it. We double click on it. I'm going to put a negative in front of the P. So those are the two ways you just do the same function. So that means that this 100,000 present value is worth 82,270. So then we're going to do the present value of an annuity. I'm going to use the same function up here. I'm going to go to the formula tab formula. Same present value function that probably is at the top now and we'll do the same thing. The rate is the same. It's going to be the market rate point one, 10% divided by two. Number of payments is going to be the same two years times two times a year or four. The payment amount this time is 4,000. Why? We're paying 100,000 times .08 the stated rate divided by two. So we're paying 4,004 times every period. So every period, four periods, every six months we pay 4,000. So this time we're going to use this number. So note we're using the same present value but this is present value of an annuity because we're using this payment field and not using the future value field. The present value of one would use the future value field and not the payment field. So we're going to put in the 4,000 here and that should give us our 14,184 and okay. So again I typically double click on it, go to the end of it and put a negative just to flip the sign and there we have it. If we wanted to type that in there this way we just say present value and then double click the present value and then again the rate is going to be the market rate .1 divided by two comma goes to the next function number of periods which is going to be two years times two times a year comma and in the present of the payments now our 4,000 and then close it up. We don't have any future value that's all we need and that's the other way we can do it. So I'm going to put a negative here so that means that our total then is going to be equal to the 82,270 and the 14,184. So again our cash flows are 116,4,000 times 4 plus 100 but to present value them they're only worth in today's dollars 96,454. Now this is the fastest way to do it but it also doesn't give us a lot of detail. It's nice to do it a couple different other ways it'll give us possibly more detail which you're pretty easy to do in Excel. For example we might set up a table like this and say that we have number of periods 1,2,3,4 periods and then we could say okay well how much cash flow is happening each of this is number every six months. How much cash flow is happening in these periods? Well the bond is only going to have a cash flow in year four because we're going to pay back in year four. In years one, two, three it's going to be zero so if we were to present value the bond after each period then of course it would be you know the present value of the cash flows would be zero after one, two, three and then period four would be the same formula meaning it would equal this number I'm sorry equal the present value of the rate point one divided by two comma number of periods is going to be two times two or four comma zero payments because it's just we're just present valuing one comma and then the future value is this hundred thousand and I'm actually going to change this a little bit. I'm going to change this four periods to just equal this four periods here and that's it and then that will give us this number I'm going to flip the sign by putting a negative in front of the P and then note I could just copy this all the way up I could auto fill I'm going to put my cursor on auto fill and copy it up now again obviously all these are zero because there's nothing happening here but just note we can standardize the table very easily this way if we look at the interest we're going to have four thousand which is the one hundred thousand times point oh eight the amount on the bond divided by two so there's the four thousand and here I'm just going to say equals it's going to be the same for the second period and then I'm just going to copy that down so interest is going to happen every every period six months four thousand so the total interest we're going to pay equals the sum of sixteen and we could sum these up as well some the bond and equals the sum of the present value so we're going to pay sixteen thousand now if I present value it notice we present valued here as an annuity this whole sixteen thousand present values to fourteen one eighty four but it might be useful for us to present value it each time period so now we're going to use the present value of one after each time period and we'll get to the same number this could be useful for us to see what is happening each year what's the present value each year so we'll use our same present value formula equals the present value and then we'll enter our data which is going to be the rate first which is point one ten percent divided by two comma and then the number of periods which I'm just going to point here now to this number it's going to be one period out one six month period so we're not using the annuity we're not using four periods we're bringing this one four thousand which we're going to pay six months later back to the current period so then we're going to say comma payments we're not going to have a payment because this isn't an annuity we're not going to say we're not doing it all in one calculation we're doing them one at a time using the same formula we did here except that of course we're using one period at a time out and so we're going to say zero on the payment and then the future value is this four thousand and then we'll close it up now I'm going to double click on it and flip the sign by putting a negative in front of the PV and there we have it so this four thousand six months later is only worth three thousand eight ten I'll do that one more time and then we could just copy it down using the autofill function so just to show us one more time I'm going to say the present value of this four thousand I'm sorry present value of the rate which is point one divided by two comma number of periods which is two so this is two months or two six months periods out or two periods in our workings here and then we're going to say comma the payment we don't have one zero comma and the future value is this four thousand and we'll put brackets around it enter I'm going to double click on it again put a zero in front of the P and so there we have it so of course it went down in the second year the same four thousand or another four thousand but it's worth less than the four thousand that we got you know six months ago and then we can just autofill this down I'm gonna put my cursor on autofill and just drag that down and we get the same pattern here so now we know what the present value of each of these payments were back to you know period zero and then we're going to sum those up equals the sum and we should get the same number that we got over here when we used an annuity calculation and so if we were to total everything then in terms of present value it's going to equal the present value here plus the present value here and if we copy that down autofill that down then here's our present values per period so we can say this is our present value every six months and that gives us a lot more detail than over here getting to the same end result number so that's just a benefit of using Excel once you get good at that you can do that pretty quickly one other way you might want to see it just to see this one more time we can say the same periods one two three four and if we have to present value cash flows in any kind of cash flow we might just first want to know what the total cash flows are for any given period so for every given period zero zero zero for the bond and then a hundred thousand at the end of the last period and then four thousand for the interest I'm going to say equals that one auto fill it down so the cash flow then equals the sum for the bond total we'll copy that across and then the sixteen thousand here so if we sum them up each period then it's going to have the bond plus the interest or four thousand I'm just going to auto fill that down so four thousand cash flow for period one six months out four thousand for the next six months four thousand for the next six months and then a hundred four thousand paying the whole bond back plus the four thousand for the interest so then we can just present value the total payments the total cash flow this is the total cash flow we'll present value each period so we can just say equals present value and we'll take the rate point one divided by two comma number of periods one for this first one comma payment zero and then future value is that four thousand and then that's it so same formula I'll double click on it put a zero in front of the P and then I'll copy that down I did that a lot faster but it's the same formula up here we're just present valuing instead of each component the total cash flow which is very useful for projects but you'll use this you know any any time of financing thing it's going to use this type of calculation and then if we just sum that up then we could see that we had a hundred and sixteen thousand cash flow but present value wise at the rate of ten percent per year or five percent per six months is really only worth ninety six four fifty four in current dollars what does this mean in terms of recording the bond well it means that we're going to get cash if we were to sell it at the present value of this amount the bond's going to go on the books for the one hundred thousand and that means that there's a discount of three thousand five forty six so if we post this out then of course cash goes up the bond goes up discount here meaning and I did that really fast I know but this this is just to show you the journal entry related to it so we got the bond payable minus the discount then ninety six four fifty four so the point here is that when you see these in terms of the calculations of the problem they usually will have you calculate the problem first and not really look at the journal entry but if you look at if you connect the two out it gives us makes more sense it's also useful to see how this thing could be broken out a few different ways one because it gets you a better understanding what this formula is doing when this formula can be just numbers just popping out of nowhere if you do it by cash flow it makes more sense plus if you actually do projects and look at that projects and cash flows it's really nice to see things as a cash flow per year and this is very common you'll use it in in managerial accounting finance type of decision-making