# Bond Dates

May 18, 2017 - by Bill Jelen

In today's Dueling Excel, Mike and Bill attempt to deal with Bond Dates. Say that you buy a six-month bond in the middle of the month. You need to calculate a full month's interest for months 2, 3, 4, 5, 6 but only half a month in month 1 and month 7.

## Watch Video

• Goal is to fill start/end dates for tracking a bond monthly
• Bill's method. EOMONTH. EDATE.
• IF to pull EDATE over if it is MIN(EDATE,EOMONTH)
• Helper column calculates a new EDATE when the EDATE is used
• Start date in next row is easy: Former End Date + 1
• Mike's method:
• DNF

### Auto-Generated Transcript

• hey welcome back it's time for another
• two dueling Excel podcast I'm Bill Jelen
• from MrExcel I'll be joined by Mike
• government from ExcelisFun our
• episode 184 bond dates today's questions
• sent in via YouTube someone has a bond
• investment on May 19 as a six month
• maturity and so every month they need to
• calculate do some sort of calculation
• right so from that start date go out to
• the end date and what they're trying to
• do is they're trying to fill this list
• of dates
• all right based on that six months up
• there right and the whole trick is from
• May 9th if you go out six months in that
• month see it's always just the whole
• month a whole month the whole month a
• whole month but when we get to the month
• where it matures we need to calculate
• bond a the first bond from the first of
• November until the maturity date and
• then we start on the next day and go to
• the end of the month and that is from
• the first one what's the end the month
• all the way up until you get to 12
• months after the bond because that's
• when the second bond would mature and so
• on down through the list all right so
• now here's here's my attempt at this
• alright and what I did in case you have
• a different number of months maybe
• 12-month bond or 3-month bond or
• something that I made this be a variable
• up here but first off from the start
• date it's really simple we just have to
• get to the end of the month I'm going to
• use a function back from the days of the
• old analysis toolpak but since Excel
• 2007 it's a full breaded Excel function
• from the start date going out 0 months
• that'll get us to the end of this date
• and hey the first time they use this
• it's going to give you a number like
• this 42 521 well that's just a serial
• number
• we just need to force Excel to display
• that serial number as a date all right
• so there we go that's simple from the
• start date to the end date and then the
• next start date well that's the simplest
• thing in this whole video equal this
• plus one perfect and then we use the EO
• month there like that and copy down to
• however many rows you need to copy down
• all right it's all beautiful except for
• when we get to the point where we have
• the maturity date what I'm calling over
• here is a special date the next special
• date now what I'm going to do I'm going
• to use this as a helper column out here
• to help me keep track of this and I'm
• going to use a function that I
• don't use very often called edate so ej
• starts from the original investment date
• May 9th and it goes out some number of
• months in this case b1 and months and
• what it does it gets us to the date
• where we have to split the month and a
• half alright so now that I know what the
• next special date is you know you might
• think that I'm going to use an if
• function alright and if function to say
• you know is the value and column C less
• than the value and column B if so use
• the value from column C otherwise use
• the value from column B but know there's
• this awesome trick that I love using if
• we need to do that whole Matt you know
• take take this one if it's less or that
• one of it's less all you do is a yes for
• the mint the mint of what we have
• calculated in column B comma and then
• what we have calculated in column C like
• that alright and now in this case the
• choice was 531 or November nights and of
• course 531 but when we get down to
• November it's all going to work all
• right now our special dates we can't
• just copy this down if I was going to
• copy down by the way I would have
• pressed that for there to put the dollar
• signs in but I knew that this one's not
• going at comp people what I'm going to
• do is say hey look go look and see if in
• the last row the ending date was the
• special date and if it was if the ending
• date was a special day we need to
• calculate a new special date and the new
• special date is going to be the e date
• of that ending date comma and then go up
• to six months and this time I'm going to
• press f4 to put the dollar signs in
• otherwise if this isn't a month where we
• just use the special dates then just use
• the previous special date like that all
• right and so I want to copy that one
• down okay now here the min didn't copy
• that down need to copy that down at this
• point all right and it should be working
• so we invested on May 9th that means
• that on November 9th we need to split
• the month so right there there's the
• partial month for bond one and then we
• buy bond two and are calculating from
• the 10th through the end of the month
• and then from here so on May 9th is when
• we reinvest our November 9th is when we
• reinvest so May 9th of the next year
• better be the spot where we get the
• partial month
• and let's just do a test here let's do a
• three-month bond and what we see happen
• is now five nine it matures on August
• 9th and we get two months for August and
• then two months for November and so on
• right so we can put in any number up
• here twelve months or two months or or
• whatever and it's going to do the
• calculation correctly all right now Mike
• that was convoluted how to use EEO
• months at a uz date I used the min trick
• and then finally and if and how to build
• that whole thing let's see what you have
• well here's the response from Mike sorry
• after five hours of trying I can't even
• understand what he's talking about
• alright so wrap-up goal is to fill of
• start end dates for tracking up on
• monthly we used a LM eel month in edate
• if to pull a date over fits the min of
• Eid a tour yo month helper column
• calculates a new date when the e date is
• used and then the start date of natural
• is easy just the former end date plus
• one
• Mike's method no man way I want to thank
• you for stopping by we'll see you next
• time for doing Excel podcast from mr.
• Excel and Excel is fun