The IRACALC spreadsheet offers a quick and easy way to
calculate how much money you'll pay in income and estate taxes
on your IRA.
It comes in two versions; Lotus or Microsoft Excel. Choose the one
that's best for you below.
This program assumes that the retiree is in the highest tax bracket
(39.6% Federal). It allows the user to investigate two tax avoidance
strategies: (1) buying life insurance to fund an irrevocable life
insurance trust, and (2) establishing a family limited
partnership to get a 30% reduction in the estate tax applied
to the partnership's assets.
Your author's analysis of both options
revealed that the longer you live, the more likely you're going to lose
money by trying to avoid taxes. If you think you're going to die within
10 to 15 years, maybe it makes sense. As always, especially when
these kind of sums are involved, it's best to check the results with
your own tax and legal advisors.
The IRACALC spreadsheet is shown below (Figure 1.).
The input parameters that the user must define are
in the upper left hand portion of the spreadsheet. The items
requiring input are shown in blue.
Each of input parameters are explained in detail below in
The "results" area of the spreadsheet shows the calculation
results for each fo the four cases. The "Results: Max
amount to heirs" line identifies which of the four cases
has the highest value after income and estate taxes in each
You may want to print this page so that you can refer to these
instructions while you work with the IRACALC spreadsheet.
|The Retire Early Home Page
|IRA CALC spreadsheet
||IRA + Life Ins.
||Initial IRA Value
||1997 Max IRA Distr.
|Income Tax rate
||1997 IRA Distr.
|IRA Penalty Tax
||1998 IRA Distr.
|Estate Tax rate
||1999 IRA Distr.
|Partnership Distr. (%/year)
||Amount of 1999 Distr. to P'ship
|Partnership Estate Tax (30% less)
||After tax value of 1999 Distr. to P'ship
|Annual Living Exp. (after tax)
||Death Benefit of Life Ins. Trust
|Mandatory IRA Distr. by
||Annual Life Ins. Premium
|(Single Life =1, Joint Life =2)
|Spouse 1 Age
|Spouse 2 Age
|Single Life Fac. Spouse 1
|Joint Life Factor
|Min. Yr. Distr. (%)
|Results: Max amount to heirs
|IRA + Life Ins.
|IRA + P'ship
The user must input the following parameters starting in
column B of the spreadsheet with the Inflation rate.
The values in the cells requiring user input appear in blue
on the spreadsheet.
Inflation Rate How much you expect your
living expenses to increase annually. (percent)
Invest. return Enter the percent return on investment
you expect for your retirement accounts (typically, 10% for stocks,
7% on long-term bonds, 4% on short-term savings.)
Income Tax rate The highest Federal rate is currently
39.6%. You may want to add your state or local income tax
rate to this. (percent)
IRA Penalty Tax Currently, this is 15%.
Estate Tax rate The maximum Federal Estate tax rate is
55%. You may want to add any state or local taxes to this.
Partnership Distr. (%/year) Tax laws will require you
to distribute most of the income generated by the family limited
partnership. This will most likely be a minimum of 5%. (percent)
Partnership Estate Tax (30% less) The program calculates
the estate tax applied to partnership assets as a 30% discount
to the Estate Tax rate input above. (percent)
Annual Living Exp. (after tax) The amount of money you
will withdraw (after tax) from your investment accounts in
the current year to cover living expenses
Mandatory IRA Distribution by (Single Life=1,
Joint Life=2) Choose 1 or 2. If you're married using the
Joint Life expectancy table will reduce the size of
your mandatory distribtion.
Spouse 1 Age Enter age of first spouse.
Spouse 2 Age Enter age of second spouse.
Single Life Fac. Spouse 1 See Page 65 of
IRS Publication 590
Individual Retirement Arrangements.
Joint Life Factor See Page 66 of IRS
Publication 590 Individual Retirement Arrangements.
Run some scenarios.
The IRACALC spreadsheet allows you to input different
parameters and see the results immediately. Use your
imagination. Investigate the effect of a 1% increase in the
inflation rate or a 1% decrease in your investment return.
It will make you think.