How to calculate Maturity Date

[WLHagen]

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!

 

[BiocideJ]

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

 

[Dannyh1]

Hi try:

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

 

[Dannyh1]

ha - or previous simpler solution!

 

[WLHagen]

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

 

[WLHagen]

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.

 

[BiocideJ]

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?