How to annualize quarterly data in Excel

The question I have refers to annualised standard deviation.

For example, I have various funds monthly returns data for the period 1980-2019. Some of them report data for e.g. 13, 19, 43, 56 months and so on. Given that the particular fund is not straight 12, 24, 36 months and so on, can I still use the formula = standard deviation (entire time series of a fund 1)*square root of 12

Unless I should do a count of all available monthly returns and write the formula:

=stdev.p(range)*sqrt(counta(range))

i.e. (standard deviation * the square root of the count of monthly returns of a particular fund)

The 2nd question on the back of that is, what if the returns are quarterly, would the square root of 4 suffice (even if I have e.g. 5, 6, 7 and more quarters of data for a particular fund?)

Thanks

Jonmo1

MrExcel MVP

JoinedOct 12, 2006Messages44,061

• #2

Can you be more specific?
Like you have numbers going down from A1, each cell represents a quarter.
In another column I want to SUM each year of quarters in 1 cell going down?

You could do something like

=SUM(OFFSET($A$1:$A$4,(ROWS(A$1:A1)-1)*4,0))

Then you would have results like
B1 = sum(A1:A4)
B2 = sum(A5:A8)
B3 = sum(A9:A12)
etc..

Jonmo1

MrExcel MVP

JoinedOct 12, 2006Messages44,061

• #4

Pretty much exactly as I posted, except you're going Accross, not down..

Try
=SUM(OFFSET($A$2:$D$2,0,(COLUMNS($A1:A1)-1)*4))

Make $A$2:$D$2 reflect the FIRST set of 4 values to add together.

Jonmo1

MrExcel MVP

JoinedOct 12, 2006Messages44,061

• #6

Change $A$2:$D$2 to $A2:$D2

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:

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.

Version Notes

v1.0

• Initial release

How do you annualize quarterly growth?

How do you annualize two quarters of data?

How do I Annualize data in Excel?