1. Scenario: I have a worksheet that derives, in cell C12, "Revenue Before Return and Taxes". Based on and from this number, I need three (3) formulae to compute (1) in cell C14, a "Return" which, for these purposes, is equal to "x", (2) in cell C16, a Federal Income Tax provision which is, for these purposes, equal to "x*(.35/(1-.35))", and (3) in cell C18, a State Income Tax provision which is, for these purposes, equal to the sum of (a) the Return, plus ( the Federal Income Tax provision, multiplied by 6%, or "(x+(x*(.35/(1-.35))))*6%". All three of the results in cells C14, C16, and C18 must equal the total in cell C12, "Revenue Before Return and Taxes". What I need are formulas to come up with the different pieces, and they must be based on the "Revenue Before Return and Taxes" figure in cell C12. Any ideas?
Jeff

2. The numbers in C14, C16 and C18 will be different from each other, so how could all three of them equal C12?

If you mean that the SUM of C14, C16 and C18 must equal C12, use the following formula in C14:

=C12/(1+0.35/(1-0.35))/(1+6%)

You can then use the formulas you mentioned for C16 and C18, with C14 instead of x (and beginning with = of course)

3. [quote name='HansV' post='776791' date='25-May-2009 01:11']The numbers in C14, C16 and C18 will be different from each other, so how could all three of them equal C12?

If you mean that the SUM of C14, C16 and C18 must equal C12, use the following formula in C14:

=C12/(1+0.35/(1-0.35))/(1+6%)

You can then use the formulas you mentioned for C16 and C18, with C14 instead of x (and beginning with = of course)[/quote]

Sorry, Hans. I meant to say the SUM of the three cells, C14, C16, and C18 equal C12. How would I phrase C15 and C18?

4. Take the formulas that you mentioned in the first post in this topic, replace x with C14 and stick an = in front of it.

5. [quote name='HansV' post='776796' date='25-May-2009 01:37']Take the formulas that you mentioned in the first post in this topic, replace x with C14 and stick an = in front of it.[/quote]

Thanks Hans.

6. [quote name='HansV' post='776796' date='25-May-2009 01:37']Take the formulas that you mentioned in the first post in this topic, replace x with C14 and stick an = in front of it.[/quote]

Hans (or anyone),
I have been dealt a new twist: For federal and state income tax purposes, one is allowed to deduct interest paid. If I know the amount of interest paid, and it is a figure found at, say, B1 (and it is not part of the computation of "Revenue Before Income and Taxes"), how would this be handled in the equations? My original equations would be restated as follows:

1. Return = x
2. Federal Income Tax Provision = (x - B1) * (.35 / (1 - .35))
and
3. State Income Tax Provision = (x + ((x - B1) * (.35 / (1 - .35)))) * .06

Any ideas?
Jeff

7. In C14:

=(C12/(1+6%)+B1*0.35/(1-0.35))/(1+0.35/(1-0.35))

See attached workbook.

8. [quote name='HansV' post='776990' date='26-May-2009 07:47']In C14:

=(C12/(1+6%)+B1*0.35/(1-0.35))/(1+0.35/(1-0.35))

See attached workbook.[/quote]
Thanks again.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•