Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula in excel
I am trying to calculate a formula for alimony recapture for tax planning,
that will compute the amounts that must be paid for each of three years without triggering recapture. The facts a Assume total payment over 3 years is $100,000 To calculate: Year 1 payment must not be $7,500 more than year 2 payment, nor $22,500 more than year 3 payment (otherwise, there will be excess payments that will trigger a recapture tax) Any negative results of calculations are considered zero The formula that I have works only when there is enough in the total payment to make a positive payment for each of the three years. Legend: A1 = Cell containing the total payments over 3 years (e.g., $100,000) A2 = Cell containing year 1 payment A3 = Cell containing year 2 payment A4 = Cell containing year 3 payment Formula for year 1 payment: Cell A2 =A1 + 30000/3 Results: $43,333 Formula for year 2 payment: Cell A3 =IF(SUM(A2-7500)<0,0,SUM(A2-7500)) Result: $35,833 Formula for year 3 payment: Cell A4 =IF(SUM(A2-22500)<0,0,SUM(A2-22500)) Result: $20,833 The problem with the formulas for years 2 and 3 is when the total payment drops below a minimum amount to allow for full payments in years 2 and 3; then, the total payments for years 2 and 3 will exceed the remaining amount after subtracting year 1 payment from the total amount For example, if total payments for all 3 years are $35,000, then year 1 payment is $21,667, year 2, $14,167, and year 3 is zero, for a total of $35,833. This exceeds the alloted total payment by $833. What can be added to the formulas for years 2 and 3 to assure that the total payments for each of the 3 years will not exceed the alloted total amount? Thank you -- MZ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula in excel
Try
=MAX(MIN(SUM(A2-7500),$A$1-SUM($A$2:A2)),0) =MAX(MIN(SUM(A3-2250),$A$1-SUM($A$2:A3)),0) --- HTH Bob Phillips "MZ" wrote in message ... I am trying to calculate a formula for alimony recapture for tax planning, that will compute the amounts that must be paid for each of three years without triggering recapture. The facts a Assume total payment over 3 years is $100,000 To calculate: Year 1 payment must not be $7,500 more than year 2 payment, nor $22,500 more than year 3 payment (otherwise, there will be excess payments that will trigger a recapture tax) Any negative results of calculations are considered zero The formula that I have works only when there is enough in the total payment to make a positive payment for each of the three years. Legend: A1 = Cell containing the total payments over 3 years (e.g., $100,000) A2 = Cell containing year 1 payment A3 = Cell containing year 2 payment A4 = Cell containing year 3 payment Formula for year 1 payment: Cell A2 =A1 + 30000/3 Results: $43,333 Formula for year 2 payment: Cell A3 =IF(SUM(A2-7500)<0,0,SUM(A2-7500)) Result: $35,833 Formula for year 3 payment: Cell A4 =IF(SUM(A2-22500)<0,0,SUM(A2-22500)) Result: $20,833 The problem with the formulas for years 2 and 3 is when the total payment drops below a minimum amount to allow for full payments in years 2 and 3; then, the total payments for years 2 and 3 will exceed the remaining amount after subtracting year 1 payment from the total amount For example, if total payments for all 3 years are $35,000, then year 1 payment is $21,667, year 2, $14,167, and year 3 is zero, for a total of $35,833. This exceeds the alloted total payment by $833. What can be added to the formulas for years 2 and 3 to assure that the total payments for each of the 3 years will not exceed the alloted total amount? Thank you -- MZ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula in excel
And of course you can replace SUM(A2-7500) by just A2-7500, because you
haven't given Excel anything to add to A2-7500. Excel help for the SUM function will tell you what it does. -- David Biddulph "Bob Phillips" wrote in message ... Try =MAX(MIN(SUM(A2-7500),$A$1-SUM($A$2:A2)),0) =MAX(MIN(SUM(A3-2250),$A$1-SUM($A$2:A3)),0) --- HTH Bob Phillips "MZ" wrote in message ... I am trying to calculate a formula for alimony recapture for tax planning, that will compute the amounts that must be paid for each of three years without triggering recapture. The facts a Assume total payment over 3 years is $100,000 To calculate: Year 1 payment must not be $7,500 more than year 2 payment, nor $22,500 more than year 3 payment (otherwise, there will be excess payments that will trigger a recapture tax) Any negative results of calculations are considered zero The formula that I have works only when there is enough in the total payment to make a positive payment for each of the three years. Legend: A1 = Cell containing the total payments over 3 years (e.g., $100,000) A2 = Cell containing year 1 payment A3 = Cell containing year 2 payment A4 = Cell containing year 3 payment Formula for year 1 payment: Cell A2 =A1 + 30000/3 Results: $43,333 Formula for year 2 payment: Cell A3 =IF(SUM(A2-7500)<0,0,SUM(A2-7500)) Result: $35,833 Formula for year 3 payment: Cell A4 =IF(SUM(A2-22500)<0,0,SUM(A2-22500)) Result: $20,833 The problem with the formulas for years 2 and 3 is when the total payment drops below a minimum amount to allow for full payments in years 2 and 3; then, the total payments for years 2 and 3 will exceed the remaining amount after subtracting year 1 payment from the total amount For example, if total payments for all 3 years are $35,000, then year 1 payment is $21,667, year 2, $14,167, and year 3 is zero, for a total of $35,833. This exceeds the alloted total payment by $833. What can be added to the formulas for years 2 and 3 to assure that the total payments for each of the 3 years will not exceed the alloted total amount? Thank you -- MZ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) |