Calculated properties

[SBencheikh]

Hello, 

I currently have the following properties on my hubspot : 

- Amount which is my total contract value

- Date when the contract will start

- Date when the contract will end 

For example if i win a 1 000 000€ 3 year deal that begins the 01/07/2024 then the Total contract value is 1 000 000€, the starting date is 01/07/2024 and the ending date is 01/07/2027. 

I want to know what is the amount that i will be able to recognize in my revenue this year which is 166 666€ and i would like to create a calculated property to have this automatically in hubspot.

Could someone help me create this calculated property because i don't know how to create the formulas and i dont even know if its possible to do so. 

Thanks for your help. 

Regards, 
Sami

[Jnix284 · Accepted solution]

Happy to help @kennedyp, @SBencheikh you can use this as a starting point, I used Hub Helper Harry a chatGPT trained on HubSpot and a great tool for solving questions like this.

Steps to Calculate Recognized Revenue for the Current Year:

1. Define the Inputs (these are your existing properties)

   • Total Contract Value (e.g., €1,000,000)
   • Start Date (e.g., July 1, 2024)
   • End Date (e.g., July 1, 2027)

2. Calculate the Duration of the Contract:

   • Use the difference between the start and end date to calculate the total length of the contract (in days or months). For your example, the contract spans 3 years, or 36 months.

3. Divide the Total Contract Value Over the Duration:

   • Divide the total contract value by the total contract duration to get the monthly or yearly amount. In your example:
     • Annual Revenue=1,000,0003=333,333.33 € annually \text{Annual Revenue} = \frac{1,000,000}{3} = 333,333.33 \, \text{€ annually}Annual Revenue=31,000,000​=333,333.33€ annually
     • Monthly Revenue=1,000,00036=27,777.78 € per month \text{Monthly Revenue} = \frac{1,000,000}{36} = 27,777.78 \, \text{€ per month}Monthly Revenue=361,000,000​=27,777.78€ per month

4. Identify the Portion Recognized for the Current Year:

   • For the year 2024, since the contract starts on July 1st, only half of the annual revenue will be recognized (for 6 months). Therefore, the recognized revenue for 2024 will be:
     • $27,777.78\times 6=166,666.68\text{€ for 2024}$

5. Create the Formula in HubSpot:

   • You can use HubSpot’s calculated property feature to create a formula that performs the following:
     • First, subtract the Start Date from the End Date to get the total duration.
     • Then, divide the Total Contract Value by the duration to get the monthly revenue.
     • Finally, sum the monthly revenue for the current year.

Here is a sample formula you could apply:

(Total Contract Value) / DATEDIFF('End Date', 'Start Date', 'months') * DATEDIFF('31/12/CurrentYear', '01/07/2024', 'months')

• You might need to adjust the formula based on how HubSpot handles date properties and whether you're working in months or days for accuracy.
• You can apply similar logic to calculate revenue for subsequent years based on the contract duration.

I would recommend simplifying your example to create a base formula without all of the variables (like whether it starts at the beginning of the year vs mid-year, etc.) with your defined criteria (days vs months) and then running some tests to validate the formula.

If you are looking for someone to provide you with the exact formula it might be best to pursue paid services from a consultant/freelancer or a partner/agency.

[RSchweighart · Accepted solution]

Hi @SBencheikh,

You may consider leveraging properties that already exist in HubSpot for this.

A suggestion would be to set up your services as a product in HubSpot.

Then, add that product as a line item to a deal.

Use the billing portion to set up your start date, billing frequency, and number of terms.

Upon saving line items to the deal, HubSpot has some values that automatically calculate such as amount, total contract value, and annual contract value.

Then, you can leverage a report to track your recurring revenue.

Thank you for the tag, @kennedyp. Thanks for pointing out Hub Helper Harry, @Jnix284!

All the Best,

Ryan Schweighart

Whole Hart Impact, LLC

whimpact.co

I help businesses with HubSpot and Zapier. 

[Jnix284]

@RSchweighart using products and line items is a great way to manage this, there are a lot of users not taking advantage of those features and there are also situations where it's not possible to do so. It's nice to know there's more than one way to solve the same problem!

[kennedyp]

Hi @SBencheikh! Welcome to the Community-- happy to have you here 😊 

From what I understand, you want to track your multi-year values as their yearly totals, is that correct? 

You can likely create a custom formula property to host that yearly value in a separate property. This Knowledge Base article walks you through that process. That said, do the totals of years vary from deal to deal? I wonder if it would be useful also to have an automatic calculation for the total years of each contract as well. 

To our experts @danmoyle, @RSchweighart, @Jnix284: do any of you have tips or tricks for creating this kind of calculation property? Thanks! 

Best, 
Kennedy

[Jnix284 · Accepted solution]

Happy to help @kennedyp, @SBencheikh you can use this as a starting point, I used Hub Helper Harry a chatGPT trained on HubSpot and a great tool for solving questions like this.

Steps to Calculate Recognized Revenue for the Current Year:

1. Define the Inputs (these are your existing properties)

   • Total Contract Value (e.g., €1,000,000)
   • Start Date (e.g., July 1, 2024)
   • End Date (e.g., July 1, 2027)

2. Calculate the Duration of the Contract:

   • Use the difference between the start and end date to calculate the total length of the contract (in days or months). For your example, the contract spans 3 years, or 36 months.

3. Divide the Total Contract Value Over the Duration:

   • Divide the total contract value by the total contract duration to get the monthly or yearly amount. In your example:
     • Annual Revenue=1,000,0003=333,333.33 € annually \text{Annual Revenue} = \frac{1,000,000}{3} = 333,333.33 \, \text{€ annually}Annual Revenue=31,000,000​=333,333.33€ annually
     • Monthly Revenue=1,000,00036=27,777.78 € per month \text{Monthly Revenue} = \frac{1,000,000}{36} = 27,777.78 \, \text{€ per month}Monthly Revenue=361,000,000​=27,777.78€ per month

4. Identify the Portion Recognized for the Current Year:

   • For the year 2024, since the contract starts on July 1st, only half of the annual revenue will be recognized (for 6 months). Therefore, the recognized revenue for 2024 will be:
     • $27,777.78\times 6=166,666.68\text{€ for 2024}$

5. Create the Formula in HubSpot:

   • You can use HubSpot’s calculated property feature to create a formula that performs the following:
     • First, subtract the Start Date from the End Date to get the total duration.
     • Then, divide the Total Contract Value by the duration to get the monthly revenue.
     • Finally, sum the monthly revenue for the current year.

Here is a sample formula you could apply:

(Total Contract Value) / DATEDIFF('End Date', 'Start Date', 'months') * DATEDIFF('31/12/CurrentYear', '01/07/2024', 'months')

• You might need to adjust the formula based on how HubSpot handles date properties and whether you're working in months or days for accuracy.
• You can apply similar logic to calculate revenue for subsequent years based on the contract duration.

I would recommend simplifying your example to create a base formula without all of the variables (like whether it starts at the beginning of the year vs mid-year, etc.) with your defined criteria (days vs months) and then running some tests to validate the formula.

If you are looking for someone to provide you with the exact formula it might be best to pursue paid services from a consultant/freelancer or a partner/agency.