Directions for using the Financial Plan Tools.

If you're still shopping, the best evaluation results are obtained by looking at the TVM demo, while following along as you read the directions. When you're using the program, things go better if you print these directions.

The "demo" is the actual spreadsheet, password protected, without the formulas so it's much smaller. You won't be able to do anything but look at it. You'll receive a working non-protected file after you pay for it.

If you try to print the demo from Internet Explorer, or you just click Print in Excel, it won't work well. Sorry, but to preserve content, the demos are password protected, and you'll probably have to use Excel's Print... options. Tips for working and printing in Excel are here, here, and here.

When you're using the program, things go better if you print these directions.

If you have Excel 2007, then first save the workbook as an xlsx workbook.

You switch between the sheets by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing *Page Up or Down (pressing the control key and either the Page Up or Page Down key on your keyboard at the same time).

Basic input concept for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets. This will populate the presentation areas, where you look at the results. Then you fix mistakes, repeat, format, print. If a cell is not colored green, then it's not an input cell, so you won't be able to do anything with it, and you may end up damaging the spreadsheet.

Before getting started, save an unused copy of all of the files in a separate folder, so you'll always have the original unaltered files somewhere. Then if you do something like delete a formula, you can easily fix it by copying it from these original files. Then save files you've worked on using a different file name (and into a different folder). If you don't like a file being read-only, go to Save As, and then go to Tools, General Options, uncheck the Read Only box, then click Save. More tips for saving and organizing files are here.

Programs usually come without sample input data, but be sure to delete all of the sample/client input (everything in green-shaded cells) before inputting your data.

You should make CD backups of all of your work on a monthly basis, and store the CD in a safe location, but not where your computer is. More PC tips are here.

If you're seeing this: ####, then either increase your Zoom magnification number (View, Zoom), or make the column width wider. If you see this: #REF! or #DIV/0! after completing your input, please respond to get a new program immediately. This means data was lost either via e-mail, unzipping, or while making a CD.

If a red Error! dialog box comes up saying your input must be confined to a range of values, and your input was within that range, then the problem is that the input cell doesn't accept pennies. Round the value to the nearest dollar and try again.

If you're an investing consumer (not a professional financial planner working with clients), then when you read "client, prospect, or they" just think "you." You would be both the advisor and the client (or spouse).

Read about financial planning software support here.

Read about financial planning software integration (sharing data between modules) here.

Read how to present sample reports to prospects and clients.

How to send financial plans to clients via e-mail, without sending the whole program is explained on the Excel help page, here.

You should make CD backups of all of your work on a monthly basis, and store the CD in a safe location, but not where your computer is. It's critical to back up your data, but not programs. Don't forget to backup your MS Outlook Express e-mail folders too. Do a search for *.dbx files to find them.

How to send financial plans to clients via e-mail, without sending the whole program is explained on the Excel help page, here.

Basic Time Value of Money Definitions

