I am trying to take returns for each quarter of a year and calculate the
compounded return for the year. I tried using the XIRR function, but that
isn't returning me a correct value. I think it wants to use the actual value
in the equation, not the returns for each of those periods. Can anyone help?

On Jul 26, 2:42 pm, Biff wrote:
I am trying to take returns for each quarter of a year and calculate the
compounded return for the year. I tried using the XIRR function, but that
isn't returning me a correct value.

What do you mean by "correct value"? That is, what are your data;
what is the "correct value" you expect; and where did the "correct
value" come from?
Finally, what are your parameters to the XIRR() function?

If you provide the specifics, we can provide more specific answers.
But perhaps the following generalizations will be of some use.

First, there are many definitions for (or views on) how to compute
annual return rates. Second, there are several views on how to
annualize a sub-annual rate. From your ealier post, it is clear that
you want to deal with the geometric mean; but the source of the
"correct value" might not have.

Finally, even taking all that into account, I have never been able to
duplicate the annual rates reported in prospectuses and stock quotes
-- although sometimes I come close. One explanation might be: there
are at least two definitions of "returns", namely FMV and "total
return", not to mention different defintions of return rates (simple,
arithmetic mean and geometric mean). Often we do not have access to
the historical "total returns" on a per-period bases.

Okay, let's assume that you have quarter-to-quarter return rates; i.e.
r1 = q2/q1-1, r2 = q3/q2-1, etc, for consecutive quarters q1, q2, q3,
etc.

In some respects, XIRR is the more accurate computation of the
annualized compounded return rate. But it is usually more accurate
that the common method of annualizing a periodic IRR by compounding;
and many people to do not compound the periodic IRR at all (sigh).

By now, you know how to compute the geometric mean of consecutive
quarterly rates. One method: g = GEOMEAN(r1+1, r2+1,...) - 1. The
compounded annualized rate is typically (1+g)^4 - 1, or GEOMEAN(...)^4
- 1. The non-compounded annualize rate is 4*g or 4*(GEOMEAN(...)-1).

The compounded annualized rate computed in the manner above should be
comparable to the XIRR(), though not exact. But the arguments to the
XIRR() function would not be the return rates (r1, r2, etc).

Hope this helps get a leg-up on the problem. Again, if you provide
specific numbers for the questions above, we can better address your
question.

I apologize for not being very clear. Here is my example that you might be
able to help me out.

Q1 return - 3%
Q2 return - 2%
Q3 return - (1.5%)
Q4 return - 1%

Simply adding the returns for each quarter together doesn't provide an
accurate yearly return because it doesn't take into consideration the
quarterly compounding as in this example. Without more data than this, is
there way to calculate the compound rate of return for the year.

"joeu2004" wrote:

On Jul 26, 2:42 pm, Biff wrote:
I am trying to take returns for each quarter of a year and calculate the
compounded return for the year. I tried using the XIRR function, but that
isn't returning me a correct value.

What do you mean by "correct value"? That is, what are your data;
what is the "correct value" you expect; and where did the "correct
value" come from?
Finally, what are your parameters to the XIRR() function?

If you provide the specifics, we can provide more specific answers.
But perhaps the following generalizations will be of some use.

First, there are many definitions for (or views on) how to compute
annual return rates. Second, there are several views on how to
annualize a sub-annual rate. From your ealier post, it is clear that
you want to deal with the geometric mean; but the source of the
"correct value" might not have.

Finally, even taking all that into account, I have never been able to
duplicate the annual rates reported in prospectuses and stock quotes
-- although sometimes I come close. One explanation might be: there
are at least two definitions of "returns", namely FMV and "total
return", not to mention different defintions of return rates (simple,
arithmetic mean and geometric mean). Often we do not have access to
the historical "total returns" on a per-period bases.

Okay, let's assume that you have quarter-to-quarter return rates; i.e.
r1 = q2/q1-1, r2 = q3/q2-1, etc, for consecutive quarters q1, q2, q3,
etc.

