In the past, financial model time periods were generally quarterly or semi-annual. There has been a gradual change and, now that suitable technology is in the hands of essentially all users, we recommend monthly time periods for most projects that we are involved in. This means that all the time-based calculations within the model are on a monthly timeline, and outputs are monthly with (typically) quarterly and annual summaries.
This article discusses the previous convention, why it has largely changed, and some specifics.
The previous convention
Model time periods were generally quarterly or semi-annual, and sometimes annual. The periods would often align with debt service periodicity – for example, if debt service occurs at the end of each calendar quarter, then the model periods would be calendar quarters. If monthly time periods were used, it was generally limited to the first few years. This was driven by the following factors, which are less relevant these days:
All versions of Excel prior to 2007 had a limit of 256 columns per sheet. That could accommodate around 60 years of quarterly time periods, which was more than enough for most financial models. With monthly time periods it could only accommodate around 20 years, which would generally not have been long enough as assets commonly have design lives of 25 to 30 years. While we hear that there are people working in serious roles still using Excel 2003 somewhere in the world, none of them have impacted our work in a long time. If you are one of the unfortunate minority, perhaps try telling your IT department that Microsoft has not provided security support for Excel 2003 since October 2014 and for that matter Excel 2010 stopped receiving security support in October 2020.
Models with shorter time periods (e.g. months instead of quarters) have more formulae, so they are bigger files and they take longer to calculate, solve, open and save.
Computers, including the stock of relatively low-spec laptops that are gradually passed on to a variety of model users, have continued to become more powerful in recent years. The vast majority of models with monthly time resolutions can now calculate and solve in a reasonable time, provided reasonably efficient calculations are used. Calculation and processing time is still a consideration, particularly for models that are very large and consider a portfolio of assets, or a variety of funding structures, or alternative technical solutions – but it has now reached the point where we commonly recommend and use monthly models even in extreme cases.
Network drives and related file sharing technology has also improved, such that file size is generally not an issue when sharing models internally within an organisation. Email size limitations and ease of sharing models with external parties is still a consideration that occasionally comes into play. Zipping models or using the XLSB file format can help reduce file size, we have a tool that often significantly reduces the size of financial models, and there are some more labour-intensive tricks for further reducing file size. We have used combinations of these methods to successfully send models by email in every challenging situation in recent years, and most customers have a file sharing platform that can avoid the issue entirely if need be, but this remains as something to be mindful of.
There is a widespread level of familiarity for models with longer time periods – for example project finance models with monthly construction calculations and quarterly operations calculations have been a staple for decades. With this familiarity comes an affinity for it but, aside from the considerations discussed above, it would be hard to argue that longer time periods are better when taking all use-cases into account.
Monthly benefits
Building models with monthly time periods helps ensure they interact neatly with other tools used within an organisation, and that they will continue to be useful as analysis and reporting requirements change. Performing group-level analysis can be a challenge if asset models are semi-annual based on debt service dates and the timings differs between assets, whereas the same task is simple when the models are monthly.
Monthly models have advantages when developing new projects, even when you have a clear vision of project timelines and initially expect there is no benefit. Arguably the single most common characteristic of development projects is that the projects themselves change. Monthly time periods are often very helpful when projects change.
Maybe the project gets split into phases in a way that is awkward with longer time periods or a distinction between construction and operations.
An investor could make a last-minute request to see how things look if you replace the semi-annual initial bank debt with a quarterly refinance facility – or perhaps refinancing after 10 months of operations specifically to fit in with a broader portfolio consideration.
The project timeline might change such that contractual revenue / charge rate increase dates no longer align with debt service periods.
A new sponsor may join in development of the project and have requirements that expand the dates being considered.
While each potential situation is specific and unlikely to arise on any given project, there are a lot of things that can come up as projects evolve. Changing the periodicity of a model is a significant change that takes time, especially if a model audit process is already underway – this can be problematic when working to a bid submission deadline. Monthly models prevent this issue.
Monthly models are simpler than models with multiple periodicities. This point is not obvious to people who are accustomed to the old way of doing things and not tried building or working with all aspects of a model that is monthly throughout, but when it comes to practice there is no doubt about it.
Specifics
These days we typically recommend the following:
Project finance operational models: monthly throughout.
Project development models: monthly throughout from the point that serious/detailed modelling work commences. In the early stages of project development, for example when the uncertainty on construction costs is ±50% and the funding structure is similarly unknown, you might use a high-level model with annual time periods – and there should generally be a discrete move from the high-level annual model to the more detailed monthly model rather than a string of gradual changes. The old paradigm for project finance development models (monthly calculations during construction and quarterly or semi-annual during operations) will become a thing of the past.
Portfolio models: monthly throughout. Sometimes - rarely - this is impractical due to file size and Excel becoming slow. We often see portfolio models that unnecessarily compromise on periodicity, which usually creates work during update processes and detracts from outputs. Portfolio models are particularly sensitive to computational efficiency – using a better model structure and optimised formulae will often remove the need for compromise.
Corporate and ad-hoc business models: it depends, but usually monthly.
Summary
These days, we generally recommend monthly time periods for most projects that we work on. Other parties often view it as a useful tip and would have done the same themselves, if not for habit. Consider switching to monthly time periods for models that your team develops and uses in the future.