# Formula for calculating gross up amount

Discussion in 'Microsoft Excel Worksheet Functions' started by Fred, Aug 31, 2004.

1. ### FredGuest

Is there a formula that will calculate an amount that will
represent a specific percentage of a total after adding in
the additional amount, ie after being grossed up?

Fred, Aug 31, 2004

2. ### hgroveGuest

Fred wrote...
>Is there a formula that will calculate an amount that will
>represent a specific percentage of a total after adding in the
>additional amount, ie after being grossed up?

I'm going to guess you mean something along the lines of a net sal
price P plus x% sales tax = total cost, and you want to find the sale
tax as a percentage of the total cost, y%. If so,

y = x / (1 + x)

where x and y have been converted to decimal fractions

--

hgrove, Sep 1, 2004

3. ### FredGuest

Re: Formula for calculating share gross up amount

Thank you, but No.
I mean if an employee is supposed to receive 4% of the
shares of the company and there are say 10,000 shares
outstanding, a formula to calculate the number of shares
he/she should get. 400 is the wrong answer. It should be
closer to 417.
Is there a formula that calculates that? TY

>-----Original Message-----
>Fred wrote...
>>Is there a formula that will calculate an amount that will
>>represent a specific percentage of a total after adding

in the
>>additional amount, ie after being grossed up?

>
>I'm going to guess you mean something along the lines of a

net sale
>price P plus x% sales tax = total cost, and you want to

find the sales
>tax as a percentage of the total cost, y%. If so,
>
>y = x / (1 + x)
>
>where x and y have been converted to decimal fractions.
>
>
>---
>Message posted
>
>.
>

Fred, Sep 1, 2004
4. ### GovindGuest

Re: Formula for calculating share gross up amount

Hi Fred,

Lets say you have the shares outstanding in cell A1 i.e 10,000.
and you have % of shares outstanding to be allotted in cell A2 i.e 4.
(Make sure the number you enter in A2 is just 4 i.e exclude the %)

Then enter this formula in cell A3

=(A1/((100-A2)/100))-A1

This will return a value of 417

Or if you are entering the % of shares to be allotted in % format i.e if
you are entering 4% in cell A2 then use this formula in A3

=(A1/(1-A2))-A1

This will return a value of 417

Regards

Govind

Fred wrote:

> Thank you, but No.
> I mean if an employee is supposed to receive 4% of the
> shares of the company and there are say 10,000 shares
> outstanding, a formula to calculate the number of shares
> he/she should get. 400 is the wrong answer. It should be
> closer to 417.
> Is there a formula that calculates that? TY
>
>
>>-----Original Message-----
>>Fred wrote...
>>
>>>Is there a formula that will calculate an amount that will
>>>represent a specific percentage of a total after adding

>
> in the
>
>>>additional amount, ie after being grossed up?

>>
>>I'm going to guess you mean something along the lines of a

>
> net sale
>
>>price P plus x% sales tax = total cost, and you want to

>
> find the sales
>
>>tax as a percentage of the total cost, y%. If so,
>>
>>y = x / (1 + x)
>>
>>where x and y have been converted to decimal fractions.
>>
>>
>>---
>>Message posted
>>
>>.
>>

Govind, Sep 1, 2004
5. ### hgroveGuest

Fred wrote...
>I mean if an employee is supposed to receive 4% of the shares
>of the company and there are say 10,000 shares outstanding, a
>formula to calculate the number of shares he/she should get.
>400 is the wrong answer. It should be closer to 417.
>Is there a formula that calculates that? TY

4% of 10,000 is 400, whether you want it to be or not.

You need to be clearer in your language. What you seem to mean is tha
this employee would be issued *NEW* shares of stock which should equa
4% of total shares outstanding *AFTER* these new shares had bee
issued.

Let N be the total number of shares after issuing new ones.

(N - 10000) / N = 0.04
N - 10000 = 0.04 N
0.96 N = 10000
N = 10000 / 0.96 = 10417 (rounded up)

So this employees shares would be

10000 * 0.04 / (1 - 0.04) = 417 (rounded up

--

hgrove, Sep 1, 2004
6. ### GuestGuest

"hgrove >" wrote:

> Let N be the total number of shares after issuing new ones.
>
> (N - 10000) / N = 0.04
> N - 10000 = 0.04 N
> 0.96 N = 10000
> N = 10000 / 0.96 = 10417 (rounded up)
>
> So this employees shares would be
>
> 10000 * 0.04 / (1 - 0.04) = 417 (rounded up)

or (very) slightly reduced:

10000 / (1 / 0.04 - 1)

Ryan

Guest, Sep 2, 2004