I wanted to know just how much the addition of bourbon to a batch of beer affects the alcohol. I wrote this Excel Spreadsheet to calculate it.
Click here to download the Excel Spreadsheet calculator.
Put your values in the yellow fields. The rest of the sheet is "protected' so you can't mistakenly change it. Just "unprotect" the whole sheet if you want to tweak it!
The "pinkish" columns on the right automatically show how many ounces you entered. That is not used for any math. It is just there for convenience.
Under Batch size in gallons, enter the total amount you are going to keg or bottle. This includes the additional liquor. Enter it as gallons. You can use partial gallons like .5 for half, or .25 for a quarter gallon if you like. Most of us make gallons at a time. That is why it's gallons and not ounces!
Enter Milliliters of liquor in ml's next. Since those bottles are in fractions of a liter, and not quarts anymore, I find it easier to use ml instead of ounces.
Next, enter the ABV of the batch of beer you made. Use your hydrometer and it's scale, or your brewing software. If you all actually decide to use this, I could look up the calculation and have people enter their OG and FG so it will tell ABV automatically. For now, this works for me.
Use Proof of liquor to tell it how strong the bourbon (or whatever you used) is. Anything over 80 proof usually has no "flavor", but who knows, someone may want to just add alcohol without flavor so it is adjustable. Use Proof, not percentage. The next column over tells you the percentage (half of proof) in case you can't do that math in your head. ;)
Finally, enter the serving size in ml. That number can easily be 1. It serves no purpose other than to tell you how much of the drink you pour is the liquor and how much is the beer! It is not important. I put it there because I used it to verify my original math. I liked the extra data it provided, so it remains. It is in ml because the rest of the math is all done in metrics since liters are easier to verify in my head than old English! Again, ounces will be calculated if you need to see it. Hint is provided ... 12 ounces is 355 ml.
The rest of it is just the math for those that like to see all your work on the answer sheet! :O
Total ml of beer is simply the number of ml in the number of gallons you entered at the top.
Ml of beer minus liquor takes the number of ml of liquor entered and subtracts it from total batch volume. This is needed to separate how much of the serving is from the liquor and how much is the beer.
% of liquor in the beer is how much of the total batch is the liquor. This is derived from the number above and is used later in the calculations.
Ml of liquor in a serving is the next step in the chain of separating the liquor from the beer and will lead soon to a percentage. For now, it tells the user a pretty number if they want to know how much of their serving is the original liquor.
Ml of beer in a serving is what remains from a serving after subtracting the amount that is the liquor.
Ml of alcohol from liquor is how much of the original liquor was pure alcohol. Need this to get the actual alcohol versus the total amount of liquor which is 40% alcohol in a 80 proof bourbon.
Ml of alcohol from beer is the amount of the serving that is pure alcohol from the beer that was made. Same as above, it is how much is really alcohol minus the water and other ingredients.
Ml of pure alcohol is the two sources of alcohol added together.
Finally we get to the number this is all about! Now that we know how much pure alcohol is in the serving, and we know the serving size, we can divide the ml of pure alcohol by the serving size to get the percentage of it that is alcohol!
If you just wanted to get a low alcohol beer that missed it's target up to where you wanted it, you could plug in the batch size and alcohol. Then add some vodka at 120 proof or more to it using this to figure out how much it would take to hit a targeted percentage. For instance, if I added 1000 ml of 120 proof vodka to a 3% beer, it would make it 4.5% alcohol.
Let me know if you use this and find it interesting. It was educational to make it.
OK, you smarty’s out there! I have no idea how ABV and ABW plays into this. My math may not be right if the hydrometer method is by volume and the proof is by weight. I don't know if proof is weight or volume. I am sure someone will correct it for me. ;)