TVM = Time value of money - the basic concepts of what these financial calculators are all about. PV = Present Value (beginning investment values, or how much something is worth today). PMT = Payments (constant contributions or withdrawals, usually added or subtracted to the scenario monthly). I = Compound interest rate (interest rate or growth rate that is applied at each N compounding period). N = Number of compounding periods (how many times the calculations are done that compound money at the given interest rate, usually in months or years. E.g., If you have $100 and you compound, or add, 10% annual interest, then you'd have $110 as a future value over this one compounding period. If N=2, then it would compound twice, and your ending value would be $121, which is $110 plus 10%). FV = Future value (usually the result, or the amount of money accumulated over N compounding periods). P = Principal, or how much money is being lent or borrowed. ARM = Adjustable Rate Mortgage. APR = Annual Percentage Rate.

TVM Tools Directions

Once you've unzipped the file TVM Calculators.zip, open the file TVM Calculators.xls with MS Excel. Directions on how to do this are in the original delivery e-mail.

The first six sheets are the free financial calculators. Most everything has self-explanatory directions on them. In general, input data into all of the green-shaded input fields in each calculator box. The results then display in the grey-shaded cells.

If you don't understand something, that means more detailed directions need to be made, so please e-mail.

TVM Calculators Sheet

The first two free financial calculators, #1A & 1B are identical. This is so you can use two different sets of input and compare. The thing to keep in mind as you input data into all of these financial calculators, is that it works based on compounding periods (just like a real tangible hand-held financial calculator with buttons). This means that if you want it to compound monthly, ALL input must be made on a monthly basis. So when you're inputting annual inflation data, please follow the instructions on cell E7. If you input monthly data everywhere else, and then input 3% inflation, you really just input 36% annual inflation, and this will yield strange results.

The results of financial calculators #1A & B are also shown on the charts below them. Charts are titled to show which calculator box they belong to.

Gross Wage Calculator #11: This calculator box has two sections. When you input data into cell W10, the results display in the three cells below. Nothing is affected in cells Y11 - Y13. The same goes for inputting data into cell Y10.

Financial calculators #4A & 4B are identical so you can use two different sets of input and see the differences.

Financial calculator #5 is just a future value calculator, but it allows you to input ages instead of just the numbers of years.

Financial calculator #6 estimates the present value of someone's earnings. Input their earning parameters, and the present value of it all is displayed. That's how much all of their future earnings are worth if you had all the money lump sum today. This is helpful in determine life insurance needs, and is a very scaled-down version of the same concepts used in the real life insurance needs software.

The Life Expectancy calculator: The results will display somewhere in cells. AW18 to AZ21. This is how many years on average the person will live based on how old they are now. It uses the IRS Unisex mortality tables, meaning it displays the same results regardless of gender.

Estimated Capital Needed Vs. Weekly Income Need Calculator: This answers the basic question, "How much money do I need if I want to have $X amount of after-tax spendable money every week, have it keep up with inflation, and not run out for 50 years.

For example, if you wanted to spend $1,000 per week, think you can get an average of 10% rate of return, your average tax rate is 25%, with 3.5% inflation, you'd need $1,000,000 of money, which will run out in 2,600 weeks (50 years). So the rule of thumb is that you'll need a million dollars of retirement nest egg to safely obtain $1,000 per week in inflated spendable money, and have some left over when you pass away.

Net Present Value Calculator (columns BL & BM): When you input a series of cash flows, the result in cell BM4 shows the value of the investment today, assuming you experienced the future cash flows that you input, compounded at the inputted rate of return.

The first cash flow in cell BM6 should be large and negative (just like if you invested money writing a check from your checking account. The negative is money being subtracted from your balance).

The last cash flow should be large and positive, just as if you received a lump sum back from a maturing CD, and it was deposited into your bank account.

You can leave cells blank in between cash flows and they will be considered as having no money coming into or out of the investment during this period.

The interest input into cell BM5 is per compounding period - so if your cash flows are monthly, then the interest rate should be monthly too. The text, "Net Present Value of Discounted Cash Flows:" is supposed to be one sentence, but it looks like it's two due to formatting limitations.

Free financial calculator #8: This tells you the amount of annual payments needed to solve the equation based on your input.

Free financial calculator #9: This estimates the amount of lump sum needed to retire today.

Input how much of percentage of the current income input into cell BZ10 you want to have during retirement. If you currently make $100,000 per year, and you want $75,000 during retirement, input 75%. Compare the results of cells BX13 and BZ13. This shows how powerful the effects of inflation is. The answer you're looking for is cell BZ13.

Final Expense Calculator #12: This just adds up all of the usual costs of someone passing away. It's valuable because people are shocked to find out what a decent send off will cost.

IRR Calculators Sheet

Most everything is self-explanatory. This is for calculating the IRR (internal rate of return) on a series of up to 360 cash flows. In other words, you input a series of unequal cash flows, and the program will tell you what the overall period, and annual, rate of returns are.

Just ensure that dates input into column B have the same amount of time between them. If anything strange appears in cells F8 - F11, then Excel is confused because it's a unusually large or small result. If so, input a guess rate of return into cell F19.

Input the date of the first cash flow into cell B10. The dates are only for your reference, so it doesn't matter what format you use.

Input the remaining cash flows into column C. The first cash flow is usually very big and negative. The program may not work if it's not. Negative cash flows mean money coming out of your checking account, and positive means money going into your checking account. The final cash flow should be very large and positive.

Every cash flow period has to have a number input into it. If nothing happened during that cash flow period, then input 0.

Cell F8 shows the IRR compounded at the cash flow period. If you're inputting years, then this will be the annual cash flow. If you're inputting months, which is the most common, then cell F11 will multiply cell F8 by 12 and display the annual IRR (compounded monthly). So if you're inputting monthly data, the IRR shown in cell F8 will be the monthly IRR, and the number in F11 will be the answer you're looking for - the annual IRR. If you're inputting annual cash flow data, then the number shown in cell F8 is the annualized IRR (and you can ignore F11).

If F8 or F11 display an error, then input a "guess IRR rate" into cell F19 until it works. If it still doesn't work, then you probably didn't input a large enough negative cash in the early years, or a large enough positive cash flow in the final years.

Cost of Raising a Child Calculator

This is to estimate the total estimated costs of raising a child.

Totals for each expense category are in rows 29, and combined totals are in column AD.

Present value numbers mean how much the total would cost today if you had the money and invested it at the interest rate you input into cell D31.

Input the current year into cell B4. Input estimated expenses into all of the appropriate expense columns. Account for annual inflation by using standard Excel formulas. For example using demo data, cell D5 is $6,250. Cell D6 was cell D5 increased at a 5% annual inflation rate. You do this by inputting this formal into cell D6: =D5*1.05 Then you drag cell D6 down to D8 and they will all grow the previous years' number by 5%.

Column Z and AB are the amounts of ADDITIONAL insurance you'll need to maintain with the advent of this child over what you would have maintained if there was no child.

The total amount of money spent raising the child over the years is displayed in cell AD29. The present value, how much these cash flows are worth if you had all of this money today and could invest it at the rate input into cell D31, is shown in cell AD30.

So when someone asks you how much do you think it will cost today to raise a child, you can use cell AD30 to give them the answer. If the spouse says lets' have another kid, you can say, "And where are we going to get cell AD29's amount of money"?

Rent vs. Buy Home Calculator

This is for calculating what the bottom line would be between the scenarios of renting vs. buying a home.

Homeowners like to say that "you're just throwing your money away by renting" and renters like to say how much money is being wasted from property taxes, maintenance, and the fact that most of the tax benefits are lost more and more each year as interest decreases, and because of the annual increase in the standard deduction (which for some single people, is sometimes more than the interest payments, even in the early years of the mortgage).

This calculator will resolve all of these battles better than anything else ever created, because it's the only one that will correctly account for all of the cash flows, and then compare the long-term differences, assuming one invests the difference into a side investment account. As you can imagine, this part rarely occurs in the Real World.

Input the current year, or year you want to start the illustration, into cell B4.

Input the default renting inflation rate into cell. D4. This is how much one expects rent to increase annually. Both the amount of rent, and the inflation rate, can be manually overridden in columns E & F in any year.

Input the amount of annual rent into cell G4. Input the amount of annual utilities into cells I4 - L4. Input the expenses of moving into column M.

Input the standard tax deduction into cell Q4. This is the amount one gets to deduct on their taxes, even if one doesn't itemize deductions. This amount goes up annually with CPI inflation, so input the amount of estimate CPI inflation into cell O4. This is not the amount for personal exemption(s).

Input the home's initial market value into cell AA1, and the rate of annual growth into cell Y4. You can manually override the home's market value, which will matter upon assumed sale, into column AZ.

Input mortgage parameters into cells CA1, AE1, and Z4. You can manually override annual mortgage payments into column AA. Input the amount of down payment into cell AK4.

Input utility costs into cells AG4 - AJ4. Input annual maintenance into cell AM4. You can manually override annual maintenance using column AL.

Usually renting will end up costing much less money on an annual basis, plus the initial down payment is a huge initial cash flow. Renters don't have to pay this, so these amounts are assumed to be invested into a side investment account. Input the annual growth rate of this side account into cell AX4. All money resulting from having more annual cash flow, either from renting or owning, is accounted for either in column AY or BD. As time goes on, the annual increases in rent will overwhelm owning a home, especially if it's paid for.

The home's market value is then offset from the side investment accounts.

The bottom line is the running accumulation of surplus cash flows, plus their growth. When these two are compared, the total net difference is displayed in column BG. Usually renting will end up with a lot more money in the investment side account, so most all of the numbers in column BG will be positive. Positive amounts are how much more money one would have if they rented instead of purchased. And this does not account for the sales expenses, or commissions on a sale.

For calculating the numbers on rental properties, in much more detail, please see the rental real estate software.

Interest Rate Calculators Sheet

Just input deposits into column F and then different rates of return into rows 4, and the differences in the amount of money accumulated given the different growth rates will display.

Column Q allows you to change the interest rate on a year-by-year basis on the growth of money on column P.

Column S is a simple interest calculator column - meaning there is no compounding (interest earned on the previous year's interest).

This is for calculating the long-term differences in account growth assuming four different compound interest rates (and one simple interest rate).

Input the current year into cell D5.

Input the annual deposits into the account into column F. These amounts are used for all five interest rate scenarios equally.

Input the interest rates into cells J4, L4, N4, and P4. Amounts in these columns will be compounded annually by these amount in every year.

Column P and Q are different because this is where you can manually change the interest rate in column P in any year. In other words, if you don't input anything into column Q, column P grows at the rate set in cell P4. But every time you input a different rate into column Q, the numbers in column P grow by this amount instead.

You can use negative rates in any input cell.

The rate input into cell S4 grows at a simple, not compound interest rate, so its growth will be much less, because you don't get interest on interest.

All five scenarios are shown in the graphs to the right.

Compound Interest Rate Converters

This is for calculating the difference in interest rates given various compounding periods.

Input the base rate into cell C6, and the results are displayed in column C, which also explain what the program is doing.

Mutual Fund Calculator Sheet

This is the same Non-Tax-Qualified Mutual Fund A-Share sheet carved out of the of the Investment Comparator.

Input the amount of money that is currently in the investment into cell A11. This will be reduced by whatever initial front-end load and/or sales commissions are paid. This percentage amount is input into cell A27. This amount is deducted from the initial balance in cell G4. To account for any differences between this automatic reduction and the Real World, you can use the Annual Contribution Manual Override in column M.

Input the amount of annual contributions into cell A9. Input the age when they start into cell A10 and the age they stop into cell A11. Then input the percentage amount of annual growth into cell A12. Front-end loads and initial commissions will be deducted from them automatically, if any. You can manually override these amounts in any year by using column M.

Input the age withdrawals begin into cell A14 and the age they end in cell A15. Input the monthly amounts into cell A16. Input the percentage amount of annual increases into cell A17. You can manually override the withdrawal amount in any year in column AW.

There are two sets of tax rates. One for the period of time before withdrawals occur (accumulation phase) and one starting the year withdrawals begin (distribution phase). This is because withdrawals usually start to occur in the years of retirement, and most people are in lower tax brackets then, and they roll their lousy 401(k) accounts into self-directed IRAs. You can manually override tax rates in any year using columns AM or AN.

Input the annual rate of return into cell A26. You can manually override this in any year using column R.

Input the percentage amount of money subtracted from all contributions (front-end loads and/or sales charges/commissions) into cell A27.

Input the annual percentage amount of mutual fund management fee into cell A28. This is what the fund managers get paid, and is not the 12-b1 fees. Input the annual 12-b1 fee into cell A29. Input the annual 12-b1 fee for the B-share mutual fund into cell A29.

Input the annual percentage amount that is distributed as dividends and interest into cell A30. You can manually override this in any year using column AF.

Input the annual percentage amount distributed as capital gains into cell A31. You can manually override this in any year using column AI.

The program flows left to right in the same logical sequence as what happens in the Real World. The results are then summarized in the graphs below the input cells.

401(k) / 403(b) Sheet

This program is the same as the Mutual Fund Calculator sheet (so refer to the directions above), but is duplicated starting in row 80. The top section calculates the part of the account that is attributable to employee contributions, and the lower, employer contributions.

The stacked graphs then display the total amount of account balance, but in two sections (employee and employer).

Growth and Payout Calculator Sheet

This is a modified asset sheet carved out of the Real World Retirement planner (RWR).

Enter the name of the person into cell A3. Input the current year into cell A4. Input the person's current age into cell A5.

Next, enter either the asset’s current market value into cell A6. If an asset has a liability associated with it, like investment margin, then just subtract the liability from the market value. For example, if you have a stock portfolio that can be sold today for $50,000, but you owe $10,000 margin, then input its value today as $40,000. You can compensate for the leverage by using high rates of return in column C until the loan is paid off.

The only time that you would leave cell A6 blank, or enter $0, is when this asset currently does not have a market value, and you plan to make contributions to this asset either the current year, or any year in the future. In this scenario, the asset would "come alive" when the contributions started.

Next, enter the age when this asset will start to produce, generate, or pay out income to be spent into cell A7. The program will then use the payout method you selected and start paying out income at this age.

Next, enter the annual rate of return that this asset will grow at, into cell A9. This annually compounding growth rate is applied to the asset after all contributions and withdrawals are added or subtracted. This rate of return is automatically applied to the asset at every year, unless it is manually overridden as discussed later.

Be sure you enter the growth rate using the correct Excel percentage format! In older Excel versions, entering a 1 would result in 100%, and in newer versions, 1 would give you 1%. If you enter a rate of return more than 12%, you will get an informational only error message "?" (assuming rate of returns over 12% will get you into trouble so we don’t recommend going over 12% on any asset in any market condition!). If you enter a negative rate of return, you will get a real error message because you can’t do that. If you want to show a negative rate of return, use the Rate of Return Manual Override column. You can use this, or the Contribution Manual Overrides, to manipulate beginning-of-year market values to be whatever you need them to be, in any year.

Remember that this input field (A9) is where the automatic rate of return number is entered. You still have total control of each asset’s rate of return in every year by using the Rate of Return Manual Override column. For example (see the RWR demo), say your client has something that has an erratic rate of return - Gold in this case in the demo’s Oldest Asset #1. You would input this data using the Rate of Return Manual Override column (column C) in conjunction with the withdrawal Manual Override column (column BC).

Next, enter the annual inflation rate into cell A10. This is only used with payout method #3.

Next, enter the percent amount of this income that will be subject to taxes into cell A9. If you enter 100%, then all of the income withdrawals will have income taxes taken out. If you enter 0%, then none of these withdrawals will have taxes deducted from them.

Contributions to Assets: You have total control over annual monthly contributions to each asset. What? When you input the amount of monthly contributions into cell A11, these amounts are multiplied by 12, and then added to the asset’s balance. Then the growth (or loss) rate is applied.

Using the monthly contribution Manual Override column, you can control contributions to the asset on a year-by-year basis for every year in the 70-year window. The amount you enter in the Manual Override column is monthly too, but it calculates annually by multiplying by 12. Note: Amounts entered into this Manual Override column really are overrides - meaning they wipe out the current year’s automatically generated contribution number for that year.

Start by determining how much will be added to this asset over the whole first year. Then divide this amount by 12 to get an average. This input cell asks for a monthly amount because most people think in terms of adding monthly amounts to assets like 401(k) plans. Monthly contributions cannot be negative or you will get a real error message. Also, if they are $5,000 or more, you will get an informational only error message. All contributions are after tax just as if the client wrote a check to a mutual fund family.

Next, enter the rate of annual increases in the monthly contributions into cell A12. What again? This input grows the amount of annual contributions by the amount you enter. For example, 10% of pre-tax income is going to a 401(k) plan, and you expect to get an average 2% raise every year, then your contributions (made monthly) will be increasing at a 2% annual rate because of the 2% annual pay increase.

Next, enter the age when the first monthly contribution will be made into cell A13. This can be any year, even after retirement has begun, as long as it’s not earlier than the current age or the asset’s effective age

If you’re a detail person, here’s an example: Assume you have a 401(k) plan you’ll be eligible for in September next year. Currently, the asset called "401(k)" has no market value because it doesn’t exist yet. This asset will be "brought to life" by making contributions to it.

Next, enter the age when these contributions will end into cell A14. Most of the time the correct way to input this is to use one year less than the age of retirement. For example, if you retire at 60, you would want to set the last year of contributions to be age 59.

You are able to continue contributing to the asset after retirement age (but not past age 69 if you use payout method #4). You’ll get a real error message if you enter the last year at an age earlier than the starting year, earlier than the current age, or the earlier than the effective age of the asset.

Real World Asset Payouts: Each of the nine assets has ten payout methods to simulate, as closely as possible, life in the Real World.

"Payout" means how a financial asset pays out income for someone to spend. This income can come from combinations of interest, dividends, capital gains, and return of principal.

Payout methods 1-4 & 6-19 can be used simultaneously with payout method #5 - the Manual Withdrawal column. If you choose payout method #5, you cannot use any other payout method with that asset.

These ten Real World asset payout methods are explained below. To select one, just enter the corresponding number into cell A15. After selecting the payout method, look at cell B25 and A16 to see if that payout method requires additional input into cell A27.

The Ten Asset Payout Methods:

1) Lump Sum: 100% of the asset’s balance is paid out as a lump sum at any year specified (whether retired or not, or way past the age retirement has already started). You can still use the Manual Withdrawal column BC to take out amounts before the 100% lump sum year, but you can’t use it on the same year, or afterwards. If you attempt to do this, it will ignore any amount you enter into the manual withdrawal column and will not give you an error message.

If you want to divide the lump sum into two parts to tax them differently, then figure it out and do it manually. For example, if you have a $100,000 mutual fund that’s 50% basis and 50% taxable at capital gains rates of 20%, and you assume you're in a 28% marginal income tax bracket, then enter 32% into cell A10. We got that by first figuring the percentage that 18% is of 28%. It’s 64%. Only half of this is taxable, so half of 64% is 32%. This example used marginal rates, and the program uses average, but the end result is the same. It’s up to you how you want to enter data and account for taxes.

2) Yield Only: The biggest use for this is when you want to keep the principal intact forever. It has more uses than that, as explained below. For example, to account for individual bonds in the Real World, you can use the Manual Withdrawal column to lump sum the maturity proceeds.

You can also simulate any number of individual bonds maturing on different years by using the Manual Withdrawal column in conjunction with the Rate of Return Manual Override columns. Here you would enter the total market value of all of the bonds into cell A6. Then manually determine the interest rate every year by taking a weighted average, and enter this rate in the Rate of Return Manual Override column for that year. Then use the Manual Withdrawal column and enter the individual maturity proceeds until all the bonds have matured.

If you want to get real fancy with fluctuating principal (e.g., bonds that self-destruct like GNMAs) you can use the Rate of Return Manual Override column to make the market values what you want in every year. Another use of this payout option is simulating assets like bond mutual funds by assuming a total return of 7%, taking out 6% interest income, and having the principal grow by some small amount. Or slowly deplete it by taking out 7% and growing it at 6%.

Municipal bonds, or mutual funds, can be simulated by setting the amount taxable input field (cell A10) to 0%. If there’s a capital gain to pay when it’s sold or matured, enter a tax inclusion rate into cell A11. Or the tax rate on any mix of state and federally taxable scenarios can be run too. You basically have control over most any bond scenario with this payout option.

Note: Income generated by assets before retirement (payout age) is assumed to be spent, so it’s not reinvested or taxed anywhere.

3) Inflation Adjusted Income Stream Generator: This method automatically answers the question, "What’s the most money I can take out of this asset every year, have this income stream keep up with inflation every year, and have it last until I’m 100 years old?"

