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.