 In this presentation we will calculate the bond price explaining how this can be done using present value formulas within 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 then 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. Remember that the bonds is going to be a great tool for both accounting and finance to describe the present value calculations. That's why it's going to be used often times it had two cash flows related to it. One's going to be the face amount of the bond that's going to be due at the end of the term of the bond. In our case it's going to be two years semi-annual or four time periods and the other is the flow of interest. So bonds are a great example because they have the two types of present value problems that we need in one area. So even if you're not in an area where you're dealing with bonds all the time they're still going to be used and useful to understand present value types of calculations. So here we've got the bond is going to have one cash flow of 100,000 at the end of four periods or two years and we need to figure out what the present value is in order to price it back here at time period zero. And then we have these four payments in terms of the annuity 4,000 and we need to take those and present value them. We could take each period and present value each payment and present value it but the easier thing to do is to present value an annuity when it's applicable and present value the one amount when it's applicable and therefore think of that about these as two basically separate cash flows that we're going to have present value separately. So we can do this multiple different ways and it just depends on what tools you have and where you are in order to know how to do it. What you want to know is just that there's different tools to do it anytime someone uses a different tool what are they doing the same thing and when can you apply these tools and what's actually happening here. So that's what's actually happening we're present value in this information. We could do that with a formula. We could simplify this process by using a calculator or using Excel as we'll do here or tables. It's all the same stuff that we're doing. Just be aware of those different kinds of ways you may be asked to do it depending on what you're where you are if you're taking a test. Typically they give you tables and it's an accounting test. If they're not as nice to you they'll make you do the math. If they if you're somewhere not taking a test of course you'll have Excel hopefully or or a calculator to do this information. So the present value within Excel what we're going to do is we're going to we're going to do this two different ways again we're going to take the present value of the payment that's due at the end of the four time periods or two years and then we're going to present value the interest. So to do this we're if we have Excel we're just going to go to the formulas here. We're going to do this by by rather than just typing in the formula by going to this insert functions which will give us a formula box and make it a little bit easier some descriptions. We're going to type in present value and then get present value that's what we're looking for if you type in present value the entire word present value then you'll still find the same formula and that's going to be the present value formula as it explains down here that's the one we want. So we're going to say OK and then we'll just enter our data here and it'll give us some descriptions as we go through each of these down here if we click into this item it'll give us a description but we're just going to enter our data so the rate is the first thing it asks for we want to take the rate of ten percent because we're looking at the market rate to present value this thing and we're going to divide it by two so 0.1 or 0.1 ten percent divided by two to get the market rate for a semi-annual period and then we're going to take the number of payments which is going to be four and that's going to be the two years and we're going to pay every six months so we'll take that and multiply it times two to get four periods so that's the most confusing thing here is we're saying four periods not four years every six months and therefore the yearly rate is not ten percent which is given but ten percent divided by two because it's a rate for every six months rate and then we're going to take the future value that's what this is now the most confusing thing about this formula is that we use the same thing for an annuity and for a present value of one but it's the difference between these two fields that we use so we just kind of know on the annuity tables when we looked at tables we had different tables that we used in Excel we're going to say this number here represents payments how many payments we make so if it were an annuity as we will see when we do the interest portion we would put payments here but in our case we're only making one amount at the end of the time period one payment in our case at the end of the time period but we're really just trying to present value one amount so in other words we know the future amount we know what we're going to pay at the end of four time periods or two years it's a hundred thousand that hundred thousand is the future value because it's the actual dollar amount at the end of four time periods so we call that the FV or the future value so that's what we have notice that this is a required field because it's highlighted it seems like so it looks a little it's a little confusing to use this whereas this is not highlighted and therefore you would think it would not be a required field but in this case because we're using the same kind of box for Excel to do present value of an annuity or one these two are the most confusing components so this is how we're going to do this we're going to assume the calculation actually does it for us down here and that'll give us the idea of course that if there was nothing else going on if we're paying out one hundred thousand at the end of the time period and there's no interest then we would expect to get eighty two thousand two seventy now for a market a fair market transaction so that would be the eighty two thousand now if we hit okay here you could type this just in excel and it would look like this it's worth going through here and and looking at this uh... for excel it's equals present value that'll be the formula and then brackets and if we click on this item it'll give us the rate which we had point oh i mean point one divided by two or five percent and then comma just like it says here and then we're on here number of periods and then two commas what what is going on there that means that we're skipping over the payment remember we didn't have a payment so we could put a zero in there or just two commas that it's nothing and then we put the negative one hundred thousand that negative just to flip the sign that's the only reason you have a negative otherwise it would result in a negative answer so it has a positive answer it would be negative okay so then we put the hundred thousand that gives us the eighty two okay so then we have the present value of the annuity which is this form this formula we're going to do that in excel same thing we go to the same but this time we're going to take the rate same rate ten percent divided by two same number of periods four time periods and then the amount that we're going to pay is the one hundred thousand times point oh eight the stated rate on the bond divided by two because we're paying it every six months that four thousand that's how much we're going to pay every six months it's not the future value this time it's an annuity payment stands for payment so we're going to pay that each of those four time periods that's what this is saying there is no we don't need the future value or the type so this is the trick between these two this is the trick and knowing that the number of payments does not mean years but payments and knowing that the rate has to be the rate per period not per year then we're going to say okay and then this will be our present value formula here is going to get us the fourteen one eighty four which makes sense because we're going to pay four thousand four times which would be sixteen thousand if we present value it we expect an amount greater than four thousand less than sixteen thousand if we see the formula in this format we got present value times the rate point one divided by two and then we've got the number of periods four and in the payments we have this time four thousand we don't have any future value or any type not needed and that's what gives us our our calculation so if we add up our components then we've got the present value the hundred thousand eighty two two seventy the present value of the payments fourteen one eighty four or present value ninety six four fifty four now we're going to do this one other way that you could easily do in excel just to give us a better idea of what's happening here we could try to present value each time period which is an easy thing to do when using formulas in excel and it's it's going to be something that hopefully makes it uh... look a little bit different be able to see things it from a different angle so we're gonna put the number of periods here and we're gonna try to say what's the cash flow happening and then present value each time period and you'll see we'll get to the same result here so we're gonna say that uh... the bonds in period one at the end of the first year uh... there's there's no cash flow happening the interest however at the end of period one we're gonna pay four thousand now the period one is every six months so after six months after the first period we're not paying back the bond we are paying interest so the total zero plus four thousand is four thousand now if we were to present value just this four thousand not doing an annuity but just present value each time period at the end of six months we use the present value formula to present value one at the end of this time period and we would bring that that would be worth three thousand eight hundred and if we did that for period two six months later one year later now we'd say the bonds still we're not going to pay back any bond no cash flow there we are going to pay four thousand at the end of year two again and that means that the total zero plus four thousand is four thousand if we were to present value this four thousand at the end of one year or two six month time periods it would only be worth three thousand six twenty eight this is easy to do with with a formula in excel so and then if we go in in three and it's the same four formula the present value formula we can copy it down we don't even have to type it in there and then we've got the bond we pay another four thousand for the total zero plus four thousand but this four thousand at three years or a year and a half three time periods or a year and a half it's only worth three thousand four fifty five so of course the values going down as the time period increases time zero where we are now and then period four now we're going to pay back the bond because that's the end of two years a hundred thousand is going to be paid back we're paying that four thousand of interest that we pay every six months so total then one hundred and four thousand if we if we present value this whole one hundred and four thousand which is four time periods out or two years it's only worth eighty five thousand five sixty one so if we sum this up then we're going to say the cash flow is a hundred thousand that we're going to pay back at the end of the bond sixteen thousand that we're going to pay an interest in dollars a total of one hundred and sixteen which could be calculated here or here to get that one sixteen and then the present value however is the three thousand eight ten plus the three thousand six twenty eight three thousand four fifty five eighty five five sixty one or that sixty or that ninety six thousand five forty five this is often a useful way to see it in excel easy to do in excel when doing things by hand however you'll notice more tedious for us to present value each year it's easier for us to present value the annuity portion using an annuity table and then present value the bond portion that's going to be due at the end of time period separately and then add them together if you use an excel this is nice because you get to see uh... the cash flow on a yearly basis and present value on uh... a yearly basis so again that the transactions just going to be cash is going to go up when we record this we're going to sell it for ninety six four fifty four the bond goes on the books for the hundred thousand the discount is the difference three thousand five forty six cash is increasing bond on the books discount carrying value hundred thousand minus the discount