I have two columns: date and value of an investment on that date.
I want to select a date and compute the rate of return on the investment for
the prior year leading up to the selected date. Is there a possible for this?

You want XIRR. Just feed XIRR the dates and the cash flows, and it will
calculate the return for you. Remember, to calculate rate of return, all you
need are the cash flows (money in and money out). Intermediate values of the
investment are immaterial.

If you are trying to calculate the return on an existing investment, you
need to assume you sold it on the last day of the period. IE, enter the
date, and the value of the investment as a negative number.

If you need any more help, post back with an example of your data.

Regards,
Fred.

"vg" wrote in message
...
I have two columns: date and value of an investment on that date.
I want to select a date and compute the rate of return on the investment
for
the prior year leading up to the selected date. Is there a possible for
this?

On Apr 4, 1:31*pm, vg wrote:
I have two columns: date and value of an investment on that date.
I want to select a date and compute the rate of return on the investment for
the prior year leading up to the selected date. *Is there a possible for this?

Anything is possible; but it might be difficult. If I understand you
correctly, the difficulty might be finding the value of the investment
a year before the chosen date. The difficulty depends on the
regularity of the data.

Do you have daily, weekly, monthly, or quarterly data or something
like that? Or do you have values on irregular dates?

Suppose you have quarterly data starting in row 2, with A2 being the
date and B2 the value. Then in C6, the annual percentage growth is B6/
B2 - 1. (Format as Percentage). If you copy that formula down, it
will compute the annual percentage growth for each date.

If you have something different in mind, you might want to explain in
more detail.

In calculating the return of an investment, the regularity of the data is
immaterial. The only thing that matters is cash flow. When you invest $1000
and a year later have $1100, you've made 10%. It doesn't matter what values
the investment had during the year.

Put the dates and the cash flows in two ranges then feed this to XIRR. It
will calculate the return.

Regards,
Fred.

"joeu2004" wrote in message
...
On Apr 4, 1:31 pm, vg wrote:
I have two columns: date and value of an investment on that date.
I want to select a date and compute the rate of return on the investment
for
the prior year leading up to the selected date. Is there a possible for
this?

Anything is possible; but it might be difficult. If I understand you
correctly, the difficulty might be finding the value of the investment
a year before the chosen date. The difficulty depends on the
regularity of the data.

Do you have daily, weekly, monthly, or quarterly data or something
like that? Or do you have values on irregular dates?

Suppose you have quarterly data starting in row 2, with A2 being the
date and B2 the value. Then in C6, the annual percentage growth is B6/
B2 - 1. (Format as Percentage). If you copy that formula down, it
will compute the annual percentage growth for each date.

If you have something different in mind, you might want to explain in
more detail.

On Apr 5, 5:37*am, "Fred Smith" wrote:
In calculating the return of an investment, the regularity of the data is
immaterial. The only thing that matters is cash flow. When you invest
$1000 and a year later have $1100, you've made 10%. It doesn't matter
what values the investment had during the year.

And if you follow my example more closely, you will see that I did not
include them.

The "regularity of the data" does not affect the result. But it might
affect the simplicity of a solution.

Put the dates and the cash flows in two ranges then feed this to XIRR.
It will calculate the return.

And if $1000 is in B2 and $1100 is in B6, B6/B2 - 1 calculates the
annual return. Much simpler. No need to copy data or change signs.

The real point of my first response was: the OP's requirements are
ambiguous. I wanted to present the OP with a choice -- a simpler
solution for the simplest problem. It is up to the OP to decide which
solution applies to his/her situation.

(I would consider XIRR only if there were multiple cash flows, and the
OP wants to take time-value into account. But mutual funds do not
when they compute total return rates.)

Yes, it is possible to compute the annual rate of return in Excel using the data you have. Here are the steps you can follow:

1. First, sort your data by date in ascending order. This will make it easier to select the range of data you need.
2. Next, select the range of data that includes the date and value columns. Make sure to include all the rows up to and including the selected date.
3. Click on the "Insert" tab in the ribbon at the top of the Excel window, then select "Line" from the "Charts" section. This will create a line chart of your data.
4. Right-click on the chart and select "Select Data" from the context menu. In the "Select Data Source" dialog box, click on the "Add" button to add a new series.
5. In the "Edit Series" dialog box, enter a name for the series (e.g. "Returns"), then select the range of cells that contains the returns data. This will be the same number of rows as your original data, but with the first row left blank.
6. Click "OK" to close the "Edit Series" dialog box, then click "OK" again to close the "Select Data Source" dialog box.
7. Right-click on the chart and select "Format Data Series" from the context menu. In the "Format Data Series" pane, select "Line" from the "Fill & Line" section, then choose a color and line style for the returns line.
8. Click "Close" to close the "Format Data Series" pane.
9. Now you can see the returns line on your chart. To calculate the annual rate of return, you can use the following formula:
   
   
   Formula:

   =(1+Returns)^365-1 

   This formula assumes that there are 365 days in a year. If you want to use a different number of days, you can adjust the formula accordingly.
   
10. To use the formula, select a cell next to the selected date, then enter the formula using the cell reference for the returns data. For example, if the returns data is in column C, you would enter the formula "= (1+C2)^365-1" (assuming the selected date is in cell A2).
11. The result will be the annual rate of return for the year leading up to the selected date. You can format the cell as a percentage to make it easier to read.

That's it! You now know how to compute the annual rate of return in Excel using the data you have.