Calculating Revenue as a Function of Close Date rather than Contract Duration

[HurriaSA]

I am solving a rather new kind of query for one of my clients:

Their requirement:

They want to calculate the revenue starting from the close date of the deal, rather than the actual contract start - contract end dates.

E.g:

Contract Duration = 6 Months

Start Date: 1st January

End Date: 30th June

TCV= 600,000

Close Date: 15th January

Actual Monthly Revenue = TCV / No. of Months

                                            600,000 / 6 = 100,000

However, they also want to calculate the revenue starting from the close date.

Since in the e.g. above the Close Date = 15th January

January Revenue is from 15th - 30th, that is half of 100,000 (50,000). Then, for next months would be 100,000. 

Therefore for Q1, the quarterly revenue would be 1/2 of Jan + Feb + March = 250,000.

But for Q2, it would be 300,000. 

Currently I am able to calculate:

1. Monthly Revenue = TCV / Contract Duration in Months

2. Revenue Earning Days = Days from Close till the Contract End Date

3. Revenue for 1 Revenue Earning Day = TCV / Revenue Earning Days

However, I am stuck at calculating the Quarterly Revenue. Now I want a dynamic calculation that takes in account the Revenue Earning Days with in a Month or Quarter then calculate the revenue. 

Again, like in January Revenue Earning Days are only 15, so it will take in account 15 days from Jan, but whole of Feb & March.

Please let me know if anyone can help.

Thanks,

Hurria 

[PamCotton]

Hey @HurriaSA, thank you for posting in our Community!

You can calculate quarterly revenue by using daily revenue and multiplying it by the number of eligible days each month. January includes only days after the close date, while full months use the standard calculation. 

This can be done in HubSpot with custom properties or externally in a spreadsheet. 

To our top experts, @TomM2 and @danmoyle do you have any other recommendation for @HurriaSA matter?

Thank you,

Pam

[danmoyle]

Thanks for the tag @PamCotton. And hi & welcome to the community @HurriaSA! Looking this over and thinking through ut, I don't have any advice other than the spreadhseet idea. Honestly, I did some research and then tried to create something I found in Perplexity, but it didn't make sense to my mind. Hopefully another member has done this and has insights.