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 of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.