How to Create Annual Cashflows from Monthly Cashflows

 
 
 

Monthly Cashflows to Annual Cashflows

At Top Self Models (TSM) we create monthly cashflow projections for every project to provide a granular level of analysis. Monthly periods allow us to analyze the project on a much deeper level and manipulate various assumptions. Although monthly cashflows are excellent for providing in depth analysis, it may also be more difficult to understand the big picture as there are so many details. In order to understand the model on a higher level one can convert the monthly cashflows into annual cashflows.

Annual cashflows provide a quick snapshot or overview of the project on a yearly basis. By converting monthly to annual cashflows, less space is taken up on a sheet providing a much clearer picture of the project.

There are many different ways to convert monthly cashflows to annual cashflows. At TSM we use the =SUMIFS() formula. The =SUMIFS() formula is as follows:

 

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

 

  • sum_range – the range of cells to be summed

  • criteria_range1 – the range of cells that are to be tested using criteria 1

  • criteria1 – the criteria which defines criteria_range1

  • [criteria_range2, criteria2] – these are optional arguments, but are additional ranges and criteria used to evaluate the sum_range.

For example, lets convert a monthly cashflow to an annual cashflow. The monthly cashflows are below:

In order to convert the revenue from the monthly cashflow in year 1 to an annual year 1 we must write the following formula:

Where the sum_range are the revenues (line 9), the criteria_range are the year numbers (line 4), and criteria1 is year 1 (cell G17). Be sure to use appropriate absolute references where necessary in order to make the process easier. After using the =sumifs() formula for the expenses, the annual cashflows will look as follows:

Conclusion

Although monthly cashflows can provide in depth analysis, annual cashflows are helpful when determining the bigger picture. Annual cashflows are great for a quick snapshot of the project and can be used for investors or other parties in order to make projects easier to understand.

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions on converting monthly cashflows to annual cashflows or if he can help you with your modeling needs.

 
Eric Bergin