Results 1 to 17 of 17

Thread: Sum Number of Days in Months But in Different Years

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Sum Number of Days in Months But in Different Years

    I'm trying to replicate US Treasury Calculation for Bond Interest.
    From what I gleened so far they pay semi-annual interest and take into account leap years in that calculation. They do this by:
    1) Summing the number of days for a series of months
    with that series beginning in May and November of Each Year.
    2) However, if the user purchased a bond for example in June, then only the total number of days from June to October is to be included in that sum.
    3) If the bond is purchase on ANY date in a month, the total number of days for that month are included in the Sum.

    Below is procedure (right now I'm use a database to obtain the number of days in a month for testing, but will move this to a preloaded array in the future). For the inclusive period May to October, the function appears to return the correct value since it relies on incrementing each month. However, for the period November to April, months CANNOT be incremented because 1 is less than 12. Any suggestions/ rework appreciated.

    Code:
    Private Function GetPeriodDays(ByVal thisDate As Date) As Integer
    
       #If kDEBUGON Then
           Debug.Print "Begin GetPeriodDays"
       #End If
    
       On Error GoTo Error_GetPeriodDays
       
       '-----------------
       
       Dim i As Integer
       Dim j As Integer
       Dim iMonth As Integer
       Dim TotDays As Integer
       Dim strTBLName As String
       
       'Objects
       Dim arrMonths() As Integer
       Dim rsTemp As Recordset
    
       '*******
       'STARTUP
       '*******
       strTBLName = "tblMonths"
       
       iMonth = Month(thisDate)
       
       Select Case iMonth
          Case 5, 6, 7, 8, 9, 10
          
             j = 10 - iMonth
             ReDim arrMonths(j)
             For i = 0 To j
                arrMonths(i) = iMonth
                iMonth = iMonth + 1
             Next
     
          Case 1, 2, 3, 4, 11, 12
     
    '>PROBLEM BEGINS HERE as Need to Adjust for ANY date of Purchase
         
             ReDim arrMonths(5)
             j = 1
             For i = 0 To 3
                arrMonths(i) = j
                j = j + 1
             Next
             
             arrMonths(4) = 11
             arrMonths(5) = 12
             
       End Select
    
       '*****
       'MAIN
       '*****
       Set rsTemp = dbDAO.OpenRecordset(strTBLName, dbOpenTable, dbReadOnly)
       With rsTemp
    
          Do Until .EOF
          
             For i = 0 To UBound(arrMonths)
                 If arrMonths(i) = !fldMonth Then
                   TotDays = TotDays + !fldDaysInMonth
                   Exit For
                End If
             Next
             
             .MoveNext
             
          Loop
    
          .Close
       End With
    
       '*******
       'WRAPUP
       '*******
       GetPeriodDays = TotDays
       
       #If kDEBUGON Then
           Debug.Print "End GetPeriodDays"
       #End If
    
       Exit Function
    
    Error_GetPeriodDays:
    
       With TError
          .Data = CStr(iMonth) & " Is Missing"
          .Type = ERR_CRITICAL
          .Src = mstrModule & "GetPeriodDays"
          .Action = MsgAndLog
       End With
       
       Call DoError
    
    End Function
    Last edited by vb6forever; Feb 18th, 2022 at 08:42 AM.

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Sum Number of Days in Months But in Different Years

    Here's one way to do it. For any date, it'll tell you how many days there are in the month for that date:

    Code:
    Public Function DaysInMonth(ByVal TheDate As Date) As Long
        Dim mo As Long
        Dim yr As Long
        mo = Month(TheDate)
        yr = Year(TheDate)
        If mo = 12& Then
            mo = 1&
            yr = yr + 1&
        Else
            mo = mo + 1&
        End If
        TheDate = DateSerial(yr, mo, 1&) - 1&
        DaysInMonth = Day(TheDate)
    End Function
    EDIT: Just to explain what I did, I got the month and year for your date, then incremented the month (incrementing the year and setting month to January if we're in December). Then I created a new date based on that incremented month, setting that date to the first day of the month. Then I subtracted one day (putting us back into our original month, but the last day of it), and then I asked for what day we're on (as it's the last day of the month, so it's the number of days in that month).

    Basically, code in the bowels of VB6 (or maybe even Windows) did all the heavy lifting for us.
    Last edited by Elroy; Feb 18th, 2022 at 08:59 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Sum Number of Days in Months But in Different Years

    So the goal is to get the number of days between X and Y, right? Just get the first of the starting month, and the end of the ending month, then use DateDiff to get the number of days ... This takes care of Nov - Apr as well as Leap Years (when Feb will have 29 days) as well as the years that should be a Leap Year but aren't (like 2000 was).
    So how to get the first of the month? DateSerial ...
    Shooting from the hip here:
    Code:
    Private Function GetPeriodDays(ByVal thisDate As Date) As Integer
    Dim startDate as Date
    Dim endDate as Date
    
        startDate = DateSerial(year(thisDate), month(thisDate), 1)
        endDate = DateAdd(d, -1, DateAdd(m, 7, startDate))
    
      GetPeriodDays = DateDiff(d, startDate, endDate)
    End Function
    The endDate - it looked like you were going out 6 months for the end ... so after getting the first of the month for the purchase date, I add 7 to go out past the sixth month, then subtract one day ... this gives the last day of the 6th month ... then use datediff to get the number of days between start and end.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sum Number of Days in Months But in Different Years

    Huh?
    That's one convoluted way there.

    In that regard i'll mention DateAdd, DateDiff and DateSerial-Functions

    tested with different BuyDates (german Date-Notation)
    Code:
    Sub main()
    Dim BuyDate As Date
    
    Dim StartDate As Date
    Dim EndDate As Date
    Dim PeriodDays As Long
    Dim arrLookUp(1 To 12) As Long
        
        arrLookUp(1) = 4
        arrLookUp(2) = 3
        arrLookUp(3) = 2
        arrLookUp(4) = 1
        arrLookUp(5) = 6
        arrLookUp(6) = 5
        arrLookUp(7) = 4
        arrLookUp(8) = 3
        arrLookUp(9) = 2
        arrLookUp(10) = 1
        arrLookUp(11) = 6
        arrLookUp(12) = 5
        
        BuyDate = CDate("05.06.2022")
        StartDate = DateSerial(Year(BuyDate), Month(BuyDate), 1)
        EndDate = DateAdd("d", -1, DateAdd("m", arrLookUp(Month(StartDate)), StartDate))
        PeriodDays = DateDiff("d", StartDate, EndDate)
    End Sub
    Last edited by Zvoni; Feb 18th, 2022 at 09:23 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Sum Number of Days in Months But in Different Years

    Number of days between x-date and y-date? Pfff, just subtract the lesser date from the greater date.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sum Number of Days in Months But in Different Years

    Quote Originally Posted by techgnome View Post
    So the goal is to get the number of days between X and Y, right? Just get the first of the starting month, and the end of the ending month, then use DateDiff to get the number of days ... This takes care of Nov - Apr as well as Leap Years (when Feb will have 29 days) as well as the years that should be a Leap Year but aren't (like 2000 was).
    So how to get the first of the month? DateSerial ...
    Shooting from the hip here:
    Code:
    Private Function GetPeriodDays(ByVal thisDate As Date) As Integer
    Dim startDate as Date
    Dim endDate as Date
    
        startDate = DateSerial(year(thisDate), month(thisDate), 1)
        endDate = DateAdd(d, -1, DateAdd(m, 7, startDate))
    
      GetPeriodDays = DateDiff(d, startDate, endDate)
    End Function
    The endDate - it looked like you were going out 6 months for the end ... so after getting the first of the month for the purchase date, I add 7 to go out past the sixth month, then subtract one day ... this gives the last day of the 6th month ... then use datediff to get the number of days between start and end.


    -tg

    Not sure if this is correct
    a) Bought in December 2022 (adjusted to 1st Dec), so end of period is April 30th 2023
    b) Bought in Feb 2023 (adjusted to 1st Feb), so end of period is April 30th, 2023
    For a) you add 5 months, and go one day back
    For b) you add 3 months, and go one day back
    (see my Code below)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Sum Number of Days in Months But in Different Years

    Quote Originally Posted by Zvoni View Post
    Not sure if this is correct
    a) Bought in December 2022 (adjusted to 1st Dec), so end of period is April 30th 2023
    b) Bought in Feb 2023 (adjusted to 1st Feb), so end of period is April 30th, 2023
    For a) you add 5 months, and go one day back
    For b) you add 3 months, and go one day back
    (see my Code below)
    It wasn't clear to me what the end date should be from the post. Either way, using DateSerial to get the first of the month for the purchase date, and then using DateDiff should be the way to go.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Sum Number of Days in Months But in Different Years

    First let me thank everyone for their input.
    I obviously was over thinking the problem.

    1) Elroy: just subtract the two dates but would need to
    adjust both buy and sell date first and add 1 to make inclusive.

    2) Techgnome: Using DateDiff should give the same result as Elroy.

    3) Zvoni: Haven't tested yet, but being able to enter just the purchase date
    may be a plus UNLESS Treasury (which I imagine they don't)
    pay interest to the end of the semi-annual period if sold early (i.e. interest just to end of the sale month)

    NOTE: Where this gets more complicated is Treasury adjust their rates every six months (May and November).

  9. #9
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Sum Number of Days in Months But in Different Years

    Quote Originally Posted by vb6forever View Post
    1) Elroy: just subtract the two dates but would need to
    adjust both buy and sell date first and add 1 to make inclusive.
    Yes, I thought/understood that, but I thought the OP should be able to work out those details. Also, depending on the banking/financial institution, the rules are always different. In other words, do you get interest for the day deposited, or not. Often, you get interest for the day of deposit or the day of withdrawal, but not both. Those edge situations can get tricky, but I wasn't sure if those were the subject of this thread or not.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sum Number of Days in Months But in Different Years

    1) if sold earlier than End of period
    Easy. Set SaleDate to the 1st of the Following month and subtract one day using DateAdd and DateSerial.
    Then just compare if the SalesDate is after the regular EndDate. If yes, interest from BuyDate to regular EndDate plus new interest for StartOfNewPeriod to SalesDate
    2) interest rate
    Well, that‘s just a simple multiplier you can pass to your calculation
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Sum Number of Days in Months But in Different Years

    Elroy:
    but I thought the OP should be able to work out those details
    Those edge situations can get tricky, but I wasn't sure if those were the subject of this thread or not.
    Re: OP, he is working those out.
    Re: edge > This is a trial error, best guess process, as Treasury does NOT appear to publish how they calculate,
    so attempting to tie out to some known numbers, hence flexibility in computation is KEY.

    Zvoni: Agree. Comment was just a comment. The issue here being rates are presented at the beginning of the
    semi-annual period but applied at the end of semi-annual period.

  12. #12
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Sum Number of Days in Months But in Different Years

    Also, when calculating interest, there's also the issue of how often it's compounded, such as monthly, daily, or continuous, or any other interval but those are the most common. And just as an FYI, you can compound monthly even when the deposit-withdrawal interval is less than a month.

    And as a further FYI, the more often it's compounded, the more interest there'll be (at the same rate).
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Sum Number of Days in Months But in Different Years

    there's also the issue of how often it's compounded
    I agree. This is semi-annual and using the following I believe is taking this into account.

    Code:
          Value = Value * (1 + (Rate * (DaysInPeriod / DaysInYear)))
    Code is working just can't tie back to Treasury's calculation.
    Coming up short about $30.00

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sum Number of Days in Months But in Different Years

    What‘s the rate for which interval?

    IIRC, the formula is as follows:

    C(n)=C(0) x (1 + p/100)^n

    C(0) = Capital at Start
    n = count of Timeintervals (daily, montly, yearly)
    p = rate per Timeinterval
    C(n) = Capital at the end
    Last edited by Zvoni; Feb 18th, 2022 at 01:58 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Sum Number of Days in Months But in Different Years

    Zvoni: Thanks

    Using the formula (if I am using it correctly) I'm even further off.
    For testing:
    Code:
    C(n) = 10000 * (1 + .0466/100) ^ 2         '2 = semiannual
    
    Treasury I-Bond calculator comes up as $40.00

  16. #16
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sum Number of Days in Months But in Different Years

    What‘s your expected result? 10.953,72 after one year?

    is the rate 4.66% per halfyear?
    Last edited by Zvoni; Feb 18th, 2022 at 04:36 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Sum Number of Days in Months But in Different Years

    This is Treasuries Bond Calculator I'm using:

    https://treasurydirect.gov/BC/SBCPrice

    They also provide Historical Redemption tables:
    https://treasurydirect.gov/indiv/too...historical.htm

    Other info of interest that gives some clues as what they're doing
    https://www.treasurydirect.gov/indiv...esandterms.htm

    What makes things a bit difficult is the composite rate is a function of a fixed rate and an inflation rate.
    (above link shows formula). So depending on the date a particular bond was purchased, its composite rate
    (which varies every six months) is tied to the date of purchase.

    The easiest way to deal with this is use one of the later dated bonds which have a zero fixed rate.
    Hence one only has to try and tie out to the value based on the inflation rate. However, based on
    the link above, the 6 month rotation period is a function of when the bond was bought even though
    the actual rates only come out May 1st and Nov 1st for the next semi-annual period. This raises
    an interesting question in that if a bond was purchased in June, when the new rate comes out in November
    does one continue to use the old May 1st rate for another month (so 6 months of rate is applied) -- OR -- does the new November rate apply?

    Here's the link to the historical composite rates:
    https://www.treasurydirect.gov/indiv...dRateChart.pdf
    Last edited by vb6forever; Feb 18th, 2022 at 04:49 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width