EOMONTH Function

How to Use EOMONTH Function in Excel?

The Excel EOMONTH function stands for “End of Month” and is used to determine the end date given a specified time interval.

The EOMONTH function is a built-in feature of Excel with a practical use-case in financial modeling, because the function conveniently automates the process of finding a specific date in either the past or the future.

• Quarterly Model → The Excel EOMONTH function can be used to construct a quarterly projection model, where each sequential column falls on the last date of the month in the next quarter, i.e. three months from the current month.
• Annual Model → The Excel EOMONTH function can also be used to build an annual model, where each column falls on the final date of the month in the next year, i.e. twelve months from the current month.

The “EOMONTH” function is classified as a “Date & Time” function in Excel, since its purpose is to retrieve dates based on the user-input arguments.

Excel EOMONTH Function Formula

The Excel EOMONTH function formula is as follows.

Where:

• “start-date” → The initial date from which the monthly interval starts.
• “months” → The number of months before or after the starting date.

EOMONTH Function Syntax

The “months” argument specifies the number of months into the future or the past to calculate.

• Positive Integer → If a positive integer is entered into the “months” argument, the returned date will be forward, i.e. into the future. Therefore, entering “12” would calculate the final date on the month twelve months later from the starting date.
• Negative Integer → In contrast, a negative integer yields the date on a backward basis, so entering “-12” would result in the final date of the month twelve months ago.

Note that an error message appears if the starting date is not an actual date on the calendar. For example, a hard-coded date entry of February 29 on a non-leap year would not work.

To reduce the potential for any error messages, it is recommended the starting date be an automatic calculation and cell reference, rather than a manual entry.

The returned date values automatically adjust based on the calendar of each year, making the function far more reliable than manual entries.

The ending date is also, at all times, the last day of the month, irrespective of whether the starting date is the final date of the month (e.g. 11/15/2022).

Months Argument: Monthly, Quarterly and Annual Model

The following table describes the “months” argument corresponding to the periodicity of the most common types of financial models, assuming forward-looking projections (i.e. positive integers).

EOMONTH Function Calculator – Excel Template

We’ll now move on to a modeling exercise, which you can access by filling out the form below.

First Name *

Email *

Submit

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Email provided

Your Download is Ready

Submitting...

EOMONTH Function Calculation Example

Suppose we’re forecasting the financials of a public company to create a five-year annual model.

The date of the company’s latest quarterly report was in Q-3 of 2022, which was September 30, 2022.

The first step in preparing our 5-year annual financial model is to project the remaining quarter in the current year (i.e. Q-4 of 2022), before shifting to a fiscal year basis.

The arguments in our formula for the Q-4 2022 date cell – the stub period – are the following:

^{Quarterly Model}

The returned date is 12/30/2022, the final date marking the end of fiscal year 2022.

In the column to the right, we’ll calculate the sum of the quarterly financials to arrive at our FY-2022 projection (and the cell containing the date will reference the cell from Q-4 2022).

From that point onward, we’ll adjust our monthly input to “12” and drag the formula across for the next four years (and have five projected years in total).

^{Annual Model}