M
MZ
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 are:
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
that will compute the amounts that must be paid for each of three years
without triggering recapture.
The facts are:
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