Adjusted Closing Price formula

[AG]

I need to calculate the Adjusted Closing Price for the day which is adjusted
for all applicable dividend distributions. Data is adjusted via dividend
multipliers. Dividend multipliers are calculated based on dividend as a
percentage of price. For example, when a $1.325 cash dividend is distributed
on December 8, 2005 and the December 7th closing price was 29.60, the
pre-dividend data is multiplied by 1-(1.325/24.60) = 0.995.

1 A B C D E
2 Date Closing Price Dividend AdjFactor AdjPrice
3
4
5 12/6/05 29.73 24.27
6 12/7/05 29.6 24.16
7 12/8/05 29.67 1.325 0.955 24.22
8 12/9/05 28.43 24.29
9 12/12/05 28.49 24.34
10 6/2/06 30.4 25.98
11 6/5/06 29.81 0.313 0.990 25.47
12 6/6/06 29.24 25.25
13 12/6/06 32.14 27.75
14 12/7/06 32.03 2.198 0.931 27.66
15 12/8/06 29.82 27.65
16 6/1/07 33.77 31.31
17 6/4/07 33.87 0.387 0.989 31.77
18 6/5/07 33.25 31.18
19 12/7/07 32.46 30.44
20 12/10/07 32.68 2.030 0.938 30.65
21 12/10/2007 32.68 32.68
22 12/11/2007 29.78 29.78
23 12/12/2007 29.93 29.93


So looking at the example you’ll find the formula needed in E6
=C6*E$20*E$17*E$14*E$11*E$7. It is lengthy and not scalable for future dates.

What formula would work?

 

[Fred Smith]

My suggestion would be to store an AdjFactor of 1, rather than zero, for
those prices which don't have dividends. Then you can use
=c6*product(e6:e23)

To solve the problem of future dates, you could store your table in reverse
chronological order, enter 1 in e4, then change your formula to
=c23*product(e$4:e23). This will then copy up for you.

Does that work for you?
Fred

 

[AG]

Yes and thanks for the suggestion regarding the factor.
(Silly me I wasn't aware of the product function)
To handle expandability I would use this formula in the top row of column E
=C5*PRODUCT(E5:E$200) with the range of sufficient in size to handle future
dates.