All you do is enter a 3 into cell A15 to select third method, and the age when you want the asset to be depleted into cell A25. Everything else is automatic.

You should check the numbers on the asset page, as some combinations of rate of returns and depletion ages will result in the asset not depleting in the exact year. You can play with the rate of returns and depletion ages to get the results you want.

4) IRS Required Age 70 & 1/2 Minimum Distributions: It only does it starting at age 70, and it only uses the recalculation with single life method. The joint methods are near impossible to program. Manual withdrawals will work before age 70, but not after.

5) Specific Amounts: This method just disables all of the other eight payout methods, so that only manual withdrawals using the Manual Income Withdrawal column will work. This is located at column BC starting at row 27. In other words, you have to manually enter income payout withdrawals in each year by inputting the year-by-year Manual Withdrawal amounts.

If you enter a number that is larger than the amount of the asset’s beginning of the year balance, only the beginning of the year’s balance will be used as income, and you will not get an error message.

Numbers entered into this column do not override the normal payout amounts - they are added to them, so they are not "Manual Overrides" like in most other similar input fields.

6) Single Life Fixed Annuity: This method of paying out income trades the asset’s market value in for a permanent income stream. This income stream most resembles a single life annuity (or old style defined pension plan).

The income stream does not inflate; it wipes out the asset’s market (principal) value when it starts to pay out, it pays out until death, and cannot be altered once it begins.

