EOMONTH Function - returns the last day of the month, which depends on the number of months before or after a particular start date.
What is the EOMONTH function?
The EOMONTH function returns the serial number for the last day of the month, that is, the indicated number of months before or after a selected start date.
If you use the function without pre-formatting the cell in which you apply the formula, you might be surprised why Excel will return a simple serial number. Excel starts counting the days from the first of January of the year 1900 - so if you format the number 1 to a date format, it will return this date, as you can see below.
So before using the EOMONTH function, make sure you format your selected cell to a date format of your choice. To format a cell, you can either right-click the cell and select "Format Cells..." or speed up your modeling skills and use the shortcut CTRL + 1.
How to apply the EOMONTH function in Excel?
The EOMONTH function uses two inputs, of which both are required.
- The first argument, start_date, is a reference start date.
- The second argument, months, requires the months before or after the reference start date. Excel can return both a date before and after the reference start date. A positive value for months will return a date in the future compared to the reference date, while a negative value for months will return a past date compared to the reference date.
Example of how to apply the EOMONTH function using best practices.
When creating a timeline for a financial model, a proper application of dates is key to keeping a model clean, flexible, and robust.
Below you can see how the EOMONTH function is applied within a timeline of a financial model for a renewable energy investment. Typically models for renewable energy assets have a quarterly timeline which is why we want the formula to result in the end of a given quarter, referring to a given start date of the financial quarter.
In the above timeline, the start_date for the EOMONTH function refers to the first of January 2023. So our goal for the function is to result in the last day of March.
Within the function's second argument, we need the number which will result in our goal - the end of the year's first quarter. Applying the number "0" would result in the end date of the month of the same month as the reference date. So we will need to apply the number "2" for the second argument to achieve our goal.
You might be surprised why there is a named range "Months_qtr" within the second argument in the formula above. It is best practice to name certain constants, such as the number of months per quarter, as technical inputs within a financial model. Technical inputs help with the robustness of the financial model - the Name Manager is a handy tool to keep an overview of named ranges and technical inputs.
So in our case, we put in the months per quarter, which will return in the number "3" which is why we need to subtract "1" for the formula to result in the end of March 2023.
You might think this is quite some work to follow all these best practices, so why not simply put the numbers directly in the function? Naming certain ranges will give you much more flexibility if you ever want to change certain inputs. Instead of manually adjusting hundreds of different cells - which is also prone to error - you can simply adjust one technical input cell.
How to properly apply the EOMONTH function in a financial model for renewable energy investments?
Do you want to learn how to model the EOMONTH function within a financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.
Article sources
