Microsoft Office Tutorials and References
In Depth Information
Using EDATE to Calculate Loan or Investment Maturity Dates
• end_date, start_date
end_date, start_date — The two dates between which you want to know
the number of days. If either argument is text, that argument is passed
through DATEVALUE() to return a date.
to Calculate Loan or Investment Maturity Dates
If someone invests in a 6-month CD on the 17th of the month, the maturity date
is on the 17th of another month. This would be a fairly straightforward cal-
culation if no one invested on the 31st of a month.
The maturity rules work such that if you invest on the 31st of a month, and
the CD would be scheduled to mature on the 31st of June, the CD maturity ac-
tually happens on the last day of June, which is June 30.
If a CD is to mature on the 31st, 30th, or 29th day of February, the CD ma-
tures on the last day of February.
EDATE to Calculate Loan or Investment Maturity Dates
The EDATE function returns the serial number that represents the date that
is the indicated number of months before or after a specified date (that is,
start_date). You use EDATE to calculate maturity dates or due dates that
fall on the same day of the month as the date of issue. This function takes
the following arguments:
start_date — This is a date that represents the start date. Dates may
be entered as text strings within quotation marks (for example, "1/30/
2015", "2015/01/30"), as serial numbers (for example, 42034, which
represents January 30, 2015, if you ’ re using the 1900 date system), or
as results of other formulas or functions (for example,
DATEVALUE("1/30/2015")). If the start_dateis not valid, EDATE re-
turns a #NUM! error.
months — This is the number of months before or after start_date. A
positive value for monthsyields a future date; a negative value
yields a past date. If months is not an integer, it is truncated.
Figure 11.46 shows several examples of EDATE. Note that in column B, the
function is a no-brainer. You could easily calculate it by using the DATE
function. The only interesting cases occur on the 29th, 30th, and 31st of the