Calculating Annual % Increase that occurs mid month

[AdmiralAJ]

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

 

[joeu2004]

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 -----

 

[AJ Master]

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 -----

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.

 

[joeu2004]

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 -----

 

[joeu2004]

PS....

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 -----

 

[joeu2004]

Oops! I was not careful with the case where the first date (A5) is
the date of the initial price increase. I'll fix that later. Gotta
run!


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

 

[joeu2004]

Errata....

Oops! I was not careful with the case where the first date (A5) is
the date of 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 -----

 

[AJ Master]

Errata....


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 -----

Thanks much! That worked great. As a note yes the price change
occurs on the open of the business day. Also, we assume all months
have 30 days and that there are 360 days in a year.

Thanks again for the assist!!

 

[joeu2004]

Thanks much! That worked great.

You're welcome. The Thousand Monkey Theorem suggests that if I post
enough corrections, eventually I will stumble onto something that is
correct . Here are some more errata, for what they're worth.

First, where I said to put formulas into A5, A6 and A7, obviously I
meant B5, B6 and B7. I'm sure you figured that out.

Second, where I used EOMONTH($B$1,0), that should be EOMONTH(A5,0),
EOMONTH(A6,0) and EOMONTH(A7,0) respectively. This is necessary if
the price change is in Feb.

Finally, while the weighted average that I calculated is
mathematically sound, it is incorrect in the real world. In the real
world, the average price in the price-change month is (in B7):

ROUND(((DAY($B$1)-1)*B6 + (DAY(EOMONTH(A7,0))-DAY($B$1)+1)*ROUND(B6*(1+
$B$2),2))/DAY(EOMONTH(A7,0)),2)

Unfortunately, that does not lend itself to the mathematical
simplification that I implemented in the previous formulas. On the
other hand, I do not see any difference in various empirical trials
(doesn't mean there are not examples where a difference is evident).
So I wouldn't bother with it. After all, the weighted average is not
a real price anyway.

we assume all months have 30 days and that there are
360 days in a year.

I know. I was subtlely trying to get you to do the "right" thing
instead. If you replace DAY(EOMONTH(A7,0)) with 30, there can be a
not-insignificant difference -- although it is only between -0.1% and
+0.2%, so the visibility of the difference depends on the magnitude of
the price. Try 1234567.

However, again, since the weighted average is not a real price, this
might be much ado about nothing.


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