Calculating Annual % Increase that occurs mid month
On Mar 11, 8:36*pm, joeu2004 wrote:
Errata....
Oops! *I was not careful with the case where the firstdate(A5) is
thedateof the initial price increase.
One fix is to make a special case of the 2nd price formula, just as
the 1st price formula is a special case.
In A5, put (same as before):
=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))
In A6, put (B4 changed to $C$1 in last ROUND expression):
=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($C$1*(1+$B$2),2), B5))
And in A7, put the following formula (same as before) and copy down:
=IF(MONTH(A7)=MONTH($B$1),
* * ROUND(B6*(1+$B$2-$B$2*(DAY($B$1)-1)/DAY(EOMONTH($B$1,0))),2),
*IF(MONTH(A7)=1+MONTH($B$1), ROUND(B5*(1+$B$2),2), B6))
Alternatively, you could insert a hidden row above the first price
line (currently row 5) and put the following into (new) B5: *=C1.
That will require changes to all references in the formulas that I
posted previously. *(Not difficult.)
Alternative #2: *take care of the special case(s) in the original
formulas. *But it seems unfortunate to copy that overhead down the
rest of the table.
Post back if you prefer to pursue either alternative (which one?) and
need help.
----- original posting -----
On Mar 11, 5:59*pm, joeu2004 wrote:
Oops! *I was not careful with the case where the firstdate(A5) is
thedateof the initial price increase. *I'll fix that later. *Gotta
run!
----- 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 thedateof the first price change
datein B1, the annual rate (3%) is in B2, the firstdate(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
withDATE(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 thedate5/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 tocalculatethe 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 tocalculatethe 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 implementationdatecould 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.
|