how do I caluclate return on investment, compounded quarterly?

[SAR]

We can calculate the return on investment with no problem. What we want to
do is make the rate compound quarterly, rather than annualy. Is there a way
to do that?

We calculate a $15,000 investment on 1/1/02 worth $25,794 on September 30,
2006, as a 14.95% rate of return. But if we use another program and
compound out quarterly, the return is only about 12%.

 

[Niek Otten]

<We calculate a $15,000 investment on 1/1/02 worth $25,794 on September 30, 2006, as a 14.95% rate of return. >

How did you calculate that?

If I use the RATE function I get:

11.58%

if I use the annual rate/4. Slightly different results if I use the EFFECT and/or NOMINAL functions, but near 12%.

You can easily set up a timetable and calculate the quarterly amount; same result, of course.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| We can calculate the return on investment with no problem. What we want to
| do is make the rate compound quarterly, rather than annualy. Is there a way
| to do that?
|
| We calculate a $15,000 investment on 1/1/02 worth $25,794 on September 30,
| 2006, as a 14.95% rate of return. But if we use another program and
| compound out quarterly, the return is only about 12%.
|
|

 

[joeu2004]

We can calculate the return on investment with no problem. What we want to
do is make the rate compound quarterly, rather than annualy.

Notwithstanding the question about your numbers, the answer to your
question is....

If the annual IRR is "r", the equivalent quarterly compounded rate is
one of the following, which are equivalent:

=(1+r)^(1/4) - 1

=rate(4, 0, -1, 1+r)

This assumes that "r" is either expressed as percentage (e.g. 12%) or
as a fraction (e.g. 0.12).

 

[joeu2004]

PS....

We can calculate the return on investment with no problem. What we want to
do is make the rate compound quarterly, rather than annualy.

[....]
If the annual IRR is "r", the equivalent quarterly compounded rate is
one of the following, which are equivalent:
=(1+r)^(1/4) - 1
=rate(4, 0, -1, 1+r)

That presumes that the annual IRR was computed "correctly" in the first
place. If instead you computed the annual IRR by calculating a
sub-annual rate, then multiplying by the number of periods per year --
which is quite common, sad to say -- you should reverse that to recover
the sub-annual rate, then compound it to compute the "correct" annual
IRR. Of course, if your sub-annual rate was quarterly to begin with,
you can dispense with compounding.

If you are unsure of how the annual IRR was computed, it really does
not matter much if you divide or exponentiate. To give you a
worst-case idea, 12%/365 is 0.03288%, whereas (1+12%)^(1/365)-1 is
0.03105% -- a difference of less than $75 per $10,000 compounded daily
for a year.