Calculating Revenue between Dates

[Kapil35]

Hello All

We have signed some contracts each of which have their own values, start and end dates. I need to be able to automatically calculate the revenue in respect of these dates.

What formulae is available to automatically calculate the below:

Many thanks

[#2]

@Kapil35 

On row 3, you have the contract start on 15th October 2023 and end on 30th June 2024.

You list the first amount in Oct-23, but if that is correct, the duration should be 9 months:

Oct, Nov, Dec. Jan. Feb, Mar, Apr, May, Jun

And the amount should be 200,000/9.

Or do I miss something?

[#3]

@Hans Vogelaar yes you are correct. Although the reason why the table finishes at March 2024 is because the company operates on a fiscal year, April to March.

[#4]

@Kapil35 

Select E2.

Enter the formula

=IF(AND($B2<=EOMONTH(E$1,0),$C2>=E$1),$A2/$D2,"")

Fill to the right to P2, then down.

[#5]

@Hans Vogelaar thank you so much this has worked a treat!

I do have another request though...

Where a contract has a value of £100,000 for a duration of 12 months and starts on 15/10/2023, I should only recognise 16 days of revenue i.e. (16/31)*(£100,000/12) = £4,301. Please advise, thanks!

[#6]

@Kapil35 

But then the sum of the amounts would not be equal to the contract value anymore. Does that matter?

[#7]

Ideally I would like the sum of the amounts to agree to the contract value. However, please show me the solution you have in mind.

[#8 · Accepted solution]

@Kapil35 

See the attached workbook. The amount per month depends on the actual number of days that fall within that month.

[#9]

Thank you Hans, this is much appreciated!