+ Reply to Thread
Results 1 to 16 of 16

Maturity Date Calculation

  1. #1
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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.
    Attached Files Attached Files
    Cheers!
    Deep Dave

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    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)))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    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)

  5. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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!

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Maturity Date Calculation

    That is insane.. Worked like a charm..

    Rep Added

    Cheers!

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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..

  8. #8
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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?

  9. #9
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    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

  10. #10
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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!
    Attached Files Attached Files

  11. #11
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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..
    Attached Files Attached Files

  12. #12
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    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]))

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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?
    Audere est facere

  14. #14
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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!
    Attached Files Attached Files
    Last edited by NeedForExcel; 01-22-2015 at 12:58 AM.

  15. #15
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Maturity Date Calculation

    Hi daddylonglegs,

    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!
    Last edited by NeedForExcel; 01-22-2015 at 01:04 AM.

  16. #16
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate average until date = maturity
    By todd18us in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-21-2013, 08:02 PM
  2. Replies: 6
    Last Post: 10-03-2011, 04:26 PM
  3. yield to maturity
    By Tish in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-06-2007, 01:15 PM
  4. Calculate maturity date on loan
    By pforce in forum Excel General
    Replies: 1
    Last Post: 07-19-2007, 06:38 AM
  5. Yield to Maturity
    By broker in forum Excel General
    Replies: 1
    Last Post: 11-29-2006, 04:09 AM

Bookmarks

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