Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusted Closing Price formula
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" wrote in message ... 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 youll 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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusted Closing Price formula
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. "Fred Smith" wrote: 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" wrote in message ... 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 youll 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: Need Excel formula to return correct price from price history table | Excel Discussion (Misc queries) | |||
calculate/convert volume price to monthly average price | Excel Worksheet Functions | |||
Can this adjusted GPA formual be accomplised? | Excel Discussion (Misc queries) | |||
create a formula for price * discount* tax =final price | Excel Discussion (Misc queries) | |||
formula for - price times discount times tax = final price | New Users to Excel |