How To Create A Dutching Calculator In Excel

create-a-dutching-calculator-in-excel-01

Updated April 2021

In this post, I wanted to show you something a bit different. I’m going to show you how to create a Dutching Calculator in Excel.

Becoming a profitable bettor is not easy. Simply placing accumulator bets on football or having a punt on the horses here and there is not the way to riches. Remember – there’s no such thing a poor bookmaker!

The mathematics behind dutching can seem complicated so I’ll show you step by step how it’s done.

You’ll then be able to use the calculator to split your bet stake across a number of selections to guarantee an even profit no matter which one wins! Sounds good, right?

  1. What Is Dutching?
  2. Dutching Mathematics
  3. Creating The Dutching Calculator In Excel
  4. Conclusion
  5. BONUS – Target Profit Calculator

What Is Dutching?

According to Wikipedia, dutching is a betting technique where you ‘share the risk of losing across a number of runners by backing more than one selection in a race or event’.

Simply put, you back multiple selections in 1 event (e.g. a horse race) and split your total stake across those selections to increase your chances of landing a winner.

With careful research, and managing your bankroll using a Dutching strategy, you can help tilt the odds in your favor and finally start beating the bookie.

Dutching Mathematics

The amount that you need to stake on each selection is directly affected by the number of selections you choose and their respective odds.

I’ll show you how to Dutch 3 selections. If you need or want to do more, by the end of this post you’ll be able to expand the calculator to accommodate as many as you need.

The formula for working out the stake per selection is:

Stake Required =(Implied Probability of Selection / SUM(Implied Probability of All Selections)) * [Total Stake]

That might look daunting but let’s first start building our calculator by entering our total stake we want to bet with and the commission we will pay if we’re using a betting exchange. If you’re not using a betting exchange and you’re placing bets with a traditional bookmaker, you can leave that bit out.

Creating The Dutching Calculator In Excel

Adding Stake and Commission

how-to-create-a-dutching-calculator-in-excel-02

My stake is in cell C2 and I have formatted it as ‘Currency’. The commission value I have entered in cell C3, after setting the format to ‘Percentage’

Next I want to add my selections and the odds for each one. I’m using the decimal odds format. If you’re using a different format, you can easily convert them into decimal odds using this fantastic Odds Converter tool from aceodds.com.

Adding Selections and Implied Probability

how-to-create-a-dutching-calculator-in-excel-03

I am making a Dutching calculator for 3 selections. First of all we can add 1 row per selection onto the calculator with a column next to each for the odds. For the purposes of demonstration I have set fictional odds of 3.0, 5.0 and 8.0. My odds cells (C6:C8) are set to the ‘Number’ format and 2 decimal places.

In the adjacent cells, you want to work out the ‘implied probability’ of each selection. This is essentially working out the percentage chance of it being the winning selection according to the odds that have been offered.

It’s a very simple sum.

Implied Probability = 1 / [decimal odds]

Enter this sum into the cell D6 and then copy down to the remaining selections on your calculator. These cells are also formatted as ‘Percentage’.

how-to-create-a-dutching-calculator-in-excel-04

Adding Stake and Profit

The next step is to input the brains of the calculator. In cell E5, add a heading called ‘Stake’ and then drop down to cell E6 and add the following formula.

=(D6/SUM($D$6:$D$8))*$C$2

 

D6 is the implied probability of this selection winning. This value is divided by the sum of all implied probabilities for all selections and then this number is multiplied by your total stake (cell C2).

The result is the exact stake you’ll need to place on this particular selection. Notice the ‘$’ signs in the formula. These are called absolute references and will mean the formula will point to these same cells even when you copy the formula down to your remaining selections.

Copy the formula down to cells E7 and E8.

NOTE: if your calculator has more than 3 selections, adjust the range of summed implied probabilities accordingly (the $D$6:$D$8 part).

Selection Profit

The penultimate column to add to the dutching calculator is the selection profit. In the next column, add a header to cell F5 called ‘Selection Profit’. In F6, enter the profit formula.

=(C6-1)*E6

This is simply multiplying the odds by the stake to return the profit. We have to subtract 1 from the odds as this represents our original stake that is returned to us should that bet win, so it doesn’t count as profit.

Copy the profit formula down to the remaining selections.

You’ll notice that the profits are not equal across all of our selections which is not what we expect when Dutching. This is because we are only working out the profit for that individual selection and not taking into account the losing stakes that we would incur on the other selections.

 

Dutched Profit

To work out the equal profit we’d make if any selection won, we can use a neat array formula to calculate this. Create a final column on the calculator adjacent to the ‘Selection Profit’ column and call it ‘Dutched Profit’. Enter this formula into cell G6:

=F6-SUM(IF(ROW(E6)=ROW($E$6:$E$8),0,$E$6:$E$8))

NOTE: To enter an array formula, you must hold the CRTL + SHIFT key together and then press ENTER on the keyboard. You’ll know that it’s worked if you see curly braces ( { } ) surrounding your formula in the formula bar.

The array formula is taking the selection profit from selection 1 and subtracting all other selection stakes. It is checking the current row number that the formula is in, and NOT summing the selection stake for that row because it does not need to be subtracted from the overall profit.

The absolute references force the formula reference these cells even when the formula is copied down to the rest of the calculator. As a result, it works correctly for each selection row on the calculator.

Adding Net Profit

Finally, if you’re adding betting exchange commission to your calculator, add a final header to cell B10 called ‘Net Profit’ and enter the net profit formula.

=IF(G6>0,G6*(1-C3),G6)

