Earnings Calculator - No Math Required :)

ColsCreations
Honored Contributor II

Zazzle's royalty calculator lets you choose either a product price or a royalty rate and will change the other value accordingly, and shows you "royalty you will make". That figure has proven unreliable as it doesn't take into account the deduction if the sale is referred, sometimes it seems to be taking current sales into account, and sometimes it's just plain wrong for no logical reason. 

I got tired of "running the numbers" all the time trying to figure out how a royalty would shake out after fees and discounts when deciding what to set one at. And doing the same after a sale, trying to make sense of the very limited info shown in the royalty report. So a while back I made a spreadsheet to figure it all out for me.

My spreadsheet can't determine the product price if you enter a royalty or the royalty if you enter a product price like Z's calculator does. You need to pull those two numbers from the Z calculator and enter them in the spreadsheet, and then everything else will be broken down for you:

EarningsCalc-01.jpg

Now you can see all your  various earnings scenarios, the amount of fees deducted,  and can even enter a discount % to see what you'd earn when the item is on sale. You can also enter a quantity to see what you'd earn on a bulk sale, handy for say invites or other things were people usually by more than one.

After a sale, you can plug in the subtotal they paid and the royalty % applied from the royalty report to "check the math" and see why the royalty paid might have been less than expected.

EarningsCalc-02.jpg

In this example, $144 x 18% is $25.92, more than the $19.44 shown on the report. So I plugged the $144 and 18% into the spreadsheet and aha - it's a match for a third-party sale after the referrer fee and transaction fee were deducted.

Now, i also know this item has a list price of $15. $15 x 12 is $180. So what discount they they get to arrive at the $144 subtotal paid? This time I entered my royalty, the list price, and quantity purchased, and then guessed the discount was either 15 or 20%. So I entered 20% in the spreadsheet and aha - yep, they had a 20% discount on this order.

EarningsCalc-03.jpg

 

So you can use this in a lot of ways, before and after a sale, with no math required. There are only four fields you can enter numbers in: product price, your royalty %, and an optional discount and/or quantity. All other fields are calculated for you, and are also locked so you can't accidentally change/erase the formulas in them. (You can unlock the sheet though if you want, I set it so no password is required to do so.)

This is what the actual sheet looks like:

EarningsCalc-04.jpg

It's two calculators side by side so you can easily compare two different rates, or a list price vrs a sale price, or a single item vrs in quantity.

My sheet can't account for getting dinged for when customers use secondary content in your design (that's just too convoluted a situation) or the 5% mess when User Options are applied to the pay out.  But other than those two unpredictable sales situations, this is a pretty handy tool for helping decide on a royalty rate and figuring out our earnings after a sale.

