Bi-Monthly DateAdd Code

S

Stephen Lynch

Anyone have code that will figure out a twice a month date. I really do not
want to code to deal with month ends.

I am building an amortization schedule and some need to pay twice a month.

Right now I am using Dateadd for all scenarios except this. I can easily
add 14 days to the start date , but if it is the end of the month then I
need to account for it.

If paydateday is 7, then frequency is 7 and 22.
If paydateday is 8, then frequency is 8 and 23. etc.

But

If paydateday is 15, then frequency is 15 and date of end of month.

Any ideas?

TIA

Steve
 
J

John W. Vinson

Anyone have code that will figure out a twice a month date. I really do not
want to code to deal with month ends.

I am building an amortization schedule and some need to pay twice a month.

Right now I am using Dateadd for all scenarios except this. I can easily
add 14 days to the start date , but if it is the end of the month then I
need to account for it.

If paydateday is 7, then frequency is 7 and 22.
If paydateday is 8, then frequency is 8 and 23. etc.

But

If paydateday is 15, then frequency is 15 and date of end of month.
Click to expand...

What if paydateday is 17? or 28? or even 31?

What's the business logic? how is paydateday set, and what effect should it
have on the payment dates? Just what do you mean by "frequency" (clearly not
the common use of the term)?
 
S

Stephen Lynch

John:

I am redeveloping a db that we use to process 401k plans. A participant can
take a loan and the payments have to correspond with the paydates of the
company.

The problem cases are the companies that pay the mid and end of month.

My code as attached works fine for all circustances except for the companies
that pay twice a month, so I need to revisit how I am going to do it.


********* Portion of code ******************

Select Case Frequency
Case "52"
Interval = "ww"
CounterAdj = 1
Case "26"
Interval = "ww"
CounterAdj = 2
Case "24" ************ Tried this no go
Interval = "m"
CounterAdj = 0.5
Case "12"
Interval = "m"
CounterAdj = 1
Case "4"
Interval = "q"
CounterAdj = 1
Case "2"
Interval = "q"
CounterAdj = 2
Case "1"
Interval = "yyyy"
CounterAdj = 1

End Select

FVal = 0 ' Usually 0 for a loan.
If InterestRate > 1 Then InterestRate = InterestRate / 100 ' Ensure
proper form.
PayType = 0 'Payments made at the beginning of the month

Payment = Abs(-Pmt(InterestRate / Frequency, TotPmts, BeginPrincipal, FVal,
PayType))
RemainBalance = BeginPrincipal

'loop through each payment

For Period = 1 To TotPmts
EB = RemainBalance
P = PPmt(InterestRate / Frequency, Period, TotPmts, -BeginPrincipal,
FVal, PayType)
P = (Int((P + 0.005) * 100) / 100) ' Round principal.
I = Payment - P
I = (Int((I + 0.005) * 100) / 100) ' Round interest.

PD = DateAdd(Interval, Period * CounterAdj, StartDate) ' here I
generate the dates for the Amort Schedule
EB = RemainBalance - P
RemainBalance = EB
 
R

raskew via AccessMonster.com

Hi -

Re the last day of month:

DateSerial(year([dteMyDate]), month([dteMyDate]) + 1, 0)

returns the last day of the month, regardless of the month's length.

Bob
Stephen said:
John:

I am redeveloping a db that we use to process 401k plans. A participant can
take a loan and the payments have to correspond with the paydates of the
company.

The problem cases are the companies that pay the mid and end of month.

My code as attached works fine for all circustances except for the companies
that pay twice a month, so I need to revisit how I am going to do it.

********* Portion of code ******************

Select Case Frequency
Case "52"
Interval = "ww"
CounterAdj = 1
Case "26"
Interval = "ww"
CounterAdj = 2
Case "24" ************ Tried this no go
Interval = "m"
CounterAdj = 0.5
Case "12"
Interval = "m"
CounterAdj = 1
Case "4"
Interval = "q"
CounterAdj = 1
Case "2"
Interval = "q"
CounterAdj = 2
Case "1"
Interval = "yyyy"
CounterAdj = 1

End Select

FVal = 0 ' Usually 0 for a loan.
If InterestRate > 1 Then InterestRate = InterestRate / 100 ' Ensure
proper form.
PayType = 0 'Payments made at the beginning of the month

Payment = Abs(-Pmt(InterestRate / Frequency, TotPmts, BeginPrincipal, FVal,
PayType))
RemainBalance = BeginPrincipal

'loop through each payment

For Period = 1 To TotPmts
EB = RemainBalance
P = PPmt(InterestRate / Frequency, Period, TotPmts, -BeginPrincipal,
FVal, PayType)
P = (Int((P + 0.005) * 100) / 100) ' Round principal.
I = Payment - P
I = (Int((I + 0.005) * 100) / 100) ' Round interest.

PD = DateAdd(Interval, Period * CounterAdj, StartDate) ' here I
generate the dates for the Amort Schedule
EB = RemainBalance - P
RemainBalance = EB
[quoted text clipped - 20 lines]
not
the common use of the term)?
Click to expand...
Click to expand...
 
Top