Rate of Return Custom Formula

[dav9999]

I am trying to create a custom formula to find the average rate o
return of some of my investments. I have searched through the Exce
help program to find a formula that will work, but havent foun
anything.

Here's what I want to do.

I enter two dates:
Date 1=The date I purchased Stock X
Date 2=Today's date

Then I enter two values:
Value 1=The amount I originally paid for Stock X
Value 2=The current value of Stock X

Then I want to have another cell that will tell me the average annua
rate of return of that stock. I can't find a way for Excel to do this
If I use the INTRATE function, it gives me a rate, but it is no
correct. For example, if I purchase a stock for $100 and two year
later it is worth $121, then that should be a 10% rate of return, bu
Excel says it is a 10.5% rate of return.

Any help or advice would be greatly appreciated

 

[Norman Harker]

Hi dav9999

I'll use common data:
A1:
23-Feb-2004
B1:
=TODAY()
C1:
123.45
D1:
137.21

One way:

Assuming you are entering the purchase price and current value as
positives.

=(1+RATE(B1-A1,0,-C1,D1,0,0))^365-1
Returns: 26.6981499824513%

Another:

Same assumption:

=(1+((D2/C2)^(1/(B2-A2))-1))^365-1
Returns: 26.6981499825019%

And another:

But here the purchase price needs to be entered as a negative:

=XIRR(C33,A3:B3,0)
Returns: 26.6981499023437%
This is an Analysis ToolPak function and Analysis ToolPak needs to be
installed and selected as an Addin (see Help).

The first two formulas calculate the daily effective return and then
calculate the annual effective equivalent.

The last formula does that internally to the function. You'll notice
minor differences in accuracy. My suspicion is that the second one is
the most accurate as it is an explicit solution whereas the first and
third solutions require iteration.

 

[dav9999]

Norman,

That seems to work, thanks a lot. I've been looking for a solution fo
quite a while now

 

[Norman Harker]

Hi dav9999!

Thanks for thanks. You won't have wasted time in your efforts to find
a solution on your own though.

The second solution (by formula) is based on a re-expression of

FV = PV*(1+i)^days
i = (FV/PV)^(1/days)-1

But since n is measured in days and you want annual effective return:

AnnEff = (1+((FV/PV)^(1/days)-1)^12-1

Which in cell formula terms gives:

=(1+((D2/C2)^(1/(B2-A2))-1))^365-1

And in passing you found out the joys of using the RATE and XIRR
functions. Not a bad return for your efforts.

 

[Norman Harker]

Doh!

For:
AnnEff = (1+((FV/PV)^(1/days)-1)^12-1
Read
AnnEff = (1+((FV/PV)^(1/days)-1)^365-1

Too used to doing conversions of monthly effective.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

Hi dav9999!

Thanks for thanks. You won't have wasted time in your efforts to
find a solution on your own though.

The second solution (by formula) is based on a re-expression of

FV = PV*(1+i)^days
i = (FV/PV)^(1/days)-1

But since n is measured in days and you want annual effective
return:

AnnEff = (1+((FV/PV)^(1/days)-1)^12-1

Which in cell formula terms gives:

=(1+((D2/C2)^(1/(B2-A2))-1))^365-1

And in passing you found out the joys of using the RATE and XIRR
functions. Not a bad return for your efforts.