In some respects, XIRR is the more accurate computation of the
annualized compounded return rate. But it is usually more accurate
that the common method of annualizing a periodic IRR by compounding;
and many people to do not compound the periodic IRR at all (sigh).

By now, you know how to compute the geometric mean of consecutive
quarterly rates. One method: g = GEOMEAN(r1+1, r2+1,...) - 1. The
compounded annualized rate is typically (1+g)^4 - 1, or GEOMEAN(...)^4
- 1. The non-compounded annualize rate is 4*g or 4*(GEOMEAN(...)-1).

The compounded annualized rate computed in the manner above should be
comparable to the XIRR(), though not exact. But the arguments to the
XIRR() function would not be the return rates (r1, r2, etc).

Hope this helps get a leg-up on the problem. Again, if you provide
specific numbers for the questions above, we can better address your
question.

On Jul 26, 5:24 pm, Biff wrote:
Here is my example that you might be able to help me out.
Q1 return - 3%
Q2 return - 2%
Q3 return - (1.5%)
Q4 return - 1%
Simply adding the returns for each quarter together doesn't provide an
accurate yearly return because it doesn't take into consideration the
quarterly compounding as in this example.

Of course; perish the thought! Who said anything about "simply
adding" returns?

Without more data than this, is
there way to calculate the compound rate of return for the year.

There are several ways. For example, enter the following as an array
formula (commit with ctl-shift-Enter, not just Enter):

=geomean(1+Q1:Q4)^4 - 1

I use that form to demonstrate the general paradigm for annualizing
quarterly data. However, in the case above where you have exactly 4
quarters of data, you could get the same answer with the following
array formula:

=product(1+Q1:Q4) - 1

Note that I am using "Q1" etc as cell references.

FYI, you cannot use XIRR() with only the data above. You could use
the above information to synthesize data that would work with XIRR().
But why bother, when there are much simpler and more direct ways to do
the computation, as demonstrated.

Hope this helps. If not, keep posting unapologetically.

Thank you. This is exactly what I was looking for.

"joeu2004" wrote:

On Jul 26, 5:24 pm, Biff wrote:
Here is my example that you might be able to help me out.
Q1 return - 3%
Q2 return - 2%
Q3 return - (1.5%)
Q4 return - 1%
Simply adding the returns for each quarter together doesn't provide an
accurate yearly return because it doesn't take into consideration the
quarterly compounding as in this example.

Of course; perish the thought! Who said anything about "simply
adding" returns?

Without more data than this, is
there way to calculate the compound rate of return for the year.

There are several ways. For example, enter the following as an array
formula (commit with ctl-shift-Enter, not just Enter):

=geomean(1+Q1:Q4)^4 - 1

I use that form to demonstrate the general paradigm for annualizing
quarterly data. However, in the case above where you have exactly 4
quarters of data, you could get the same answer with the following
array formula:

=product(1+Q1:Q4) - 1

Note that I am using "Q1" etc as cell references.

FYI, you cannot use XIRR() with only the data above. You could use
the above information to synthesize data that would work with XIRR().
But why bother, when there are much simpler and more direct ways to do
the computation, as demonstrated.

Hope this helps. If not, keep posting unapologetically.

Simple. Compute the rate of return for the first quarter, use this as input
to the second quarter, compute the return, etc. Simple math with do.

"Biff" wrote in message
...
I am trying to take returns for each quarter of a year and calculate the
compounded return for the year. I tried using the XIRR function, but that
isn't returning me a correct value. I think it wants to use the actual
value
in the equation, not the returns for each of those periods. Can anyone
help?

This is what I ended up doing, but I thought maybe there was a function that
peformed that. Thanks.


"Tiro" wrote:

Simple. Compute the rate of return for the first quarter, use this as input
to the second quarter, compute the return, etc. Simple math with do.

"Biff" wrote in message
...
I am trying to take returns for each quarter of a year and calculate the
compounded return for the year. I tried using the XIRR function, but that
isn't returning me a correct value. I think it wants to use the actual
value
in the equation, not the returns for each of those periods. Can anyone
help?