Note: The asset’s market value is supposed to vanish when the income stream starts, so this is not a program error.

If you really want to use exact numbers from tables, you’ll need to get the amounts from somewhere else (like the insurance company that holds the annuity) and enter them manually using payout method #5.

7) Inherited IRA or IRS Rule 72(t) Governing Pre-Age-59 ½ Tax-Qualified Plan Distributions: What? To sum this long story up, if you have a tax-qualified plan (e.g., IRA), the IRS has rules to make sure people pay the taxes that they saved during the accumulation phase.

There are also rules saying that if you take money out of an IRA before you turn age 59 ½, then you have to pay a 10% premature distribution tax (in addition to ordinary income tax).

There are three ways to avoid the 10% penalty tax in section 72(t). The three methods are not the only ways to qualify for these exceptions. All the IRS cares about is that you are receiving "substantially equal periodic payments" from the IRA, and thus are paying taxes on this income.

Payout method #4 is also a way to do this (but don’t use it before getting advice from a tax pro!).

Payout method #7 uses the same calculations used for Inherited IRA distributions and the 72(t) method called Life Expectancy. Basically the end of the last year’s balance is divided by the life expectancy of the owner. These life expectancy numbers go down every year, so the required payments escalate to the point that all of the IRA is distributed over the person’s lifetime (assuming that they live until life expectancy).

