How to calculate Maturity Date

WLHagen

Board Regular
Joined
Sep 18, 2009
Messages
177
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top