I was only able to get the simple annual interest rate by adjusting your
formula to:

=( ((X+Y)/ -X ) - 1 ) / ((B-A)/365)

--
W


"joeu2004" wrote in message
...
On Jan 24, 2:49 pm, "W" wrote:
Let's say you have an investment that costs $X on Date A
and on Date B it pays $Y and you get back your principal.
How do you calculate the annual rate of return for this
simple investment in Excel?

There are several ways to state the rate of return.

The simple annual return is:

=(X+Y)/X / ((B-A)/365)

Note: For investments in traded securities, it is not uncommon to use
252 instead of 365 -- 252 being the typical number trading days per
year. But that also requires that you determine the number of trading
days between A and B, which is non-trivial.

The compound annual rate of return (aka CAGR, compound annual growth
rate) is any of the following equivalent formulas:

=(X+Y)/X ^ (365/(B-A))

=rate(365/(B-A), 0, -X, X+Y)

=(1 + rate(B-A, 0, -X, X+Y))^365 - 1

(But sometimes RATE requires a "guess" argument in order to return a
valid result.)

There are other tools, like XIRR. But their ease of use (or not)
depends on how your worksheet is set up.