Excel date functions

Excel’s date functions can be helpful in financial modelling.  If you have a monthly or quarterly financial model, you will want to roll months or quarters (= three months) forward.  Of course Excel stores dates as numbers, which the next day one integer longer than the last.  This means that you can’t roll a monthly model forward by adding 30 to the last month, because not all months are 30 days long.

Rolling dates forward in an Excel financial model: date functions.

How to solve the problem?  Excel has some date functions which help you roll months forward in a financial model.  The “Eomonth” function can be particularly helpful.  It can be set up to take the previous month and add one whole month to the last, whether that month is 28, 29, 30 or 31 days long.  Download the Excel date functions spreadsheet and click on cell C4 to see the Eomonth function in action.

What if your financial model requires a date within a month?

The Eomonth can be modified to return a date part way through the month instead of the date at the end of a particular month.  Click on cell C5 in the spreadsheet.  This function is starting with the end of the previous month (31 Dec 2010) and counting forward 15 days to yield 15 Jan 2010 as the formula result.  Cell C6 is starting with 31 Jan 2010 and counting forward 15 days to yield 15 Feb 2010 as the result.

In financial modelling there is always more than one way to solve a problem

Cell C12 shows you a different way to solve the same problem, illustrating the application of other of Excel’s date functions.  In Cells C9:C11 the model start date has been deconstructed into its component date, month and year.  As the model rolls forward, a whole year is added to each date, starting in cell O11.  Starting in cell C12 the date is then built up again using Excel’s “Date” function.  As a result, the model keeps rolling foward one month at a time.  Line 18 in the model shows you how to roll the model forward from the 15th of each month.

Financial modelling course tips: benefits of the Eomonth function

Excel’s Eomonth function provides a neat elegant way of rolling dates in a model forward.  When applied correctly it’s a short and relatively clear function.  It ends up adjusting the model (including leap years) for the exact number of days in each month.  When linked to a model start date that is regularly refreshed, it can be used automatically to adjust a model for revised dates.

A more advanced example: flexing a financial model for valuation dates

Here is a more advanced example where timing flags have been combined with Excel’s Eomonth function to automatically adjust a financial model for valuation dates.  Download the Excel spreadsheet valuation dates here.  You will see Excel’s date functions applied in line 11, with the financial model rolling forward year by year from the start date in cell C11.  It wouldn’t take much work to adjust this function so that the model rolled forward month by month or quarter by quarter.

About this financial modelling course material

You’re looking at a free online course extract covering Excel functions – it’s all arisen from FTA’s regular financial modelling course training.



...join the conversation...

Your email address will not be published. Required fields are marked *