Monthly Return Calc in Excel


Posted by Ryan R. Anderson on December 08, 2000 6:11 AM

I know this one will be super easy for someone....

Here is my data structure:
Column A: has the date for each month(11/30/00, 12/31/00) for many years, and the most current date typd in the next cell in that column.
Column B: the net asset value corresponding to each date.

Here is what I am trying to do: I want a formula in 1 cell that will calc the monthly return:
(Ending Value-Beginning Value)/Beginning Value
And, I want this formula to update automatically each month...

Thanks for your help.
RRA



Posted by Aladin Akyurek on December 08, 2000 8:14 AM

I'm not sure I understand your question properly:

If column A contains data, column B asset values starting in A1 and B1, then the following could be the formula that you want (in C1).

=(B1-INDIRECT(ADDRESS(COUNT(B:B),COLUMN(B1))))/B1

Aladin