How do I calculate quarterly return in Excel?

To annualize a quarterly return, start by going online to your investment account to find the quarterly rate of return (ROR) figure. Then divide that percentage by 100 to convert it into a decimal. Add 1 to your decimal. You probably can do that sum in your head, but grab your calculator for the next step. Use the exponent function to take that sum to the 4th power. Now subtract 1 from what you get, and you’ll have your annual ROR in decimal form. If you want the percentage, just multiply the number by 100. To learn more from our Financial Advisor co-author, like how to annualize your daily returns, keep reading the article!

Did this summary help you?YesNo

Thanks to all authors for creating a page that has been read 163,710 times.

> I am trying to compare the rates of return on an investment account to other
> benchmarks, such as the SP 500 or certain Vanguard and Fidelity mutual
> funds. I am using Excel 2007.

Please forgive the incessant postings, but my first posting (and
errata) probably was not very helpful insofar as it was not
dispositive. Perhaps the following will offer more insight.

For the following, consider a different hypothetical investment. It
is probably similar to yours, but since you did not post the details,
I chose my own "nice" numbers. Consequently, the bottom line is
slightly different.

[Note: I wrote this follow-up before I saw yours, which now does
include some real numbers for you. However, it is still lacking
sufficient detail for me to do an XIRR analysis -- or for me see any
mistakes that you might be making in using XIRR. So I decided to
stick with my own hypothetical example.]

Consider an initial investment of $10,000 on 1/1/2007 followed by
periodic investments of $100 on the first of each subsequent month.
Suppose on 1/1/2008, the investment is worth about $12.939.88 [1].

Normally, industry benchmarks and fund prospectuses do not take
periodic investments into account. (Annuity prospectuses do, at least
during the investment phase.) However, they do take reinvested
dividends into account. So for the sake of argument, assume the $100/
month investment represents reinvested dividends for comparison
purposes.

Breaking this down by quarter, we might see the following, with
investments represented by negative numbers:

1/1/2007 -$10,000
2/1/2007 -$100
3/1/2007 -$100
4/1/2007 $10,129.30 (quarter-end value)

Suppose we compute the quarterly "total return" (distinct from the
simple "return", which does not take reinvested dividends into
account) as follows, based on what I had asserted in my previous
posting (viz. endBalance/startBalance - 1):

(10129.30 / 10000) - 1 = 1.2930% (approximately)

For the subsequent quarters, the "initial investment" is the ending
balance plus the $100 reinvested dividend (and remember to negate
it). Thus, we might see the following:

4/1/2007 -$10,229.30 (quarter-end value $10,129.30 plus $100
investment)
5/1/2007 -$100
6/1/2007 -$100
7/1/2007 $10,997.35 (quarter-end value)
8.5697% (approximate quarterly rate, calculated
as above)

7/1/2007 -$11,097.35
8/1/2007 -$100
9/1/2007 -$100
10/1/2007 $11,633.21
5.7820%

10/1/2007 -$11,733.21
11/1/2007 -$100
12/1/2007 -$100
1/1/2008 $12,938.88
11.2236%

Note that (1+1.2930%)*(1+8.5697%)*(1+5.7820%)*(1+11.2236%) [2] is
29.3888%, which is indeed the same as:

(12938.88 / 10000) - 1

However, those quarterly and annual rates are wrong(!). I know that
because I engineered the quarterly and year-end values based on
foreknowledge of hypothetical actual quarterly rates [1].

As I will show below, if you find that a benchmark or fund annual rate
of return is the product of the quarterly rates of return, I believe
they are not taking reinvested dividends into account. That is, you
might be looking at the simple returns, not the total returns. Or
they are simply using the "endBalance/startBalance" approach, which on
second thought now would surprise me.

An XIRR construction of this scenario would be (in A1:B13):

1/1/2007 -10000
2/1/2007 -100
3/1/2007 -100
4/1/2007 -100
5/1/2007 -100
6/1/2007 -100
7/1/2007 -100
8/1/2007 -100
9/1/2007 -100
10/1/2007 -100
11/1/2007 -100
12/1/2007 -100
1/1/2008 12938.88

The XIRR result is 17.4522% (approximately).

