Calculating Annual Return From One Time Payment

[W]

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?

Assume that I already calculated the one time payment as a present value,
based on the value at the start date of the investment.

 

[joeu2004]

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.

 

[Fred Smith]

Use the Rate function, as in:

=Rate((DateB-DateA)/365,0,-X,Y+X)

Regards,
Fred.

 

[W]

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

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

--
W

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.

 

[joeu2004]

I was only able to get the simple annual interest rate
by adjusting your formula to:
=( ((X+Y)/ -X ) - 1 ) / ((B-A)/365)

First, sorry, there were a number of mistakes and unnecessary
complexities in my formulas. I will provide corrected formulas below.

But your -X concerns me. That suggests to me that your initial
investment is recorded as a negative number. Is that right?

Nothing wrong with that. But my formulas assumed that all values are
recorded as positive numbers.

If the initial investment is a negative number and the net return is a
positive number, the correct formulas are:

Simple Return:
=Y/(-X) / ((B-A)/365)

Compound Return (aka CAGR):
=((-X+Y)/(-X)) ^ (365/(B-A)) - 1

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

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

If the initial investment and net return are both positive numbers:

Simple Return:
=Y/X / ((B-A)/365)

Compound Return (aka CAGR):
=((X+Y)/X) ^ (365/(B-A)) - 1

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

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

HTH.


----- original posting -----