Of the three methods of doing 72(t), this method will result in the lowest annual required minimum distributions from the IRA.

8) IRS Rule 72(t) Governing Pre-Age-59 ½ Tax-Qualified Plan Distributions Using the Fixed Amortization Method: The same story applies as above, but the formula is different. A time value of money formula is used, using life expectancy numbers, end of the last year’s balance, and an assumed interest rate. This method will result in the highest annual distributions.

9) IRS Rule 72(t) Governing Pre-Age-59 ½ Tax-Qualified Plan Distributions Using the Annuitization Method: The same story as above. This method uses an actuarially determined annuity factor, so be careful! You can get examples from the IRA website: http://irs.gov This method produces about the same annual distributions as payout method #9, but are just a little less.

25-Year Dollar-Weighted Rate of Return Calculator Sheet

Dollar-weighted rate of return and IRR (internal rate of return) are pretty much the same thing. To make a long story short, the present value of the cash flows are just added up to get an NPV (net present value), and then compared to the ending period's value, and an overall rate of return is given.

This sheet is designed to work well with people's actual Real World monthly brokerage account statements. All of the information needed is there. So all you need to do is pull out all of them that you can find.

A unique feature of this sheet is that it will calculate an overall rate of return on all of your assets that you input data for, not just what's on a single set of brokerage statements.