If we apply XIRR to each of the quarters, set up as above for the
"endBalance/startBalance" analysis, we get the following quarterly
return rates [3]: -0.7%, 5.5%, 3.0% and 8.5%. Those are the same as
the hypothetical quarter rates that I used to derive the example.

Thus, the quarterly XIRR does indeed compute the correct quarterly
market rates of appreciation.

However, (1-0.7%)*(1+5.5%)*(1+3.0%)*(1+8.5%)-1 [2] is 17.0762%
(approximately), not 17.4522%. This is also true if I "normalize" the
quarterly results [4], which results in an annual rate of 16.9760%.

I have not given any thought to why this "discrepancy" exists. I
believe there is a mathematical explanation -- perhaps something
similar to the fact that the average of averages of different size
groups is not equal to the average of the whole. (But I would think
that my "normalization" approach would correct for that particular
explanation.)

For the same reason, I cannot say, with impunity, which annual rate is
correct mathematically. I believe it is the first XIRR based on the
complete annual cash flow. But I am relunctant to say that "for
sure".

But my point is: even when the quarterly XIRRs are computed
"correctly" (i.e. they correctly reflect the true rate of return for
the period), the product of the quarterly ratess does not equal the
annual XIRR.

So I conclude that if a benchmark or fund annual rate of return is
equal to the product of the quarterly rates of return (including any
reinvested dividends for the quarter), they "must" be using the
"endBalance/startBalance" approach. But I suspect they would do that
only for simple "returns", not "total returns" (which include
reinvested dividends).

FYI, I have always had trouble validating a funds "total return" rate
by simply taking the product of its stated quarterly "total return"
rates. I suspect now that this explains why. That is, I suspect that
you will find that "total return" rates do indeed take the timing of
reinvested dividends into account, effectively computing the XIRR.

HTH. I apologize for the lengthy "explanation". I had taken some
things for granted myself.

Endnotes:

[1] The quarterly and year-end values were derived by actually
applying the following quarterly appreciation rates to the periodic
investments: -0.7%, 5.5%, 3.0% and 8.5%.

[2] When multiplying rates (1+q1)*(1+q2)*...., the actual computed
values are used, not the approximate values shown here.

[3] The quarterly XIRR is computed as you did, namely (for the first
quarter, for example):

=( 1+xirr(B1:B4, A1:A4) ) ^ ( (A4-A1) / 365 ) - 1

[4] Quarterly XIRR results are normalize as follows (for the first
quarter, for example):

=(1 - 0.7%) ^ ( 365 / 4 / (A4 - A1) )

> The calculated XIRR for the account for one year is 20.11%. Using a
> hypothetical $10,000 beginning balance, the account generates a final
> balance of $12,011.
>

> The POWER and RATE functions reveal that the equivalent quarterly return is
> about 4.69%.
>

> That is: 10,000 x 1.0469 x 1.0469 x 1.0469 x 1.0469 = 12012.15, allowing for
> rounding error. Of course, in the real world, the quarterly returns vary
> from quarter to quarter.
>

> I have separately calculated the individual quarterly XIRRs as:
>
> Q1: -.70
>
> Q2: 5.41
>
> Q3: 3.01
>
> Q4: 8.34
>

> I used this formula for the first quarter, where the dates are in column A
> and the amounts are in column B:
>
> =((1+XIRR(B7:B14,A7:A14,0))^((A14-A7)/365)-1)*100
>

> Here is the problem: 10000 x .9930 x 1.0541 x 1.0301 x 1.0834 = 11681, not
> 12011.
>

> For comparison, here are returns for the Vanguard Wellington mutual fund,
> using Vanguard's own figures:
>
> 2007 annual return: 8.34
>

> 10000 x 1.0834 = 10834 final value
>
> 2007 individual quarterly returns: 1.14, 4.91, 3.01, -.88.
>
> 10000 x 1.0114 x 1.0491 x 1.0301 x .9912 = 10834 final value
>

> The final balance using the annual return for Wellington is the same as the
> final balance using individual quarters.
>

> I see no errors in my data entry, so I must assume that XIRR does not in
> fact provide quarterly results that can be accurately compared to benchmarks
> such as Vanguard Wellington.
>

How are quarter returns calculated?

How do you calculate quarterly dates?

How do I convert my monthly return to quarterly?

How do you calculate Q4 in Excel?