[Retire Early]
Download IRACALC Spreadsheet.

IRACALC Spreadsheet


Download Free Software -
User Instructions.


Return to Table of Contents - Home Page

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.

Download Lotus 1-2-3 Version,
(53k zip file)
Release Date: 03/28/97
Download Microsoft Excel Version,
(63k zip file)
Release Date: 03/28/97

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.


User Instructions.

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 Figure 1.

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 year.

You may want to print this page so that you can refer to these instructions while you work with the IRACALC spreadsheet.

Figure 1.


The Retire Early Home Page . Case No. Case 1 Case 2
IRA CALC spreadsheet . Description IRA Only IRA + Life Ins.
Inflation rate 3.5% Initial IRA Value $5,000,000 $5,000,000
Invest. return 10.7% 1997 Max IRA Distr. $160,000 $160,000
Income Tax rate 39.6% 1997 IRA Distr. $160,000 $254,000
IRA Penalty Tax 15.0% 1998 IRA Distr. $160,000 $254,000
Estate Tax rate 55.0% 1999 IRA Distr. $160,000 $254,000
Partnership Distr. (%/year) 5.0% Amount of 1999 Distr. to P'ship N/A N/A
Partnership Estate Tax (30% less) 38.5% After tax value of 1999 Distr. to P'ship N/A N/A
Annual Living Exp. (after tax) $80,000 Death Benefit of Life Ins. Trust $0 $2,000,000
Mandatory IRA Distr. by . Annual Life Ins. Premium $0 $69,000
(Single Life =1, Joint Life =2) 2 . . .
Year 1997 1998 1999 2000 2001
Spouse 1 Age 67 68 69 70 71
Spouse 2 Age 69 70 71 72 73
Single Life Fac. Spouse 1 18.4 17.6 16.8 16.0 15.3
Joint Life Factor 22.4 21.5 20.7 19.8 19.0
Min. Yr. Distr. (%) 4.46% 4.65% 4.83% 5.05% 5.26%
Excess Distrib. $160,000 $165,600 $171,396 $177,395 $183,604
Results: Max amount to heirs Case 1A Case 1A Case 2A Case 2A Case 2A
IRA Only $1,116,401 $1,208,300 $1,308,418 $1,418,797 $1,538,376
IRA + Life Ins. $3,091,696 $3,156,480 $3,226,829 $3,298,173 $3,376,042
IRA + P'ship $1,116,401 $1,208,300 $1,866,969 $1,971,413 $2,083,684
IRA/P'ship/Life Ins. $3,091,696 $3,156,480 $3,785,380 $3,849,721 $3,919,037

Input parameters.

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. (percent)
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.


filename = softwar1.html
Copyright 1998 John P. Greaney, All rights reserved.

Send a message to: The Retire Early Home Page