-
Feb 18th, 2022, 08:37 AM
#1
Thread Starter
Fanatic Member
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.
-
Feb 18th, 2022, 08:54 AM
#2
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.
-
Feb 18th, 2022, 09:06 AM
#3
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
-
Feb 18th, 2022, 09:11 AM
#4
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
-
Feb 18th, 2022, 09:14 AM
#5
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.
-
Feb 18th, 2022, 09:15 AM
#6
Re: Sum Number of Days in Months But in Different Years
Originally Posted by techgnome
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
-
Feb 18th, 2022, 09:46 AM
#7
Re: Sum Number of Days in Months But in Different Years
Originally Posted by Zvoni
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
-
Feb 18th, 2022, 10:07 AM
#8
Thread Starter
Fanatic Member
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).
-
Feb 18th, 2022, 10:13 AM
#9
Re: Sum Number of Days in Months But in Different Years
Originally Posted by vb6forever
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.
-
Feb 18th, 2022, 10:20 AM
#10
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
-
Feb 18th, 2022, 11:48 AM
#11
Thread Starter
Fanatic Member
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.
-
Feb 18th, 2022, 12:12 PM
#12
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.
-
Feb 18th, 2022, 01:20 PM
#13
Thread Starter
Fanatic Member
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
-
Feb 18th, 2022, 01:47 PM
#14
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
-
Feb 18th, 2022, 03:03 PM
#15
Thread Starter
Fanatic Member
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
-
Feb 18th, 2022, 04:09 PM
#16
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
-
Feb 18th, 2022, 04:39 PM
#17
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|