# Maturity Date Calculation

1. ## Maturity Date Calculation

I have a date based on which all calculation will occur, known as the Value Date
My Tenor can be either 1,2,3,6 months
For e.g. if the value date is 01.01.2015 and the tenor is 2 months, then 31 days of Jan (Value Date month) + 28 days of Feb will be added to the value date, which will be 1st of March 2014 (Maturity Date)
Uptill here I have it figured out.

If the Value Date is last day of the Month for eg. 28th Feb 14, and the tenor is 1 month, then it should calculate directly to 31st March 2014 (Maturity Date) (Last Days of the next month; Tenor of 1 months added)

I have a list in range A1:A17 that has Holidays (Festivals etc). And apart from those Holidays, all Saturdays & Sundays should be taken as holidays.
So if the Maturity date is coming on a Sunday, it should show the Friday Date (Previous Working day)

I have worked on the problem a bit, Please find attached Excel Sheet for reference.  Register To Reply

2. ## Re: Maturity Date Calculation

Not sure I completely understand your requirements, but this simpler formula should serve you well in your initial calculation

In Details tab

=IF(DAY(\$F\$2+1)=1,EOMONTH(\$F\$2,LEFT(\$F\$1)),EDATE(\$F\$2,LEFT(\$F\$1)))  Register To Reply

3. ## Re: Maturity Date Calculation

Thats awesome.. Wonder why I had to run after such a long formula.. :O

Now the 1st Part is sorted. The date which we derive using your formula should not fall on a Saturday or a Sunday or any Holiday from the Holidays list.

Hope you can help me with that too  Register To Reply

4. ## Re: Maturity Date Calculation

Try

=WORKDAY(IF(DAY(\$F\$2+1)=1,EOMONTH(\$F\$2,LEFT(\$F\$1)),EDATE(\$F\$2,LEFT(\$F\$1)))+1,-1,A2:A17)  Register To Reply

5. ## Re: Maturity Date Calculation

I am unable to edit the above post for some reason hence writing a diff post.

The main Issue I feel is, Holidays can come in sequence, for example 20th-Jan-2015, 21st-Jan-2015, 22nd-Jan-2015, 23rd-Jan-2015, 24th-Jan-2015 can all be holidays, which need to be handled, besides If all holidays are successfully handled and 1 day is subtracted to find out a working day, that days can well be a Saturday/Sunday which again needs to be handled.. Its like a loop that runs around in circles..

By the way, a VBA Function will also help if that is more convenient for you.

Cheers!  Register To Reply

6. ## Re: Maturity Date Calculation

That is insane.. Worked like a charm.. Cheers!  Register To Reply

7. ## Re: Maturity Date Calculation

Hi,

I'd be thankful if you could explain why you used =WORKDAY(IF(DAY(\$F\$2+1)=1,EOMONTH(\$F\$2,LEFT(\$F\$1)),EDATE(\$F\$2,LEFT(\$F\$1)))+1,-1,A2:A17)

Why +1,-1 .. Cant seem to figure out..  Register To Reply

8. ## Re: Maturity Date Calculation

Hi,

1) I have a similar scenario where In case of Holidays or Weekends, the next working date should be displayed instead of the previous one like in the previous example.

2) Only incases where its moving to another month, for example, 1M from 30-Jan-14 would Ideally show 2-Mar-14 (31 Days) where the difference in months is 2 (or more based on the tenor) (March - Jan), the Maturity Date should be the last working day of Feb. Hope that makes sense.

Can you suggest what changes I need to do?  Register To Reply

9. ## Re: Maturity Date Calculation

Why +1,-1 .. Cant seem to figure out..
That's for the WORKDAY function to work..since WORKDAY(Date,0) will return the same day for weekends/holidays
1) I have a similar scenario where In case of Holidays or Weekends, the next working date should be displayed instead of the previous one like in the previous example
=WORKDAY(IF(DAY(\$F\$2+1)=1,EOMONTH(\$F\$2,LEFT(\$F\$1)),EDATE(\$F\$2,LEFT(\$F\$1))),1,A2:A17)