So in the following directions, it's assumed that you have three sets of investment account statements. The rest of the directions follow along with the inputted demo data.

Start by inputting the year in which the portfolio started into cell A18. This is not the current year. It's the first year that you have data to input, and the first year you want to start computing numbers for. If you have statements going back to 1999 for one account, and only going back to 2000 for the others, then you're either going to have to guess what happened in 1999 for the ones without statements, or just start with year 2000. It's best to not guess, and start in the first year in which you have statements, or data, for everything.

The total of all three accounts in January 2000 is assumed to be $100,00, so this is what you input into cell A19.

Now add up all three end-of-year account values (12/31/2000) and input the total into cell A20. This is AFTER all of the cash flows have been account for (contributions, withdrawals, dividend and capital gains). This is amount of money you would get (before taxes) if you sold everything on the last day of the year.

In cells A21 - 23, input estimated tax rates that you pay on all dividend and capital gains that were received in cash and NOT reinvested into the account.

In rows 25 and 28, input the total of all three accounts' dividend and capital gains that were received in cash and NOT reinvested. Do this on a monthly basis as it matters in the IRR calculations.

The monthly before- and after-tax IRR for the year is given in cells J19 & J20. The annual rate of return, of just the beginning and ending values, is displayed in cell J21. This is not accurate, and is mostly meaningless, but some people like to see it. The important, and accurate annual rate of return numbers, are shown in cells N21 & N22. This is dollar-weighted rate of return for the year (IRR).

The sums of all of the monthly cash flows are shown in column O.

The process is just duplicated going down the sheet for the next 24 years. The only exceptions are that years 2 - 25 display the running annual total average rates of return. So in year #2, cells M38, I40, & M40 shows the average for both year #1 & year #2.

The only other difference is that you need to account for the ending year. This is done by not inputting anything into column B in the year after everything is either liquidated, or you want to stop. In the demo, all three the portfolios were assumed to be liquidated in March 2005. So nothing was input into cells B134 - B137.

Year #6, 2005, will not show a totally accurate annual rate of return because everything was sold mid-year, but then this is not the most accurate way to do this in the first place. The best way is to use the Time-Weighted Rate of Return method, which is on the next sheet.

25-Year Time-Weighted Rate of Return Calculator Sheet

The input and information display scheme is the same as the above, Dollar-Weighted Calculator, but with two differences.

First, you need to add up the end-of-month values for all accounts and input them into the appropriate month's row starting in row 23. These are all AFTER all cash flows (contributions, withdrawals, and dividend and capital gains distributions).

