calculating maturity dates
Author Message
calculating maturity dates

I need a formula to add months or years to given date to calculate loan
maturity.  For example the loan closes on 12/1/99 and matures 36 months
later on  X date.  (The calculated beginning and maturity dates are always
the first of the month).  The answer is 11/1/2002.
On occasion I also need to figure a loan closes on 12/5/99 and matures 120
days later on X date.  In this case the maturity date is not the first of
the month but the actual 120th day including 12/5/99.   Feb. 29th, whenever
it occurs, really adds fun to this calculation.
Help me end the embarrassment of being a 48 year old professional sitting in
front of a computer ---counting on his fingers and leafing through a
calendar.

Wed, 18 Jun 1902 08:00:00 GMT
calculating maturity dates
Assume 12/1/99 is in cell a1
in B1 =Date(Year(A1),Month(A1)+36,1)

Returns 12/1/2002

=Date(Year(A1),Month(A1)+35,1)

Returns 11/1/2002

Assume 12/5/99 is in A3
then
=A3+120  returns 4/3/2000
=A3+119 returns 4/2/2000

You will have to be the arbiter of which additive numbers to use to get the
right answer.

Chip Pearson has an extensive page of information on working with Dates and
Times.

http://www.cpearson.com/excel/datetime.htm  Dates and Times
http://www.cpearson.com/excel/DateIntervals.htm Date Intervals
http://www.cpearson.com/excel/datedif.htm  The undocumented Datedif function
(documented in Excel 2000)
Datedif computes the difference between two dates.
datedif("12/1/1999","11/1/2002","m")  returns 35 by the way.

http://www.cpearson.com/excel/DateAdd.htm  Adding Months and Years

HTH,
Tom Ogilvy

Quote:

>I need a formula to add months or years to given date to calculate loan
>maturity.  For example the loan closes on 12/1/99 and matures 36 months
>later on  X date.  (The calculated beginning and maturity dates are always
>the first of the month).  The answer is 11/1/2002.
> On occasion I also need to figure a loan closes on 12/5/99 and matures 120
>days later on X date.  In this case the maturity date is not the first of
>the month but the actual 120th day including 12/5/99.   Feb. 29th, whenever
>it occurs, really adds fun to this calculation.
>Help me end the embarrassment of being a 48 year old professional sitting
in
>front of a computer ---counting on his fingers and leafing through a
>calendar.

Wed, 18 Jun 1902 08:00:00 GMT
calculating maturity dates
there's probably many ways to do this, but one easy way that works for me
is:

assume that cell A1 holds the Loan issue date

cell A3 holds the duration in years
cell B3 holds the duration in months
cell C3 holds the duration in days

= date(year(a1)+a3,month(a1)+b3,day(a1)+c3)

you might need to add a -1 to the end of the formula, depending on whether
you want to include the last day or not.

(in your examples, if you were
adding months, you would add -1 but if you were adding days, you wouldn't -
if you
see what I mean.)

hope that helps.

regards,
Anthony

Quote:

>I need a formula to add months or years to given date to calculate loan
>maturity.  For example the loan closes on 12/1/99 and matures 36 months
>later on  X date.  (The calculated beginning and maturity dates are always
>the first of the month).  The answer is 11/1/2002.
> On occasion I also need to figure a loan closes on 12/5/99 and matures 120
>days later on X date.  In this case the maturity date is not the first of
>the month but the actual 120th day including 12/5/99.   Feb. 29th, whenever
>it occurs, really adds fun to this calculation.

[snip]

Wed, 18 Jun 1902 08:00:00 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages

Powered by phpBB® Forum Software © phpBB Group