Betting formula

[starman102]

Hi all,
I'm trying to create a spreadsheet for placing two bets - one at a bookie and one at an exchange which leave equal profit or loss.
Basiclaly - I need to fill a cell with a number which will cause my two profits at the bookies and exchange to be as close to equal as possible.
Here is an example.

Place £10 at Ladbrokes on Man U to win at odds of 2.0 meaning either £10 loss or £10 win
At Betfair I need to place a lay bet of ?? in order to cover the 5% commission and leave me with as nearer profit or loss to the bookies.
i.e if I placed a lay bet of £10 at Betfair if the bookies won I would end £0 profit/loss while if Betfair won I would end up -50p due to the comm, I need a formula which will give the lay figure to ensure both these numbers were as close as possible - in this case a lay of £10.26 would leave me with a -25p at both bookies - but how do I get the spreadsheet to work out this £10.26 figure for me?
Sorry for the long message!!

[shg]

Does this look right?

Please Login or Register  to view this content.

The formula in C3 is =C2 * (1 + B3) / (2 + B3) * (2 + B2) / (1 + B2)

[starman102]

Hi,
The table looks spot on, I'm just a bit confused what you mean by B2 and B3 - are these the odds, cos you've got them as the commission?
Thanks for your help.

[shg]

It's the commissions on the win bet and lay bet respectively. So if there's a $100 bet, it costs $105 if there's a 5% commission. Or for the example in the table, you have to put up 102.44 to get 102.44/(1.05) = 97.56 on the table.

It doesn't consider odds because I don't understand your odds. It seems to me if you arrive with $10 in your pocket and place it at 2:1 odds (and no commission), you walk away with either $30 or $0, not $20 or $0. My table assumes what I would call 1:1 odds.

If you explain how they work, I'll add odds. I assume that they would, in general, not be the same for the Win and Lay bets?

[starman102]

Hi,
Odds at bookies work like this - if they are 5 you get minus 1 times your stake.
i.e. £10 at 6 would equal £10 times 5 = £50.
And as you say normally the lay odds are higher than the back odds so this needs to be put into the equation.
I've got the spreadsheet working with everything apart from the calculation to work out this lay bet to keep profits or losses as equal as possible.
All help is appreciated!

[shg]

So if Man O' War were racing a horse with a wooden leg in a two-horse race, what would the odds be on Man O' War?

[starman102]

Bookies offer odds starting from 1.00.
Odds on horse are anything below 2.00.
So Man O War would be something like 1.05 so if you put £100 you would only win £5 plus your stake back.
Whereas the other horse would be something like odds of 90 which works out as 90 minus 1 = 89 times £100 = £8900 if he wins plus your stake back.

[shg]

Does this look right? It's a sure thing ...

Please Login or Register  to view this content.

The formula in D3 is =D2 * (1 + B2 / (1 + C2) ) / (1 + B3 / (1 + C3) )

[starman102]

Almost there, but if you could do it for the following I reckon we'll be there.

Stake = £10
Bookie back odds 3
Exchange lay odds 3.2
Bookies commission 0%
Exchange commission 5%

What is lay stake to end up with equal profit/loss.

Thanks for all your help.

[shg]

Stake = £10
Bookie back odds 3
Exchange lay odds 3.2
Bookies commission 0%
Exchange commission 5%

What is Stake -- the total amount to be divided between the Win and Lay bets?

On Lay odds -- if you bet $10 at 3.2, you lose your bet (and only your bet) if the horse wins. What do you win if he loses -- the odds are backward on lays, right?

[starman102]

The stake is the amount you put on the selection at the bookie.
While if you placed a lay bet of £10 on a horse you would win £10 if he lost but lose £10 times the odds if he won.

So in this example.

Stake £10 on Man Utd to win at bookies at odds of 3.0 with 0% comm.
Lay Man Utd at odds of 3.2 with 5% comm but what lay amount do we need to give both equal profit and loss?

In this example the lay amount would be £9.52 to give an overall loss of about 94p at both bookies, but how can we get a spreadsheet to work this out?

Here it is written down.

So if Man Utd won you would win £20 from bookies but lose (3.2 times £9.52 at exchange) = £20.94 = overall loss of 94p

But, if Man U loss you would lose £10 at bookies and win £9.52 less 47p commission leaving you with overal loss of 95p

[shg]

I think this is right. If so, give me one more example to check, and then I'll post the workbook.

(The exchange bet is rounded to the nearest penny, so the results are not exactly the same.)

Please Login or Register  to view this content.

[starman102]

That looks like it!!

Right try this one, but we will change commission rate as some exchanges can give you better rates -

Stake £10 on Man U to win at odds of 4.0 with 0% commission.
Lay Man Utd at odds of 4.3 at betting exchange with 3% commission.

So what is lay figure to get equal loss or profit (I've worked it out manually as £9.37).

So, if Man Utd win we get £30 win at bookies and lose £30.92 at exchange - overall 92p loss.
But if Man Utd lose we lose £10 at bookies but win £9.37 less 3% commission (28p) = total win of £9.09 - overall loss of 91p.

[shg]

We have a winner!

Please Login or Register  to view this content.

You may keep the attached ...

[starman102]

You've done it - your a legend!!
Thank you so much for your help.

[shg]

You're welcome. Mark us up as Solved, please.

[starman102]

Hey Shg, don't suppose you could jus clarify which cells are which and the idea behind the lay figure formula? Jus so i know the jist of it, cheers

[shg]

Whaddaya mean, which cells are which? I thought they were pretty clearly labeled.

The formula in the box is my derivation of the formula used in the worksheet to compute the lay bet amount.

[starman102]

The formula doesnt seem to work in my workbook,
have a look and see what forumla you put in the lay figure box.

[shg]

Please Login or Register  to view this content.

[starman102]

Really sorry if Im being thick but I've put that formula in and getting wrong result.
Have a look-

[shg]

You're still entering commissions as whole numbers: 3.00 for 3%. That's 300%.

Format the commission cells as percent, and then enter 3 for 3%

EDIT: How about just using this version of the workbook. You can delete Sheet1.

[starman102]

Thats it! got it! thanks very much - ill mark as solved!

[orgagigio]

Nuovo Foglio di lavoro di Microsoft Excel modificato.xlsx
hello guys I have a question:
I would like to develop an excel spreadsheet where I'm from automatically setting the formula, the amount to bet on the second book, in order to have a fair win on both bookmakers. Keep in mind that the sign I tip 35% of the net profit.
I am attaching the file that I did, but I manually set the value of the sign back.
thank you

[protonLeah]

orgagigio,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[shg]

There's an updated version of the workbook, with both Dutching and Lay Betting, at https://app.box.com/s/5pt3sgepeuoj8q42lp36