Next, input the word "sold" into the month the portfolios were liquidated, or when you want to stop calculating (which would most likely be the current month.

This is a much more accurate because returns are compounded whenever there is a cash flow. So cash flows do not distort the rate of return calculations like they do in the dollar-weighted methodology. Most all expensive portfolio management software uses time-weighted rate of return.

Investment Management Fee Calculator for Investment Advisors

If you're not a fee-based investment advisor, then these four sheets won't mean anything to you. If you are, then this may save you several hours every quarter.

First, determine which of the four tier schedules to use. There is one sheet for each.

Then input your breakpoints and fees into the green-shaded cells in columns L and O. all of the green-shaded input cells need to have valid data for it to work correctly.

Then input the client's account balance at the valuation date into column F (usually either the end of trading on the last day of the quarter just passed, or the first day of the new quarter).

The totals are then displayed. The amount to bill the client on a quarterly basis is in column M.

You can input data into the columns C - E if you want to or not, as they're not used in the calculations.

If you get the input data from another source, then you can reference it from these input cells. Then everything may be automatic the next time, and you probably won't have to do it again. We can customize the sheet so it will be automatic for you at the consulting rates.

Just the section you want will print well if you use print selection described in the first link of the third paragraphs above.

Inflation Adjusted Income Stream Generator Sheet

This is also known as calculating an inflation-adjusted systematic withdrawal plan.

In the Real World, people often want to know, "What’s the most money I can take out of this asset (or portfolio or account) every year, have this income stream keep up with inflation every year, and have it last until I’m 100 years old?" In other words, how much can I take out annually, and not run out of money, while keeping up with inflation?

Just input basic data into the green input cells in column C and everything is automatic. The before-tax amounts to withdraw are shown annually in column H, and the account's end-of-year value after the withdrawals are in column I.

We can guarantee that the results shown here will NOT be what happens in the Real World, because the data you input, and the results, will not occur every year. So be conservative with your input assumptions, and update your input annually, if you really want to not run out of money before a certain age.

Portfolio Yield Calculator

Estimates the combined average income/dividend yield on your total portfolio; how much income, or paycheck, your total portfolio will produce on a daily, weekly, monthly, and annual basis; how much as a percent each asset is of the total portfolio; and how much each asset is estimated to pay out on a daily, weekly, monthly, and annual basis.

Input asset names into the green-shaded cells of column B, the dollar amounts into column D, and the asset's estimated annual yield into column F.

Everything else is automatic, and the point is to see how much the combined annual yields will produce in income on a daily, weekly, monthly, and annual basis.

All amounts are before-tax.

Age 70 and a Half MDIB and MRD Calculator and Inherited IRA Minimum Distribution Calculator

If you have an IRA, or similar tax-qualified retirement plan, where you were able to get tax deductions when you contributed money into the plan, then the IRS wants you to pay that money back. Some people let their IRA's grow without taking any contributions from them, hoping to escape this. So the IRS requires people to start taking distributions from them starting in the year they turn 70 and a half. They publish tables on their website on how to calculate these minimum amounts. One can take more out, but not less than the minimum.

The account balance is just divided by a divisor that decreases every year, resulting in a higher percentage of the account needing to be distributed every year. All of the amounts distributed are then subject to ordinary income tax rates.

This sheet won't work using fractional ages, so don't input things like age 59.5.

The process goes by several names, but they're all the same thing: MDIB stands for Minimum Distribution Incidental Benefit, MRD stands for Minimum Required Distribution, and RMD stands for Required Minimum Distribution.

For those people that do pass away before the IRA is depleted (at a maximum age of 115), the IRA is passed on their heirs, which are then subject to similar minimum distribution rules. That's why there are two RMD calculators, one for owners, and one for beneficiaries. There used to be different table for male and female, but it was replaced by a unisex life expectancy table a few years ago. So if you see tables or MRDs for male and female, they are out of date, and shouldn't be used.

The amounts calculated are estimates based on the tables. To be sure you don't' get dinged penalties for not taking enough out; you may want to round up to the nearest thousand.

Input the current IRA values into cell B7 or Q7. Input the current year into the cell below, and the name, if you want, into the cell below. Input the age the person will be at the end of the current year into cell B10 or Q10. Then estimate a tax rate and input that into the cell below. Then estimate an average rate of return into the cell below.

The estimated amounts that you'll have to withdraw from the account is shown for each year up until an advanced age that the IRS assumes you will pass away at (currently age 115).

These numbers are just estimates because they all depend on the actual tax rate and rate of return the account will grow (or shrink) at.

The Inherited IRA calculator is for calculating the amount of minimum required distributions only when someone has an IRA they inherited from someone else. You'll need to scroll down to see the results.

IRA 72t Early Withdrawal Calculators

72t is the section of IRS Code that governs how to take money out of tax-qualified plans, like IRAs, before the normal distribution age or 59 and a half, without having to pay premature distribution penalties.

Before this code, people that wanted, or needed, to retire before age 60 were hurt unfairly.

In 2002, the IRS realized the error of its ways, and made exceptions to these rules in Section 72 of the code. Part "t" makes exceptions to getting these premature distributions, because many people are already retired at ages before 59. Also, people that have inherited IRAs may need the money now. There are three ways to avoid the 10% early distribution penalty tax in section 72(t). The three methods are not the only ways to qualify for these exceptions.

All the IRS cares about is that you are receiving "substantially equal periodic payments" from the IRA, and thus are paying taxes on this income. To make a long story short, all the IRS requires is that you start making withdrawals using "substantially equal periodic payments" to qualify. This allows people to retire before age 60 and get retirement income from their retirement plans, without being penalized. The rules make it unfeasible for people to just tap into their retirement plans because they either need the money to pay for something, or want to use it up too fast, and be left with nothing later in life.

Even though the IRS will allow most ways of withdrawing "substantially equal periodic payments," they have three set formulas for people to follow that serve as guidelines. These are called 72t Early Distributions. Using the formulas given on the IRS website, the three calculators were constructed.

Life Expectancy Method: Basically the end of the last year’s balance is divided by the life expectancy of the owner. These life expectancy numbers go down every year, so the required payments escalate to the point that all of the IRA is distributed over the person’s lifetime (assuming that they live until life expectancy, which most won't be a long shot). Of the three methods of doing 72(t), this method will result in the lowest annual required minimum distributions from the IRA.

Fixed Amortization Method: The same story applies as above, but the formula is different. A time value of money formula is used, using life expectancy numbers, end of the last year’s balance, and an assumed interest rate. This method will result in the highest annual distributions.

Annuitization Method: The same story as above. This method uses an actuarially determined annuity factor, so be careful! You can get examples from the IRS website: http://www.irs.gov/ This method produces about the same annual distributions as payout method #9, but are just a little less.

Stock Option Calculator

This is not for ESOPs (Employee Stock Option Plans). It's for ISOs, (Incentive Stock Options), NSOs (Non-qualified Stock Options), and ESPPs (Employee Stock Purchase Plans).

Everything needed to manage your stock options is here except: It does not deal with vesting, expirations, or taxes. The differences between ISO/NSO/ESPP have to do with these elements.

Because this is such a low-demand product, and it was so hard to program these elements, it was not implemented. Still, this is far superior to all of the free online stock option calculators, and most of the sites where you have to register and login (and get their spam). There are some programs in the $100 range that deal with these elements correctly.

So when using this, just be sure that you input the correct amounts of shares to exercise into column F.

If shares are not fully vested, or they have expired, then results will not match the Real World. Also, you have to estimate the tax rate on each batch of shares sold. Fortunately, you can use the manual overrides to have a different rate in any year.

The program works on a quarterly basis, because this is how most people actually get their options. They get money taken out of their paychecks, and this after-tax money is put into a savings account, and then at the end of the period, it's used to purchase the options, in one way or another.

So get out your paperwork that lists all of your shares/options, and start inputting the quantities for each quarter that they were granted. Input them into the corresponding annual quarter in column D.

If you have different types of options, you may want to maintain separate spreadsheets for each type.

First, input the stock's current market price into cell B5.

Then input the current year into cell B4. You can make this be a year in the future, to see what the growth may be.

Input the global default tax rate into cell E4.

Then input the Grant Price into column I and the Exercise/Strike Price into column J.

The program will maintain a running total until you tell it that you exercised shares.

When you do exercise shares, or have in the past, input the quarter and year into column G, in the quarter actually exercised, using the Excel format given in cell G. Then input the amount of shares exercised into column F. Ensure that you can actually exercise this amount due to vesting/expirations, and ensure that you don't tell the program to sell more than granted in that quarter.

The reason you do this, telling it to sell shares on a batch basis, is to keep the difference between the grant/exercise prices and current market prices straight.

Use the tax rate manual override column N to account for how much tax is estimated to be due with each batch sale. This is where you can raise the rate to account for the AMT. In order to calculate the AMT, one's total tax pictures would have to be calculated, so that's why this wasn't implemented (we don't do tax software because that market is saturated).

If there are any other expenses to be deducted from the exercise, input them into column R. Some plans may charge you a few bucks for trading expenses every time you exercise.

The point is to be able to use columns M and O to estimate the total value of the options at any given quarter (even in the future).

Average Tax Bracket Calculators

First determine which of the four tax-filing status categories fit your situation - Single, Married Joint, Married Separate, or Head of Household. Use the appropriate calculator.

Input data into the three green-shaded input fields. Then using the resulting Taxable Income number, scroll down to find the corresponding Taxable Income number in the body of the spreadsheet. The Average Federal Tax percentage is in the column to the right. If there's two numbers, then it's a number that's not an even thousand, so it's your call on how to round it.

You can read about this product in more detail here.

Convertible Bond Calculators

There's no more directions here than what's on the sheet already because if you're using them, then you know what they're for.

Please send e-mail if you'd like more clarification on these directions.

Product Information

Fully Integrated Financial Planning Software

Menu of Retirement Planning Software

Asset Allocation Overview with Our Historical Returns

Asset Allocation Software

Asset Allocation Models with Our Historical Returns

Our Monthly Updated Mutual Fund Picks

Family Budgeting Calculator

Financial Planning Fact Finders