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

Download File

Download the sample file here: Duel184.xlsm

Title Photo: Counselling / Pixabay