No need for the +1, -1 here 2) Only incases where its moving to another month, for example, 1M from 30-Jan-14 would Ideally show 2-Mar-14 (31 Days) where the difference in months is 2 (or more based on the tenor) (March - Jan), the Maturity Date should be the last working day of Feb. Hope that makes sense.
=IF(EOMONTH(\$F\$2,LEFT(\$F\$1))=EDATE(\$F\$2,LEFT(\$F\$1)),WORKDAY(EOMONTH(F2,LEFT(F1))+1,-1,A2:A17),WORKDAY(EDATE(F2,LEFT(F1)),1,A2:A17))

Hope that helps   Register To Reply

10. ## Re: Maturity Date Calculation

Hi, thank you for all the help.. You've been great.. However the formula is not returning the expected results, just a day here and there. Please check the Excel attached for clear understanding.

Also, I want both the formulas consolidated, instead of 2 different formulas.

This is the last piece of the puzzle and then it will be done. Cheers!  Register To Reply

11. ## Re: Maturity Date Calculation

Hi, with reference to the above post, I feel as If I have sorted it out..

Below is the formula, If you can optimize it - ``Please Login or Register  to view this content.``
Based on what I have tried, it works perfectly.. (New File Attached For Reference)

Thnx a ton for helping me out..  Register To Reply

12. ## Re: Maturity Date Calculation

Ok..try this

=IF(EOMONTH(\$D2,LEFT(\$D\$1))<=WORKDAY(EDATE(D2,LEFT(\$D\$1))-1,1,HolidayList57[Holidays]),WORKDAY(EOMONTH(D2,LEFT(\$D\$1))+1,-1,HolidayList57[Holidays]),WORKDAY(EDATE(D2,LEFT(\$D\$1))-1,1,HolidayList57[Holidays]))  Register To Reply

13. ## Re: Maturity Date Calculation

Reading through this thread I'm not sure whether you were asking different questions or whether this is all one calculation. If it's the latter can you re-iterate all the rules?

I understand you want to add the number of months shown, and if it's the last day of the month you go to the last day of the relevant month, if it's not the last day you go to the same day in the relevant month....but what if that day doesn't exist - what result should you get if the "value date" is 30th December 2014 and the "tenor" is 2M?

....what are the rules on getting a working day, you want to always go back unless that puts you in the wrong month, in which case you go forward?  Register To Reply

14. ## Re: Maturity Date Calculation

Hi,

The new Formula shared by you works in 99% of the Cases, however in case of month ends it sometimes messes up.

For example, if the date is 28/Feb/2015 (Month End) with Tenor of 1M, the Maturity Date should be 31st Mar 2015 (Month End), the formula returns 30th Mar 2015.

See the file attached where I have put both formulas.

Cheers!   Register To Reply

15. ## Re: Maturity Date Calculation

If the Value date is 30/Dec/2014 and the Tenor is 2M, then it should ideally return 28/Feb/15, however it being Saturday it will try to move to the next available working day. Since there are no available working days in Feb 15 after 28th Feb, it will move backwards looking for an available working day and return 27/Feb/15.

Basically the calcualtion goes like -

1) Look for End Date based on Tenor & Value Date, If the Value date is month end, directly return month end based on Tenor.
2) If the date which is returned in Step 1 is a Holiday or Weekend, Move ahead to look for available working day in the same month
3) If there in no scope to move further, only then in the reverse order look for an available working day which should be the answer

Hope It made sense.

Cheers!   Register To Reply

16. ## Re: Maturity Date Calculation

OK, I think this version should work for you

=INDEX(WORKDAY(EDATE(D2+(DAY(D2+1)=1),LEFT(\$D\$1))-(DAY(D2+1)=1)-{-1,1},{-1,1},HolidayList57[Holidays]),IF(MONTH(WORKDAY(EDATE(D2+(DAY(D2+1)=1),LEFT(\$D\$1))-(DAY(D2+1)=1)-1,1,HolidayList57[Holidays]))=MONTH(EDATE(D2,LEFT(\$D\$1))),2,1))  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1