# How to calculate revenue equally based on start and end dates?

My company phases revenue based on slightly obscure start/end dates and I need calculate how much revenue falls into the current year, based on these dates.

If a contract starts on or after the 16th of the month, the revenue will start from the following month. If a contract start on or before the 15th of the month, the revenue will start from the current month:

For example:

``````Dec-15  16/11/2015  15/12/2015
Jan-16  16/12/2015  15/01/2016
Feb-16  16/01/2016  15/02/2016
``````

If the value of this contract is £1000, my company phases the revenue equally by months.

Example 1:

``````Contract start= 16/06/2016 means revenue will start in June 2016
Contract end =  23/02/2017 means revenue will end in March 2017
``````

Example 2:

``````Contract start= 16/06/2016 means revenue will start in June 2016
Contract end = 04/02/2017 means revenue will end in Feb 2017
``````

In example 1, this would result in the total revenue being split into 10 months.

In example 2, this would result in the total revenue being split into 9 months.

If need a calculation that splits the total revenue based on this equal revenue phasing (months), and then calculates how much falls into 2016.

• I can't see your formula or what you've tried. As it is, the question is too broad and reads like a "do my work for me" type question :( I'm sure it's not, so if you can edit to show your efforts then we can see where it's going wrong :) – Dave Jul 12 '16 at 10:57
• I'm confused. The examples say "16/06/2016 means revenue will start in June 2016" but the rules say "If a contract starts on or after the 16th of the month, the revenue will start from the following month." Shouldn't revenue start July 2016? – creidhne Jul 12 '16 at 21:20

Dave simplified Mikey's question so it's not so open-ended. Let me restate it. We know when a contract begins and ends. There are rules for deciding when the Revenue Phase starts and ends. We want to find two things:

1. How many months are there in the revenue phase, and
2. How much revenue occurs in 2016.

I'm going to find the revenue that occurs in the same year as the start, whatever year that may be. I've defined names for the following cells. `Contract.value` is defined as 1000.

``````Start  \$C\$3
Stop   \$D\$3
Months \$E\$3
``````

`Start` and `Stop` are computed from the contract begin and end dates. We need the number of months from `Start` to `Stop` so we can find the "equal revenue phasing." It's really easy. Use DATEDIF or check How to calculate the number of months between two dates in Excel for an alternative method. Here's the formula for cell `E3`.

``````=DATEDIF(Start,Stop,"M")+1
``````

The `+1` makes the number of months inclusive of the first and last months. In the example, there are 9 revenue months from July 2016 to March 2017.

Here's how to find the revenue in 2016, the year that the Revenue Phase starts. First, find the number of months in 2016. Cell `C2` computes it:

``````=IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1
``````

Compute the number of months times the revenue for one month in the Revenue Phase. Cell `D2` does it:

``````=C2*Contract.value/Months
``````

Combining `C2` and `D2` into a single formula shows the same result in cell `E2`.

``````=(IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1)*Contract.value/Months
``````

There are some disadvantages to this approach. What if you want to find the revenue in 2017? The formula for 2017 follows the same pattern as `C2`, but it's not the same. Things get messy if the Revenue Phase spans more than two years.

The problem can be generalized by making a simplified amortization table and using PivotTable to summarize it. `Start`, `Stop` and `Months` are all that's needed to make a list of months and revenue. The PivotTable finds the revenue for every year in the Revenue Phase. Row `2` is special.

Here are the formulas for `A2:C2`.

``````=Start
=YEAR(A2)
=Contract.value/Months
``````

Use the formulas for `A3:C3` to `Fill Down` to fill the list. Fill the number of rows to allow for the largest possible months in the Revenue Phase. It's easy to add more.

``````=IF(A2<Stop,DATE(YEAR(A2),MONTH(A2)+1,1),"")
=IF(A3<>"",YEAR(A3),"")
=IF(A3<>"",Contract.value/Months,"")
``````

The "trick" is in `A3`. When the date in column `A` for the previous row is after `Stop`, the cell in column `A` is blank and every following row is blank, so there will be one row for each month in the Revenue Phase -- no more, no less. The trick is useful when you want to make a list, but you don't know the number of rows in advance.

To make the pivot table, select the entire list, including blank rows. By selecting the entire list, the pivot table will still work if even when you change the contract dates. Use the `Year` field for the pivot table row, and `Sum of Revenue` and `Count of Year` for the summary fields.

• Cell A1: Contract Start Date
• Cell B1: Contract End Date
• Cell C1: Month index when contract began:

`=YEAR(A1-15) * 12 + MONTH(A1-15) + 1`

• Cell D1: Month index when contract ended:

`=YEAR(B1-15) * 12 + MONTH(B1-15) + 1`

• Cell E1: Pay per period assuming \$1000 total contract

`=1000/(D1-C1)`

• Cell F1: Number of months in 2016

`=24205 - C1`

• Cell G1: Total 2016 pay

`=F1*E1`

• I believe there are problems with this solution. Example 1 has 9 months and Example 2 has 8 months in the Revenue Period. For Example 1 this solution finds 8 months (`D1-C1`) and for Example 2 it finds 7 months. The 2016 revenue is wrong. More seriously, if a contract begins 16/06/2016 and ends 01/12/2016, this solution says the revenue is 1200. (NB: The question says 10 and 9 months, but we're waiting for clarification. The examples say the period for 16/06/16 starts June 2016, but "on or after the 16th of the month, the revenue will start from the following month," so it should be July.) – creidhne Jul 14 '16 at 1:34