[slide 01]
Hello again!
At this point I assume you have watched the spreadsheet basics video and also maybe even checked out some documentation on the Spreadsheet Resources page.
So, let's apply what we know to solving a couple of problems from problem set 3, to get you started.
[slide 02]
This is the first problem in Problem Set 3. It should be straightforward if you remember undergraduate principles of finance.
Feel free to pause the video here to give yourself some time to read it. As you can see there are 4 numeric inputs, so these are what we have to work with. Let's set up a spreadsheet to solve this problem.
[spreadsheet]
OK, let's start by labeling the problem, Problem 1. And we know that the coupon rate is 4.7%, we know the time is 6 years, we know the rate is 3.7%, and finally we have a face value of 100.
Let us start by setting up a timeline and dealing with the individual cash flows that we expect to receive.
Time goes from 0 which is today until 6 years in the future, so let's drag it down.
And then we have the coupon payments, which are going to be 4.7% times the face value, and let's lock in these references since we are about to fill them down all the way, and finally let's remember at the end to add the repayment of the face value. These are our cash flows.
Now we can find the individual present values of these cash flows using the discount rate that we are given. Cashflow divided by 1 plus discount rate, lock that in right away, raised to the power of time, and fill this down all the way. Double check that everything is looking ok, and add everything up, to find the total price of this bond, which apparently is 105.29.
Now another way that we can find the price of this bond is by using the present value of annuity formulas from principles of finance, which you may or may not still remember. Let's give this a shot, it should come out with the same result.
So we start with the coupon payments, each coupon is that times the face value, and divide by the discount rate, multiply by (1 - 1/(1+r)^t), and that is the present value of the coupon payments, plus the present value of the face repayment, f/(1+r)^t.
And if we did everything right, we should get exactly the same result. And there we are, 105.29.
Finally, another way we can find the price is to use the built-in spreadsheet function called 'price'. Let's see what arguments it requires. Notice the first argument is settlement, second maturity, rate, yield, redemption, etc. How do you know what do all of these arguments actually mean and what does the function expect as input?
One easy way to find out is to use your favorite search engine and look for the documentation on the price function. Here is a sample website that comes up with information on the arguments that are required. You can see that we start with the date on which we buy the bond, the settlement date, second argument is maturity date of the bond, then we have coupon rate, yield to maturity, redemption value or face value at the end, number of coupon payments, and basis is the day count convention. There is some more documentation on the basis and what the different options are.
So, let's see what happens. First, we need the settlement date. Let's assume today is august 25 2019. We can't just start typing numbers, in order to designate to excel that this is a date, we have to use the date function, and use the year, 2019, month, and the date, and then for maturity date again we use the date function, and make it exactly 6 years in the future, 2025. Finally we want the coupon rate, 4.7%, the yield, 3.7%, the face value at the end, $100, payment frequency is once per year, and finally the basis doesn't really matter since we have exactly one whole year between now and the first payment, so let's keep it at 0 if you want, and let's see what happens.
If we did everything right, we should get exactly the same price, and there it is, as you can see.
Now it doesn't really matter to me what method you use to solve the problem, but it is very important to you to understand what it is you are actually doing and what is the mathematics behind the formulas that you use. So I hope you don't just go around and use built in excel functions without knowing what the arguments are and what the mathematics is, because whenever you encounter some situation that is not exactly canonical that fits the formula, you will need to know what to modify to fit the situation to get the correct results.
Additionally, I will mention that it is very important to check that your result is in accordance with what you expect. In this case here we see that the coupon rate is 4.7% and the discount rate is 3.7%, so we indeed expect the bond to be selling at a premium relative to face value, so our result of 105.29 is in accordance with that expectation. If that weren't the case, you would know that we did something wrong, because a bond with a yield that is lower than the coupon rate must be trading at a premium to face value.
[slide 04]
Now let's try another problem. Take a moment to give it a read. As you can see, there are again 4 numeric inputs, so we'll start by inputting that into the spreadsheet.
[spreadsheet]
Label the problem, Problem 4.
And what we know is that time to maturity is 2 years, coupon rate is 6%, price is 99.1, and the face value at the end is 100.
What we don't know is the yield to maturity. Let's see how we can figure that out.
When in doubt, it never is a bad idea to build a time line of cash flows that you expect, so let's do that.
Time, 0, 1, 2
cash flows, 6% of 100 dollars for this first cash flow, same thing for second cash flow, plus 100 at the end.
Now what are we going to do to find the present value of these cash flows? we need a discount rate for that. let's just make a guess of what the discount rate could posibly be. The bond is trading at a discount to par value, which means the YTM must be something higher than 6%, so perhaps I'm going to guess that it is 7%. Let's use this rate to find the present value of the cash flows.
Cashflow divided by (1+r)^t. Fill it down, and let's add them up.
If 7% is the correct rate, then our price will be exactly 99.1 dollars per 100 of face value. Let's see, 98.19, not quite, we have to tweak the discount rate in order to reach a price of exactly 99.1.
We can of course tweak it manually, let's say we try .069, and we see that price has improved and has gotten closer to 99.1, but this problem of having to solve problems numerically, by trial and error, is so common that spreadsheets come with tools that allow you to specify what the target data is and what cells to tweak until you get the desired result. So let's give it a shot.
Here in LibreOffice there is a tool called 'goal seek', it is right here. So our formula cell is here, the price, target value is 99.1, and the variable cell that we are trying to tweak is the YTM. Let's say ok, and success, it has found the rate that produces the result of 99.1.
That is pretty awesome, if you haven't seen goal seek before, it certainly is going to be one of the favorite tools in your toolbox.
Another way to solve this problem is to use a built-in spreadsheet function called 'yield'. As you can see it takes similar arguments as the other built in function for price, starting with the settlement and maturity dates and the other characteristics of the bond. Let's look at the documentation for this sequence of arguments.
Here is a website that documents the use of the yield function, we can see the settlement date is our purchase price, maturity is maturity date, coupon rate, price, redemption is the face value, frequency and basis with the same codes as previously.
so let's see if we can use this function to come up with the same yield as we used for goal seek.
As before, we will start with the settlement date and use the date function and assume that today is august 25, so our maturity date is going to be 2021 august 25, then we want coupon rate 6%, price 99.1, face value 100, frequency is once per year, and for the basis we can leave it at 0. Let's see if we get the same result. Apparently not!
Ok, this is going to happen to you once in a while and you are going to have to figure out what's wrong. Let's examine our function inputs and see if we can figure out what we did wrong. I think I see the problem, instead of 2019 we put in 2015. let's fix that and see what happens. There we are, that is exactly the same result.
Does this result make sense? Since the bond is trading at a discount to face value of 100, that means the ytm must be higher than the coupon rate, and in this case it's about 6.5% which is higher than 6% coupon rate, so yes, it is a sensible result. Always important to sanity check your numbers, because garbage in, garbage out.
As another bonus way of finding the rate, if you realize that this is equivalent to the internal rate of return problem from corporate finance, treating this investment as a project with certain outflows and cash flows, we can use the built-in IRR function to find the YTM on this bond.
First, we are going to put in our initial outflow as minus the price, this is how much we have to pay up front, and in future years this is how much we are going to receive.
Let's use the irr function, and supply this stream of cash flows, and there we go, 6.49%. If you want to convince yourself that this is exactly the same, increase the number of decimal points that the answer shows by default, and you can see that this is the same result.
[slide 06]
I hope you find this helpful. See you on the discussion boards!