By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,056 Members | 1,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,056 IT Pros & Developers. It's quick & easy.

Calculate next available date

didajosh
P: 47
Fund-----------LockUpDate----------Liquidity
A-----------------12-31-2007-------------6
B-----------------02-29-2008-------------3
C-----------------05-31-2008-------------12

There are funds that are locked up, once the lock up period is over they are available

according to their liquidity - semi annually(6), quarterly (3) or yearly (12).
So if the liquidity is semi-annual, user can withdraw funds every six months, after lock

expiration.
I want to know according to me As-Of-Date, what funds are available on WHAT date?
Eg. If my As-Of-Date = 01-08-2009, then
Fund-----------Availability
A-----------------06-30-2009
B-----------------28-02-2009
C-----------------31-05-2009

Availability will alter according to As-Of-Date, which the user will provide during the run-

time.
Can you please suggest, if there are some changes that I need to do in the database,

introduce a field etc.
How can I achieve this result?
Please help :(

Regards,
Dipali
Jan 9 '09 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Dipali.

Though, your example is not excessively consistent I would assume availability date is supposed to be a result of addition of a quantity of months which is a given liquidity multiplied by an appropriate natural number.

To achieve this you may:
  • Find a difference in months between [LockUpDate] and [AsOfDate] using DateDiff() function.
  • Divide it by liquidity rounding to the greatest integer using something like the following:
    Expand|Select|Wrap|Line Numbers
    1. - Int ( - MonthsDifference / Liquidity )
    2.  
    Thus, you obtain difference in months from [LockUpDate] till the nearest availability date after an AsOfDate.
  • Add the months difference to [LockUpDate] using DateAdd() function.
Jan 11 '09 #2

didajosh
P: 47
Hi,
Thank you for your reply.
Your solution gave me a new direction of

thoughts.
Let me again explain my problem with an example

Fund-----------LockUpDate----------Liquidity
A-----------------12-31-2007-------------6

my As-Of-Date = 01-08-2009

after its lock up, this fund will be available

on
6-30-2008
12-31-2008
6-30-2009
12-31-2009....and so on...
since my As-Of-Date is 01-08-2009, the nest

availabe date more that it is...

6-30-2009.

So basically Available date can be more than or

equal to As-Of-

What you are saying is:
1. Find a difference in months between

[LockUpDate] and [AsOfDate]
= 13
2. Divide it by liquidity rounding to the

greatest integer
= (13 / 6) = 2
3. Add the months difference to [LockUpDate]

using DateAdd() function.
12-31-2007 + 2 MONTHS = 02-28-2008

Which is smaller than the As-Of-Date. !!

What do I do...??
I am stuck here....

Please help.

Dipali
Jan 12 '09 #3

FishVal
Expert 2.5K+
P: 2,653
Hello, Dipali.

@didajosh
Not right. The result should be 3 (rounding to greatest integer).
And it should be multiplied by [Liquidity] (this part I missed, sorry) to get difference in months.

So 3*6=18 months

Add the months difference to [LockUpDate]

using DateAdd() function.
12-31-2007 + 18 MONTHS = 06-30-2009

Kind regards,
Fish.
Jan 13 '09 #4

didajosh
P: 47
Hi Fish,
What a solution...!!!!! Awesome...!!! Thank you so much...!!
I am sorry for a late reply, I was away form office due to some emergencies.
I could have never thought of it...!!!
I am in the process of trying it....and I will let you know about the update....
One again...thank you...for your help and time...

Regards,
Dipali
Feb 2 '09 #5

didajosh
P: 47
Hi Fish,
I am done with the report and your solution helpled me a lot. Thank you so much. I could'nt have done it without your help.
Regards,
Dipali
Feb 12 '09 #6

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Best regards,
Fish.
Feb 12 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.