Results 1 to 7 of 7

How to calculate Maturity Date

This is a discussion on How to calculate Maturity Date within the Excel Questions forums, part of the Question Forums category; For all: I'm using Excel 2007. I need to calculate the maturity date on a spreadsheet of loans of varying ...

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Location
    Northern VA
    Posts
    137

    Default How to calculate Maturity Date

    For all:

    I'm using Excel 2007. I need to calculate the maturity date on a spreadsheet of loans of varying term lengths. I have the "first payment date" (always the first of a month) in Column C, and I have the term of the loan (in months) in Column H. I need to calculate the Maturity date in Column D. For Example:

    C1= 01/01/2012
    H1= 180
    D1 should be 12/01/2026?

    Thanks in advance for a prompt reply!

  2. #2
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,419

    Default Re: How to calculate Maturity Date

    This will give you 12/1/26
    =EOMONTH(C1,H1-2)+1

  3. #3
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,126

    Default Re: How to calculate Maturity Date

    Hi try:

    =DATE(YEAR(C1),MONTH(C1)+H1-1,DAY(C1))

  4. #4
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,126

    Default Re: How to calculate Maturity Date

    ha - or previous simpler solution!

  5. #5
    Board Regular
    Join Date
    Sep 2009
    Location
    Northern VA
    Posts
    137

    Default Re: How to calculate Maturity Date

    Thanks so much, Danny! Your formula worked like a charm!

  6. #6
    Board Regular
    Join Date
    Sep 2009
    Location
    Northern VA
    Posts
    137

    Default Re: How to calculate Maturity Date

    gah, it just gets more convoluted as we go along!

    Perhaps I should have worded my request a different way?

    I need to calculate the number of months till maturity, given that I have the first payment date (which is always the first of the month). My data is all "as of 12/31/2011", so I thought I was being oh, so smart by formulating the maturity date.... now I'm having trouble calculating the number of months from 12/31/2011 to Maturity date.

    I don't guess I really need the maturity date, if there is an easier way to calculate the number of months to maturity given that I have the first payment due date, and the number of months of the loan.

    Again, thanks in advance for any assistance.

  7. #7
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,419

    Default Re: How to calculate Maturity Date

    It's not statistically precise, but if you are looking for a number as a pretty good approximation, this should do.

    =(DATE(YEAR(C1),MONTH(C1)+H1-1,DAY(C1))-FLOOR(NOW(),1))*12/365


    Also, as an aside. Wouldn't 180 months from 1/1/12 be 1/1/27?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com