Calculating partial dates for costs per month.

[bbeaver8605]

Im trying to calculate the revenue per month with an escalator going into affect on the anniversary each year. I took the size * Rate and then * the escalator after the first year. i cant figure out how to auto-populate the rest of the months with the escalator going into affect with February also being a partial month every year.

Customer	XYZ
Contract Start	02/23/15​
Contract End	05/31/26​
Size	1200​
Rate	$ 178.50
Escalator	2.25%​
Total Revenue
Date	Monthly Revenue
Feb-15​	$ 214,200.00
Mar-15​
Apr-15​
May-15​
Jun-15​
Jul-15​
Aug-15​
Sep-15​
Oct-15​
Nov-15​
Dec-15​
Jan-16​
Feb-16​
Mar-16​	$ 219,019.50
Apr-16​
May-16​
Jun-16​
Jul-16​
Aug-16​
Sep-16​
Oct-16​
Nov-16​
Dec-16​
Jan-17​
Feb-17​
Mar-17​
Apr-17​
May-17​
Jun-17​
Jul-17​
Aug-17​
Sep-17​
Oct-17​
Nov-17​
Dec-17​
Jan-18​
Feb-18​
Mar-18​
Apr-18​
May-18​
Jun-18​
Jul-18​
Aug-18​
Sep-18​
Oct-18​
Nov-18​
Dec-18​
Jan-19​
Feb-19​
Mar-19​
Apr-19​
May-19​
Jun-19​
Jul-19​
Aug-19​
Sep-19​
Oct-19​
Nov-19​
Dec-19​
Jan-20​
Feb-20​
Mar-20​
Apr-20​
May-20​
Jun-20​
Jul-20​
Aug-20​
Sep-20​
Oct-20​
Nov-20​
Dec-20​
Jan-21​
Feb-21​
Mar-21​
Apr-21​
May-21​
Jun-21​
Jul-21​
Aug-21​
Sep-21​
Oct-21​
Nov-21​
Dec-21​
Jan-22​
Feb-22​
Mar-22​
Apr-22​
May-22​
Jun-22​
Jul-22​
Aug-22​
Sep-22​
Oct-22​
Nov-22​
Dec-22​
Jan-23​
Feb-23​
Mar-23​
Apr-23​
May-23​
Jun-23​
Jul-23​
Aug-23​
Sep-23​
Oct-23​
Nov-23​
Dec-23​
Jan-24​
Feb-24​
Mar-24​
Apr-24​
May-24​
Jun-24​
Jul-24​
Aug-24​
Sep-24​
Oct-24​
Nov-24​
Dec-24​
Jan-25​
Feb-25​
Mar-25​
Apr-25​
May-25​
Jun-25​
Jul-25​
Aug-25​
Sep-25​
Oct-25​
Nov-25​
Dec-25​
Jan-26​
Feb-26​
Mar-26​
Apr-26​
May-26​
Jun-26​
Jul-26​
Aug-26​
Sep-26​
Oct-26​
Nov-26​
Dec-26​
Jan-27​
Feb-27​
Mar-27​
Apr-27​
May-27​
Jun-27​
Jul-27​
Aug-27​
Sep-27​
Oct-27​
Nov-27​
Dec-27​
Jan-28​
Feb-28​
Mar-28​
Apr-28​
May-28​
Jun-28​
Jul-28​
Aug-28​
Sep-28​
Oct-28​
Nov-28​
Dec-28​
Jan-29​
Feb-29​
Mar-29​
Apr-29​
May-29​
Jun-29​
Jul-29​
Aug-29​
Sep-29​
Oct-29​
Nov-29​
Dec-29​
Jan-30​
Feb-30​
Mar-30​
Apr-30​
May-30​
Jun-30​
Jul-30​
Aug-30​
Sep-30​
Oct-30​
Nov-30​
Dec-30​
Jan-31​

 

[KRice]

This will give you the monthly amount for every subsequent year...just extend the years and drag the formula down. Details about how to handle partial months will (should?) be covered by the contract. For example, was there an initial prepayment to pay up through the end of the first month, and subsequent bills are due on the 1st? Or is the billing done on the monthly anniversary of the contract start (in which case some additional payment might be due at the end of the contract. More detail would be needed to offer further adjustments to this approach.

Book2
	A	B
1
2	Contract Start	2/23/2015
3	Contract End	5/31/2026
4	Size (volume)	1200
5	Monthly amt	178.50
6	Yearly rate escalation	2.25%
7
8	Year	Monthly Revenue
9	1	214200.00
10	2	219019.50
11	3	223947.44
12	4	228986.26
13	5	234138.45
14	6	239406.56
Sheet2

Cell Formulas
Range	Formula
B9:B14	B9	=($B$4*$B$5)*(1+$B$6)^(A9-1)

 

[bbeaver8605]

That works. Thanks

 

[KRice]

You're welcome...I'm glad it works.