Template for Marketing Spend Return on Investment - Recurring Revenue

When I first thought about the difficulties of this calculation, I realized it is probably something nearly any recurring revenue business model struggles to figure out accurately. The problem is figuring out the ROI (return on investment) of the money you spend in a given month on sales/marketing as it relates to the future revenue directly related to that spend.



Pricing
The template will be sent to you after purchase.

The first realization is that the ROI will obviously change over time as more revenue comes from the clients/customers that were acquired directly from those initial marketing efforts. The longer you can retain the revenue from that initial spend, the better (higher ROI).

So, the excel template I came up with allows you to work on a monthly cohort basis that will display data for up to 10 years at once. You just have to enter the spend in a given month and then the monthly revenue directly derived from that spend in any month in the future. This is the 'cohort' and each row of marketing spend is its own monthly cohort over time with its own ROI.

Some useful calculations can be easily derived from building this logic beyond just ROI. I was able to calculate the IRR, which will take into account the time it takes to earn said revenue. Additionally, and probably most importantly, I included a calculation to show the number of months it takes to generate enough revenue that it covers the initial investment. So, you can then see if you spend $x dollars, it took you n months to earn those $x dollars back. The lower that is the better.

Conditional formatting was done to tell if ROI/IRR was greater than or less than 0 and I did tiers for the number of months it takes to payback marketing spend (red/yellow/green) based on the number of months it actually takes to do so.

I also included averages for each of the main analysis metrics so that you can see how your average ROI/IRR/months to payback compiles over a longer period of time.

One of the business types that would be able to greatly benefit from this is professional services i.e. Lawyers and Accountant firms. If you spend money to try and acquire customers, it is likely that you will have revenue from those customers that is irregular and over a longer time-frame. This template will make it easier to figure out the kind of return you are getting on your marketing efforts.