MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Bond Dates


May 18, 2017 - by Bill Jelen

Bond Dates

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

Download File

Download the sample file here: Duel184.xlsm

Title Photo: Counselling / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.