You can download it from this link, free, no-strings-attached. (If you find it useful, I wouldn't mind if you wanted to share/promote one of my products in return though. 😉 )

www.colscreations.com/SharedFiles/EarningsCalc.xlsx 

 

Store IconStore IconWebsite IconFacebook IconPinterest IconInstagram IconBenable IconNight Cafe IconDiscord IconBuy Me a CoffeeOut of Stock List
18 REPLIES 18

WHS_Designs
Honored Contributor II

CreativeLeahG
Honored Contributor III

You are a genius, very impressive.

PAZP
Valued Contributor II

THANK YOU SO MUCH for sharing that.

I downloaded it and just plugged in some numbers to test it. ❤️

KittyValentine
Contributor

Awesome work! Thanks for sharing! ☺️

Windy
Honored Contributor II

Bookmarked!

I also do Postcrossing!


ColsCreations
Honored Contributor II

Sheet last updated Aug 30, 2022

Store IconStore IconWebsite IconFacebook IconPinterest IconInstagram IconBenable IconNight Cafe IconDiscord IconBuy Me a CoffeeOut of Stock List

Sheet last updated Aug 1, 2023

I have also created a second calculator, info & link to download here:

Earnings Calculator For Understanding 5% User-Options

 

Store IconStore IconWebsite IconFacebook IconPinterest IconInstagram IconBenable IconNight Cafe IconDiscord IconBuy Me a CoffeeOut of Stock List

ColsCreations
Honored Contributor II

UPDATE March 25, 2025

Due to changes taking effect April 1, 2025 (see Announcement here), I have disabled the download link in my original post.

Store IconStore IconWebsite IconFacebook IconPinterest IconInstagram IconBenable IconNight Cafe IconDiscord IconBuy Me a CoffeeOut of Stock List

ColsCreations
Honored Contributor II

UPDATE March 27, 2025

Because a number of people asked, yes, I do plan on making a new calculator spreadsheet but I won't be able to do that until I know for absolute certain how Z is calculating the referral portion on "self-promoted" sales. So that won't be until some unknown time after April 1st.

Meanwhile, I've created a temp sheet that works for calculating your earnings on everything except that PP2 referral situation, so you can see what your gross & net royalty will be with the fee & new carve-outs, and what the 15% referral commission would be.

The sheet has two tabs on the bottom. "Old vs New" which compares just that, and "4 rates" which is a second sheet with a grid of four calcs, one for each carve-out rate - 35, 40, 45, 50.

TempSheet-Preview2.png

 

TempSheet-Preview1.png

All fields are locked so you can't accidentally mess up a formula. You can only enter your numbers for price & royalty and the optional quantity and discount. As before, you need to enter a percent in Zazzle's calculator and get the product price from there to plug into this sheet.

Here's the link to download it.

https://colscreations.com/SharedFiles/TransFees/NewTempSheet.xlsx

As before, totally free, no strings attached. But if you find it helpful or found the last one useful, you could buy me a cup of coffee.

https://buymeacoffee.com/colscreations/posts

 

Store IconStore IconWebsite IconFacebook IconPinterest IconInstagram IconBenable IconNight Cafe IconDiscord IconBuy Me a CoffeeOut of Stock List

Connie
Honored Contributor

Thank you very much!

Let's meet at the nearest Starbucks! Seriously, could we donate thru PayPal as another option? 

Thank you so much.

Mariholly
Valued Contributor

♥️♥️♥️♥️♥️♥️♥️♥️♥️

J32Design
Contributor III

Thank you for your work on this Col. The calculator will help to find out where I will raise my royalty rates to.

Flobaby
New Contributor III

Very cool of you Col, thank you so much!

ColsCreations
Honored Contributor II

UPDATE April 16, 2024

Two weeks in and I've now published an updated sheet that accurately calculates earnings under the new Ambassador Program (AP) method that went into effect on Zazzle April 1, 2025.

Here are two examples of real-world earnings reports run through the calculator:

Example 1Example 1
Example 2Example 2

The percents I've shown in ( ) in my screenshots are the percent of total order that the net earnings actually equal.
So using Example 2, you can see that the set royalty rate was 28%. $17.15 x 28% = $4.80.
But it's showing they only earned $2.64, 15.4% of the $17.15.
That's because the net of $2.64 shown is after the ERF and MRF fees have been deducted.
Hopefully the new reports being promised will include columns to show the fees so that people aren't pulling their hair out wondering why the royalty shown as earned is so much less than the subtotal shown x the royalty % shown.
If a "self-promoted" sale, you can see that the net referral amount earned + the net royalty earned does equal the advertised goal of 40% of total order. (A little less due to the Excess Royalty Fee deduction.)

This particular example happened to be a coffee mug published under the main Wedding category. Weddings are in the 50% bracket but mugs as product type are in Home & Living, the 40% bracket. So this answered a very important question for me: Which category rate is applied? The actual product type cat, or the one the designer chooses on publishing? This sale shows it goes by actual product type.

This leads one to think, "Great! Glad it wasn't in the 50% cat or would have earned less ...."
But that brings up the other interesting observation - that that's not actually the case. 🤔
Here you can see the same sale with the four different rates applied:

Grid.png

Look at the numbers in the yellow sections.
While total earnings on a Cross-Over link decrease as the cat rate increases, it's the inverse for a Self-Promotion link, total earnings actually increase as the cat rate goes up. That's because the MRF is a percent of your royalty while the referral commission is a percent of the total the customer paid.

Well anyways, here's the link to download the free sheet:
https://colscreations.com/SharedFiles/AP_EarningsCalc.xlsx

* As before, you need to first put your royalty percent into Zazzle's calculator to get the actual list price for the item, and plug that list price into my sheet.

(Also shared here.)

P.S.
Have also disabled the link to the temp sheet I posted above on 3/27

Store IconStore IconWebsite IconFacebook IconPinterest IconInstagram IconBenable IconNight Cafe IconDiscord IconBuy Me a CoffeeOut of Stock List

Thank you yet again for bringing clarity to this complexity!

Thank you so much, Col!