This will calculate your net profit after commission has been removed at the betting exchange. If your selections cannot yield a profit, commission is not subtracted.

You now have a fully functional Dutching Calculator that you can use to split your stake across multiple selections to increase your changes of winning.

Conclusion

In this post I showed you how to create a dutching calculator in Excel. You can use the calculator to quickly divide your betting stake across multiple selections to return an even profit regardless of which one wins.

Using a dutching strategy can help shift the odds of profitable betting in your direction.

Did you follow along and build your own calculator? Let me know in the comments how you got on.

If you want to get up and running quickly, the Dutching Calculator is available to download completely free in my Free Templates section.

BONUS – Target Profit Calculator

The dutching calculator we’ve built in Excel is in what’s known as ‘Fixed Stake’ mode. We have a stake we want to bet with and it is spread across our selections in order to generate an even profit.

But what if we want to make a target profit instead? We can alter the calculator to accomodate this.

Calculating Stakes for Target Profit Dutching

The calculation for working out the required stakes is a little more complicated for a target profit calculator. Replace the formula in E6 with this instead:

=IFERROR((D6/SUM($D$6:$D$8))*$C$4/((1/SUM($D$6:$D$8)-1)),””)

What we’re doing here is multiplying the target profit value by the ratio of the implied probability of the selection to the overall implied probability. We then divide that value by the value of 1 / total implied probabilities minus 1 (the minus 1 is essentially our unit stake).

It’s a complicated formula, but that’s why we’re making a calculator, right? 🙂

Calculating Dutched Profit

The Dutched Profit column requires an update if we want to Dutch selections to a target profit. Copy this formula into cell G6:

=IF(AND(SUM(E6:E8)<0,–ISBLANK(C6)=0),0,IFERROR(F6-SUM(IF(ROW(E6)=ROW($E$6:$E$8),0,$E$6:$E$8)),””))

The nested elements are necessary because the calculator will return 0 if the current selections cannot yield a profit.

NOTE: Remember to enter this formula as an array formula. Hold the CRTL + SHIFT keys together and then press ENTER on the keyboard. You’ll know that it’s worked if you see curly braces ( { } ) surrounding your formula in the formula bar.

 

Summary

You now have the tools to create a fixed stake and target profit dutching calculator in Excel.

Our free download has both versions for you to use and play with so you can happily switch between Fixed Stake and Target Profit modes.

9 thoughts on “How To Create A Dutching Calculator In Excel

  1. Thanks for the calculation, it is really useful for me. I have some idea for using this calculation.

    I have a question below:
    In the content Adding Net Profit, =IF(G10>0,G10*(1-C5),G10) , there is no G10 and C5…could you please revise it? G10 is the total for G6:G8?

    Exchange commission is meaning return rate at football betting bookmaker?

    1. Hi Ryan
      Thanks for reading – I’m glad you’ve found it useful.

      You’re right about the net profit calculation. I have tweaked the spread sheet layout a few times and obviously forgot to update this accordingly – now fixed 🙂

      The formula simply references the ‘Dutched Profit’ from the first row of the calculator and works out the net profit once the betting exchange commission is deducted from the winnings.

      The Exchange Commission is the fee you have to pay on any winning bet when you use a betting exchange. It’s common place amongst most, if not all betting exchanges. Betfair have a good post explaining it all here http://bit.ly/BetfairCommissionExplained.

  2. Hello, thank you so much for putting this together. I have an issue with the last formula, I keep an getting a #error when I copy the “=IF(AND(SUM(E6:E8)<0,–ISBLANK(C6)=0),0,IFERROR(F6-SUM(IF(ROW(E6)=ROW($E$6:$E$8),0,$E$6:$E$8)),””))" formula. Any idea why this could be? I have my sheet set up exacly as you described above.

    Thanks!!

    1. Hi Sebastian

      A couple of people have reported this, so thanks for pointing it out. I think the problem is if you copy/paste the formula from the blog post into Excel, Excel doesn’t recognize the ‘-‘ character properly so throws a #NAME error. If you manually delete the ‘-‘ character before the ISBLANK function and manually enter it yourself, the problem should rectify.

      If you continue to run into problems, the spreadsheet is available to download in the Free Templates section 🙂

  3. I implemented the dutching calculator from part 1 – that works perfectly. When I try the BONUS – Target Profit Calculator, in The G column I get #NAME? I’ve checked and checked again, and the formula is correct + I am holding shift and CTRL when I press enter. Any advice? Thanks!

    Joe.

    1. Hi Joe

      A few people have reported this issue, I’m pretty sure it’s to do with the fact that if you’ve copy/pasted the formula into Excel, it doesn’t recognize the ‘-‘ character properly so throws a #NAME error. If you manually delete the ‘-‘ character before the ISBLANK function and manually enter it yourself, the problem should rectify.

  4. Hello and thank you,
    It was fun to put together, and they work with the tweaks mentioned above ☺
    My only issue is, if i remove one or two runners i get #DIV0! in all of the boxes (E,F,G columns) how do i fix this? I downloaded your template and it doesn’t have this error? So, i have missed something. please help.
    Will

    1. Hi Will
      I’m glad you had fun putting the calculator together. The templated version has a view more controls in place to mitigate issues like this. I didn’t include them in the post as I just wanted to focus on getting a working calculator together.

      However, if you replace cell D6 with: =IF(C6=””,0,1/C6), this should solve your problem. You can copy/paste that formula down for each of the other rows in your calculatort too.

Leave a Reply

Your email address will not be published.

Scroll to top