Calculation the Proportional Distribution

**Aston01** · 06-05-2012, 03:09 PM

Trying to calculation the proportional distribution of $5k based on relative difference between the 2 values below. This may be more a math question than Excel, but I was hopeing someone might know of a better way to go about it in Excel.

Value1= .0759
Value2= .0544

SqRt(.0759 / .0544) = 1.181195

The above calculation seems to work, but in a situation like below where the values are reversed the calculation is missing something.

SqRt(.0544/ .0759 ) = 0.846600

The reason I say this is if I utilize the ratio to split $5,000 there is an overage

$5,000 / 2 = $2,500
$2,500 * 1.181195 = $2,952.98

&

$5,000 / 2 = $2,500
$2,500 * 0.846600 = $2,116.50

$2,952.98 + $2,116.50 = $5,069.48 ... $69.48 too much

Honestly I am at the cusp of my understanding of this level of math and was hopeing someone a little more gifted in this area could help me in finding some training wheels for this in Excel

**Richard Buttrey** · 06-05-2012, 03:22 PM

Hi

You need to be clear what you mean by 'proportional distribution'. Neither is it obvious why the square root is involved.

In order to offer a sensible solution we need to understand how you intend to use the result you obtain and the ultimate purpose of the exercise.

In the absence of any better information I would have said

=5000*0.0759/(0.0759+0.0544)
which gives 2912.509593

and
=5000*0.0544/(0.0759+0.0544)
which gives 2087.490407

**Aston01** · 06-05-2012, 04:31 PM

Richard,

I am trying to calculate a delta neutral position. Most of the time the size of these positions are based on the number of shares someone is trying to hedge and the cost is whatever it ends up being to create the hedge. I am trying to calculate it the other way around, assuming I want to buy $5k worth of calls and puts based on the delta and price of each, how many do I need to purchase to stay delta neutral and as close to $5k as possible.

Basic Delta Neutral Calculation
0.5 (call option delta) - 0.5 (put option delta) = 0 Delta

Assuming I was buying a $5k combination of the following:

Call Option
Price - $.59 each
Delta - .0759

Put Option
Price - $.60 each
Delta - .0544

... Minimum unit size is 1 Contract which = 100 Options

My thought process was that if I could determine the relative difference between the 2 delta's I could determine how much of the $5k needed to be allocated to each. Your above calculation achieved just that, but I am missing something it seems. When I put in a second set of values like below things didn't balance as effectively.

2nd Set of Values
Call Option
Price - $.85 each
Delta - .1207

Put Option
Price - $1.30 each
Delta - .1419

I have attached a copy of my basic worksheet

**roki4** · 06-05-2012, 05:49 PM

In the call and put delta calculations, why are you multiplying by 100? If you take out the *100, the calculation is correct for your 2nd set of values.

For your first set of values, in the spreadsheet you entered $59.00 and $60.00 as your call and put prices rather than $0.59 and $0.60? Which one should be correct?