Convert linear rate to Compounded

  • Thread starter Thread starter Robert E. Leonard Sr
  • Start date Start date
R

Robert E. Leonard Sr

I am looking for formula to convert Annual stock return to a compounded
annualized return.

i have developed a formula that gives me the estimate annual return on a
stock of 22.0%. I want to convert it to a compounded annual return. I
estimate it to be around 15 or 16%.

Thanks,
Bob Leonard
 
Hi Bob,

Look in HELP for the NOMINAL() and EFFECT() functions

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am looking for formula to convert Annual stock return to a compounded
| annualized return.
|
| i have developed a formula that gives me the estimate annual return on a
| stock of 22.0%. I want to convert it to a compounded annual return. I
| estimate it to be around 15 or 16%.
|
| Thanks,
| Bob Leonard
|
|
 
Thu, 6 Dec 2007 01:55:37 -0500 from Robert E. Leonard Sr
I am looking for formula to convert Annual stock return to a compounded
annualized return.

i have developed a formula that gives me the estimate annual return on a
stock of 22.0%. I want to convert it to a compounded annual return. I
estimate it to be around 15 or 16%.
Click to expand...

Initial value in A1
Final value in A2
Number of years in A3

The growth is A2/A1

The growth per year is (A2/A1)^(1/A3)

The percent increase per year (compound annual return) is
=(A2/A1)^(1/A3)-1
formatted as a percent.
 
I am looking for formula to convert Annual stock return to a compounded
annualized return.

i have developed a formula that gives me the estimate annual return on a
stock of 22.0%. I want to convert it to a compounded annual return. I
estimate it to be around 15 or 16%.
Click to expand...

I don't understand. You seem to be misusing the terminology. A
stock's "annual return" is the same as its "compounded annualized
return". They are different only when the time period is different.

For example, if the stock price grew 22% over 4 years, we might
compute the annual return by either of the following formulas:

=rate(4, 0, -1, 1+22%)

=(1+22%)^(1/4) - 1

Note: You might need to set the Format to Percentage manually.

But if your estimate of 15-16% is close, that would suggest that you
have held the stock for only 16-17 months. That high end can be
estimated by:

=12*nper(15%, 0, -1, 1+22%)

Replace 15% with 16% to estimate the low end.

On the other hand, perhaps you mean that 22% is the "nominal" return
based on, for example, a monthly return of about 1.83% (22%/12).

In that case, the compounded monthly return can be annualized by
either of the following formulas:

=fv(22%/12, 12, 0, -1) - 1

=(1 + 22%/12)^12 - 1

Note: Again, you might need to set the Format to Percentage manually.

But that results in an annualized rate of more than 24%, not 15-16%.
Since somehow you estimated a smaller annualized rate, it would seem
that this is not the case you are asking about.
 
PS....

I don't understand. You seem to be misusing the terminology.
Click to expand...

Or you omitted a critical detail (see below).
For example, if the stock price grew 22% over 4 years, we might
compute the annual return by either of the following formulas:
=rate(4, 0, -1, 1+22%)
=(1+22%)^(1/4) - 1
Click to expand...

Perhaps you mean that the stock price grew by x% over N years, where x
%/N is the (nominal) annual return 22%. If your estimate of 15-16% is
correct, then x% would be 110-131% and N would be 5-6 years. You
would use the formula above; but it is important that you use the
total return of x% (perhaps 110-131%), not 22%.

(When I wrote "grew 22% over 4 years", I meant to say "a total of
22%".)
 
Back
Top