Convert Monthly Cash Flows to Quarterly and Annual Cash Flows (Updated Mar 2022)
Over the past few years, I’ve been working to build an ARGUS DCF alternative in Excel – or otherwise known here as the A.CRE All-in-One (Ai1) Model. As I’ve worked on that model, I’ve been jotting down modeling techniques I use and think would be helpful to share with our readers. Today I’d like to show you two techniques I use to roll up monthly cash flows to quarterly and annual cash flows. I’ve recorded three videos – one long, and two short – that demonstrate the methods. I’ve also posted a copy of the Excel template used in the videos so that you can see the formulas first hand.
You May Also Find Helpful: How to Become Proficient in Real Estate Financial Modeling
How to Convert Monthly Cash Flows to Annual Cash Flows
So what do I mean when I say convert monthly to annual cash flows? Essentially, when I say convert (or rollup) monthly cash flows to annual cash flows I mean simply adding up the monthly cash flows in a given year to equal annual values. So for instance, I might have monthly cash flows such as these – one cash flow for each month:
And I’m tasked with building an annual cash flow statement. I would need to rollup the cash flows from the year, so that the 12 monthly cash flows above are summed and entered into year one below (yellow cell):
I would then continue for each subsequent year until the annual cash flow statement is complete.
This should be an easy enough task, right? Simply use the SUM function to manually sum up the cash flows? But imagine you’re rolling up 15 years of cash flows for 20+ different variables (e.g. gross rent, expense reimbursement, vacancy, various operating expenses, CapEx, etc.), using the SUM function to perform this work would take FOREVER.
So, allow me show you two techniques I use to make this task much more manageable.
Technique #1 – Using the SUMIF Function
As described in the accompanying video, a collection of SUMIF formulas will do the trick. To make this work, on your monthly statement, you will need a year row identifying which year a given cash flow is in. You will then write a SUMIF formula in each of your annual cells summing the cash flows assigned to that year.
=SUMIF(Reference to Row with Years on Monthly Cash Flow Statement, Reference to Year on Annual Statement, Reference to Row of Cash Flows on Monthly Cash Flow Statement)
- Row with Years on Monthly Cash Flow Statement – Column and Row both set as absolute reference
- Year on Annual Statement – Column set as relative reference, Row set as absolute reference
- Row of Cash Flows on Monthly Cash Flow Statement – Column set as absolute reference, Row as relative reference
This process is made easier if you set the appropriate absolute and relative references when writing the first formula on the annual statement. This allows you to write one formula, and then copy it down and across to quickly complete the process.
Technique #2 – Using SUM and OFFSET Function Together
The second technique is less used, but equally as effective and powerful. If you recall from past OFFSET tutorials on this blog, the OFFSET function “returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.” When used together with the SUM function, the SUM and OFFSET function accomplish the same thing as the SUMIF function.
One advantage of the OFFSET function over the SUMIF function is that you do not need an annual row in the monthly cash flow statement to use an OFFSET function in this case. In fact, all you need is an annual row on the annual statement and for the monthly cash flows to be properly ordered in rows on the monthly statement. Then you simply write a formula that looks like this:
=SUM(OFFSET(Reference to Cash Flow in First Month,0,(1 minus Reference to Given Year on Annual Statement) multiplied by 12,1,12))
- Reference to Cash Flow in First Month – Column set as absolute reference, Row as relative reference
- Reference to Given Year on Annual Statement – Column set as relative reference, Row as absolute reference
As is the case with the SUMIF technique, this process is made easier if you set the appropriate absolute and relative references when writing the first formula on the annual statement.
The OFFSET function, as is also the case with the SUMIF function, are fast and effective ways to convert your monthly cash flows to annual cash flows.
Video Tutorial – Rolling Up Monthly to Annual Cash Flows in Excel
30 Second Tutorial – Converting from Monthly to Quarterly Using SUMIF()
We’ve been building a series of quick, 30-second videos covering basic concepts in real estate financial modeling. As part of that series, we recorded a tutorial showing how to convert monthly cash flows to quarterly cash flows. The logic is the same as technique #1 above, using SUMIF to add all cash flows in a given quarter.
To find the quarter for each monthly period, simply use the following formula: =ROUNDUP(Month/3,0). The resulting value will be the quarter for a given month. So for instance, the quarter for month 5 will equal [=ROUNDUP(5/3,0)] or 2.
30 Second Tutorial – Converting from Monthly to Annual Using SUMIF()
In addition to the monthly to quarterly tutorial, we’ve also created a 30-second monthly to annual video tutorial that replicates the lengthier video embedded above.
To find the year for each monthly period, simply use the following formula: =ROUNDUP(Month/12,0). The resulting value will be the year for a given month. So for instance, the year for month 15 will equal [=ROUNDUP(15/12,0)] or 2.
Compatibility
This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365.
Download the Tutorial Source File
To make this source file accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – typical tutorials sell for $25 – $100+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
We regularly update the file (see version notes). Paid contributors to the tutorial receive a new download link via email each time the tutorial is updated.
Frequently Asked Questions about Converting Monthly Cash Flows to Quarterly and Annual Cash Flows
Version Notes
v1.0
- Initial release
