[slide 01]
H!
With this video, I'd like to get you started on spreadsheet assignments and major assignment 1.
[spreadsheet]
This is the spreadsheet for our first major assignment. Notice how there is initial information given to you at the top, and there are a number of yellow shaded regions where I expect you to do calculations for submission. You may perform any other auxiliary calculations anywhere else in the spreadsheet, but I want to see the final results in the yellow shaded areas where I expect to see them.
Let's get started. Previous payment date, this bond has semiannual coupons, paid on may 15 and november 15. Next payment date is 2019 november 15, so previous payment date would be 2019 may 15. This lets us calculate the number of days in this first interest payment. Notice how the spreadsheet will automatically do arithmetic on dates. We can take the next payment date minus the previous payment date and find that this coupon period has... let's format this not as a date but as a number... 184 days.
So this means that if we want to calculate the days until the next payment period, we'll take 2019 11 15 and subtract the current date, which we assume is august 10. And we have 97 days until the next coupon.
Based on these two pieces of information, we can calculate how many periods remain until the next coupon payment date, which is going to be 97 divided by 184 which is a little more than half of a period left.
So for the next time remaining until the coupon we will just add 1 to the previous amount, and fill it down, and we have our timeline for how many periods remain until each of the future payments.
Please note also that you should do all of the calculations actually using formulas in the spreadsheet, rather than trying to do them somewhere else and typing them in, because I'd like to be able to see what you're actually doing, if something is wrong I want to be able to check the formula and see where the number is coming from.
For example when I'm filling down the cashflows, if you look at the bond, it is a 5% coupon semiannual bond, so obviously every half year we get 2.5 dollars. But instead of just calculating 2.5 and putting it in, I suggest we put in the face value of 100 times 5% divided by 2, in order to calculate your coupon payment based on the information provided. And then of course we can lock in the reference to the coupon amount and fill this down all the way to get our coupon payments. And at the end we'll add 100 dollar face value repayment.
To calculate the number of cashflows, again don't manually count how many items there are in this column, use the nice built in count function and count the number of values that we expect to receive in the future, which happens to be 11.
[slide 02]
I hope you find this helpful. See you on the discussion boards!