PS....

On Mar 11, 5:36 pm, I wrote:
But if the price changes on the open of business, as I
would expect, the weighted average would be 12*6/31 +
12*1.03*25/31.

Assuming both modifications are correct, try the following

If you truly intend price changes to occur after close of business,
simply change DAY($B$1)-1 to DAY($B$1) in both formulas.


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

On Mar 11, 5:36*pm, joeu2004 wrote:
On Mar 11, 2:35 pm, AJ Master wrote:

Then price increases for the product would occur on
05/07/2010, 5/07/2011, 5/07/2012, etc. In this case
the price for May 2010 would be =12*7/30 + 12*1.03*23/30.
I'm trying to figure out a formula that accurately
calculates the price for each month and also calculates
the weighted average price when it finds the anniversary
month.

Okay. *But I see one or two mistakes in your example, depending on
interpretation.

First, May has 31 days, not 30. *So, at a minimum, I would expect the
weighted average to be 12*7/31 + 12*1.03*24/31. *Agreed?

Second, does the price change on the open or the close of business on
May 7 in your example?

My first modified formula, based on yours, assumes the close of
business. *But if the price changes on the open of business, as I
would expect, the weighted average would be 12*6/31 + 12*1.03*25/31.

Assuming both modifications are correct, try the following (tested!).

I added C1: *the initial price (12.00). *(You will probably want to
put it elsewhere.) *I am assuming the date of the first price change
date in B1, the annual rate (3%) is in B2, the first date (4/1/2009 in
your original table) is in A5, and the price change occurs on the open
of business.

Then in A5, put:

=IF(MONTH(A5)<MONTH($B$1), $C$1,
* * ROUND($C$1*(1+$B$2-$B$2*(DAY($B$1)-1)/DAY(EOMONTH($B$1,0))),2))

And in A6, put the following formula and copy down:

=IF(MONTH(A6)=MONTH($B$1),
* * ROUND(B5*(1+$B$2-$B$2*(DAY($B$1)-1)/DAY(EOMONTH($B$1,0))),2),
*IF(MONTH(A6)=1+MONTH($B$1), ROUND(B4*(1+$B$2),2), B5))

If you get a #NAME? error, look at the EOMONTH Help page to see how to
install the Analysis ToolPak. *Alternatively, replace EOMONTH usage
with DATE(YEAR($B$1),1+MONTH($B$1),0).

Note: Since MONTH($B$1), DAY($B$1)-1 and EOMONTH($B$1,0) are
invariant, you could compute them in helper cells (which could be
hidden, if you wish) and simply refer to those cells.

HTH.

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

On Mar 11, 2:35 pm, AJ Master wrote:



On Mar 9, 6:08 pm, joeu2004 wrote:
How about the following (untested).

Assuming the annual rate (3%) is in B2 and the date 5/1/2009 is in A6,
the put this formula into B6 and copy down:

=if(month(A6)=3,round(B5*(1+$B$2/2),2),
*if(month(A6)=4,round(B5*(1+$B$2),2),B5)

That says: *for March, make the price increase half the annual rate,
and for April, make the price increase the full annual rate, both
based on the price in February. *For all other months, simply copy the
price of the previous month.

Note: *You might want to eliminate the ROUND function if you prefer to
propagate round-off errors. *But that would mean that sometimes, the
price that results from rounding due to Excel formatting will not
match paper-and-pencil computation exactly (probably off by just a
cent one way or the other). *I prefer a WYSIWYG solution.

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

On Mar 9, 4:26 pm, AdmiralAJ wrote:

I have a model where I'm trying to calculate the accurate price for a
product every month. *Unfortunately, I have a product where the price
increases by 3% every year in the middle of a month (for example:
March). *I show the prices by month and March would have a blended
price but I can't come up with a formula to calculate the correct rate
for March? *Any thoughts on how to write this...I am at a loss for a
simple solution. *Below is the way the data should appear if the
formula is correct.

Price implemented: * * * * * * *3/16/2009
Annual Price Increase: * * * * *3%

Date * * * * * * *Price
4/1/2009 * * * *$12.00
5/1/2009 * * * *$12.00
6/1/2009 * * * *$12.00
7/1/2009 * * * *$12.00
8/1/2009 * * * *$12.00
9/1/2009 * * * *$12.00
10/1/2009 * * * $12.00
11/1/2009 * * * $12.00
12/1/2009 * * * $12.00
1/1/2010 * * * *$12.00
2/1/2010 * * * *$12.00
3/1/2010 * * * *$12.18
4/1/2010 * * * *$12.36
5/1/2010 * * * *$12.36
6/1/2010 * * * *$12.36
7/1/2010 * * * *$12.36
8/1/2010 * * * *$12.36
9/1/2010 * * * *$12.36
10/1/2010 * * * $12.36
11/1/2010 * * * $12.36
12/1/2010 * * * $12.36
1/1/2011 * * * *$12.36
2/1/2011 * * * *$12.36
3/1/2011 * * * *$12.55
4/1/2011 * * * *$12.73

Thanks in advance for any help....AJ

I probably was not to clear with this problem. *I am creating a
template where price implementation date could be any day of the month
depending on the product. *In the example above I used 3/16/2009, but
in reality it could have been 05/07/2009. *Then price increases for
the product would occur on 05/07/2010, 5/07/2011, 5/07/2012, etc. In
this case the price for May 2010 would be =12*7/30 + 12*1.03*23/30.
I'm trying to figure out a formula that accurately calculates the
price for each month and also calculates the weighted average price
when it finds the anniversary month. this work, without writing the
vba code and dealing with the macro-enabled file type in '07.

Thanks again for trying to help out.