Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Annual % Increase that occurs mid month
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Annual % Increase that occurs mid month
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Annual % Increase that occurs mid month
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Annual % Increase that occurs mid month
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Annual % Increase that occurs mid month
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Annual % Increase that occurs mid month
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 ----- 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Annual % Increase that occurs mid month
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 ----- On Mar 11, 5:59*pm, joeu2004 wrote: 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 ----- 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Annual % Increase that occurs mid month
On Mar 12, 10:10 am, AJ Master wrote:
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 ----- On Mar 12, 10:10 am, AJ Master wrote: 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. 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. 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
annual budget - by month help needed | Excel Worksheet Functions | |||
Calculating Annual Growth Rate of Investment | Excel Discussion (Misc queries) | |||
How can I set month/quarter/annual date intervals | Charts and Charting in Excel | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions |