Directions for the TVM Financial Tools
|Download the TVM Financial Tools Demo||Main Time Value of Money Software Product Information Page|
(is listed below. The financial planning software modules for sale are on the right-side column)
Confused? It Makes More Sense if You Start at the Home Page
Discounts for Financial Advisers
Questions about Personal Finance Software? Call (503) 309-1369 or Send E-mail to firstname.lastname@example.org
Free Downloads and Money Tools
|First, here are some generic directions that apply to most of the personal finance software on this site:
If you're still shopping, the best evaluation results are obtained by looking at the TVM Financial Tools demo, while following along as you read these directions.
When you're using the program, things go better if you first print out these directions.
Click "Enable Editing" to make it so you can change inputs. If you don't do this, then "nothing will happen" when you input data. Any change is editing (according to MS). This occurs because the program is not in a "Trusted Location." Go to File, Options, Trust Center, to fix that.
The "demo" is the actual financial spreadsheet without the formulas. Except for the seven sheets that function, you won't be able to do anything but look at it. You'll receive a working program after purchase.
If you need to print something on a password protected sheet, and you need to tinker with column and row sizes, and you bought support, and it's in Excel 2007 format, then you can get a version sent to you that does that.
You switch between the sheets (pages) that make up the workbook (AKA spreadsheet) by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing Control Page Up or Down (pressing the Control key and either the Page Up or Page Down key 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 (or just the input areas if there are no separate input sheets). This data flows through the calculation sheets (or just areas if there are no separate calculation sheets), which will then populate the presentation areas (or the sheets to the left of the input sheets), where you look at the results.
Then you fix mistakes, repeat, format, and 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 (if it's not an input sheet). If you could, then you may end up damaging the spreadsheet. Don't input, or type over, any formulas on the presentation sheets because then they will no longer change or function when you change your input data. Input cells usually turn a light-purplish-gray when a non-zero value is entered into them.
About the colored cells: Medium or light gray is either a title heading, or an input cell after a non-zero value is input into it (it's green when either zero or empty). Peach (or pinkish, depending on your Excel version) is a (usually password protected) field that changes when input changes. Light gray cells are information-providing, non-input, cells that don't change with input. White cells usually don't have anything in them, except on the unprotected presentation sheets (and change when input changes). Anything red is usually bad (input error).
About the colored sheet tabs: Blue is a presentation sheet. Light green is an input sheet. Green is both an input sheet, and a presentation sheet. Orange is a calculation sheet. Purple is something else.
Before getting started, save an unused copy of all the programs in a separate folder, so you'll always have the original unaltered file somewhere. Then if you do something like delete a formula, you can easily fix it by copying it back using these original files. Then save files you've worked on using a different file name (and into a different folder).
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.
How to turn a zipped file attached in your e-mail (if any) into an Excel spreadsheet is explained in the text of the delivery e-mail.
If you're seeing this: ####, then either change your Zoom magnification number (using the slider at the bottom right), or make the column width wider. If you see this: #REF! after completing your input, please respond to get a new program immediately. This usually 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. Try again rounding the value to the nearest dollar. If it still happens, then send e-mail with the spreadsheet name, sheet tab name, and exact cell number, so it can be changed.
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).
How to send financial plans to clients via e-mail, without sending the whole program.
You should make CD (or at least USB flash drive) backups of all of your work at least on a monthly basis, and store the CD in a safe location, but not where your computer is. Flash drives are not safe because they're vulnerable to EMP, eventually wear out, and are in general, too flakey. It's critical to back up your data, but not programs. More generic PC tips are 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 each compounding period, usually added to or subtracted from 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.
Below is an image of the best financial calculator ever created - the HP-12C:
TVM Tools Directions
Open the file TVM Calculators.xlsx with MS Excel version '07 or later.
The first seven sheets are the financial calculators. Most everything here has self-explanatory directions on the inputs. 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.
Even though the first seven sheets are free, if you find a problem with anything, there's not only support for them, but you'll probably get the whole working program for for pointing bugs out. Perfection is always the goal!
If you don't understand something, that means more detailed directions need to be made, so please e-mail.
Financial Calculators: TVM Calculators Sheet
The first two financial calculators, #1A & 1B are identical. This is so you can use two different sets of input and compare results of two scenarios.
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, then input 3% inflation, you really just input 36% annual inflation, and this will yield very strange results.
Don't input the number one into cell D8 or P8 due to an Excel glitch.
The results of financial calculators #1A & B are also shown on the charts below them. Charts are titled to show which money calculator they belong to.
Financial calculator #2 is a simple future value calculator that shows interest earned and total amount contributed. It calculates the future value of an investment given its current value (present value), payments (more money being contributed every compounding period), an interest rate, and time (how many compounding periods there are).
The 0 vs. 1 input tells it when to apply the payments. 1 applies payments at the beginning of the period, so future values will be more because interest applies for one more period, and 0 applies at the end of every compounding period.
Financial calculator #3 (cell V9): The Gross Wage Calculator has two sections, which are independent of each other. When you input data into cell W10 and Y10, the results display in the three cells below them. Everything assumes one works for 40 hours per week.
Financial calculator #4 calculates APR and how much interest results using four different compounding periods. There are no cells below APR, because that's what you input above.
Financial calculators #5A & 5B are for seeing the interest and principal breakdown of a loan. They are identical so you can use two different sets of input and see the differences. Input additional annual principal payments made into columns AJ or AP. All of their graphs are to the left.
Financial calculator #6 is a future value calculator, but it allows you to input ages instead of just the numbers of years. The result is the age a financial goal will be reached. This is the same as an "N" finder.
Financial calculator #7 estimates the present value of someone's current 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 in a lump sum today. This is helpful in determining life insurance needs, and is a very scaled-down version of the same concepts used in the real life insurance needs software. The inflation rate negates the effects of the income growth rate somewhat.
Financial Calculator #8: This is a simple Present Value calculator to see how much a future amount and/or stream of money is worth today.
Financial Calculator #9: 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. So every dollar of need requires $1,000 to fund it.
Financial Calculator #10: The Life Expectancy Calculator results will display somewhere in cells AW18 to AZ21. This is how many years on average the investor 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. If you're using other calculators for this, and it breaks it down into male and female, then it's using very outdated mortality table data (so it's too old to use).
Financial Calculator #11: This tells you the amount of annual payments (P) needed to solve the equation based on your input. Don't input negative numbers anywhere.
Loan calculators #12A & #12B: There are two parts to this calculator that display loan parameters based on how much of the loan is paid off per compounding period as a percentage. The two graphs below show principal declining over time.
Financial Calculator #13: Simple interest rate calculator to find the compound APR.
Financial Calculator #14: Net Present Value calculator. This is different than the Present Value calculator (#8), because the cash flows don't have to all be the same.
When you input a series of cash flows, the result in cell BM4 displays 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 also 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.
Don't leave cells blank in between cash flows - input zeros and they will be considered as having no money coming into or out of the investment during this period.
The interest rate input into cell BM5 is per compounding period - so if your cash flows are monthly, then the interest rate should be monthly too (so if all you know is the annual rate, for example 10%, then divide it by 12, like this in cell BM5: =10/12).
The text, "Net Present Value of Discounted Cash Flows:" is supposed to be one sentence, but it may look like it's two due to formatting limitations.
Financial Calculator #15: Bank CD Penalty Calculator using the percent of interest rate method. These are just rough estimates and are not what an actual bank would charge.
Financial Calculator #16: Calculate the annual payments needed beginning at one age, to reach a goal at the ending age. The present value input is how much you have saved up for the goal today. The growth rate is the annual compound rate of interest and the tax rate is the amount of taxes due on the annual interest.
Financial Calculator #17: Bank CD Penalty Calculator using the three-months of interest as a penalty method. These are just rough estimates and are not what an actual bank would charge
Retirement Calculator #18: Retirement Monthly Nut Inflator. It calculates simple answers to how much is needed to fund retirement with just a few inputs.
Input how much percentage of the current income input into cell BZ10 you want to have during retirement. If you currently make $100,000 a year, and you want $75,000 during retirement, input 75%. Compare the results of cells BX13 and BZ13. This shows how powerful the affect of inflation is. The answer you're looking for is cell BZ13. The two graphs below show the annual income need numbers.
Final Expense Calculator #19: 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. Please note that these are just the "lump sum needs" and DO NOT account for the loss of the breadwinner's income. So you'd need to add the results of Financial Calculator #7 to this if you were depending on this crude method to determine life insurance needs.
Investment Calculator #20: This calculates the taxable equivalent yield after inputting a tax-free yield. In other words, how much taxable yield you'd need to get on a municipal bond to end up with the same amount of money as you'd get on a federally-tax bond of the same maturity and credit quality.
Investment Calculator #21: This calculates the tax-free equivalent yield after inputting a taxable yield. In other words, how much federally-tax yield you'd need to get on a municipal bond to end up with the same amount of money as you'd get on a taxable bond of the same maturity and credit quality (after paying the taxes due).
Financial Calculator #22: This simple compounding period calculator finds the numbers of periods (N) needed to solve the TVM equation (it's an "N" finder).
Money Calculator #22 & 23: Percentage change calculators. There's one for percentage inputs and one for decimals.
As you know, it's a confusing chore to calculate simple percent change, especially when one number is negative and the other positive. This calculator will do all that simply by just inputting the two numbers into the two green-shaded input cells. It has four results, because three of them will be wrong. The one(s) that say, "OK" next to it is / are probably correct (actually one "OK" will probably be wrong and the other one correct). The ones with question marks are probably not correct, so ignore them. One needs to use common sense to determine which of the two results that display OK are correct. The formulas display in column CV to help you decide which one is correct. Only the percentage change numbers in either columns CM or CS are the answers. All of the other numbers are just to help guide you as to which one of the four results are correct.
Insurance Calculator #25: Fixed annuity income eroder. Input the fixed annuity's annual payment to you, your current age, estimated annual inflation rate, and combined annual tax rate, and the table and charts show the loss of purchasing power over time (due to cost of living inflation).
Insurance Calculator #26: Fixed annuity true yield calculator. Input the gross total amount of money you've paid into the fixed annuity (the amount you wrote the check out for, or the total amount transferred into it electronically). Then input either the annual or monthly payment (or estimate) when annuitized. It then tells you the actual annual yield is (it's going to be much less than advertised).
Do not input "withdrawals" or "loans" here. Input either the actual gross (before tax) annuity payments to you when the annuity is annuitized, or the estimated amount from an annuity ledger program (get it from your agent). If the ledger shows a "market, net, gross, or surrender value" of any kind, then this is NOT the correct payment number. Only input the periodic payment to you when the annuity is annuitized (the deal where you are, or will be, stuck with it for life). Annuitized annuities no longer have any market, net, gross, or surrender values.
Don't input payment amounts into both the monthly and the annual input fields at the same time or it won't work. Use them one at a time.
Money Calculator #27: Rule of 72 Calculator. Input a growth interest rate and the result is a gross estimate of how many years it takes to double your money at that growth rate.
Two-period Investment Rate of Return Calculator
Money Calculator #28: This investment calculator determines the blended rate of return for two time periods.
Just input the pre-15-year total return into cell DT8. Then the 15-year total return into cell DU8.
Then input the total of months in your total time frame.
Then the combined blended linked rate of return will display.
Driving Time Calculator
Calculator #29: Input the total number of miles remaining on your trip into cell DY4.
Input the percentage of time you estimate you'll be driving at the various speeds. In the demo's example, you know that your 1,000 mile trip will have 50 miles stuck in city traffic, so input numbers into cell DZ4 until DZ5 is 50.
Just add time driving zero MPH to the total time.
All of your input needs to total to 100% or you'll get the red error message in row 8 telling you how much your off by.
The rest is hopefully self-explanatory. Just play around with it until it does what you want.
Financial Calculator: IRR Calculators Sheet
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 calculator will tell you what the overall period, and annual, rate of returns are.
IRR is weird, especially in Excel, so if you don't "know what you're doing" then you're going to get "the wrong results." There are too many things that will go wrong with this, so don't rely on the results for anything important. If you buy a supported version, then you can send it back with your input and we'll tell you if it's "right" or not. There's nothing that can be done to "fix it" because IRR is just goofy and then Excel makes everything worse (compared to something "real" like an HP12C financial calculator).
If anything strange appears in cells F8 - F11, then Excel is confused because it's a unusually large or small result. If so, then input a guess rate of return into cell F19 until a result appears. If it's still confused, then it's probably because you didn't input something "right." This calculator was built to be "robust," so one of the problems that arise is you'll need to stay on the right path. So if you stray, then you'll get weird results (and it won't display meaningful results).
Input the date of the first cash flow into cell B10. The dates are only for your reference, and don't affect calculations, so it doesn't matter what format you use. Just ensure that dates input into column B have the same amount of time between all of the rest of them. Column D is the same empty row for this, but you can input anything you want here.
Input the remaining cash flows into column C. The first cash flow needs to be input into cell C10, and is should be very big and negative. It 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 large and positive.
Ensure there's nothing input after (below) the final cash flow.
Important: Every cash flow period has to have a number input into it. If nothing happened during that cash flow period, then input 0 (0 is a valid cash flow value).
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.
This is the opposite calculation as Financial Calculator #14, so reading those directions above may help too.
Financial Calculator: Interest Rate Calculators Sheet
Just input deposits into column F and then different rates of return into row 4 (columns J & P), and the differences in the amount of money accumulated given the different growth rates will display (and show up in the charts).
Column Q allows you to change the interest rate on a year-by-year basis on the growth of money shown 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 sheet is for calculating the long-term differences in growth assuming four different compound interest rates (and one simple interest rate).
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 in every year (interest on principal plus interest).
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 interest 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 (you'd only get interest on the original principal amount).
All five scenarios are shown in the graphs to the right.
Financial Calculator: Compound Interest Rate Converters
This is for calculating the difference in periodic interest rates given various compounding periods.
Input the annual base interest rate into cell C6, and the results are displayed in column C, which also explains what the program is doing.
This is most useful when taking out bank loans and you want to ensure they're not trying to pull any shenanigans by putting compounding periods less than annual into your contract. So if you think you're getting a loan at 10% APR, then your contract fresh off the printer ends up saying 2.5% quarterly the moment before you sign it, then you're getting ripped off, because 2.5% compounded quarterly is really 10.4% APR. This simple banking scam has been going on a thousand times every day since the beginning of time.
Financial Calculator: Family Ruining Monster Calculator
This is to estimate the total estimated costs of spending "insignificant" amounts of money daily on the typical vices we all engage in.
Input the annual interest rate of a savings account that these funds could have been saved at into cell B14. This inflates the "with interest" values.
Just input the estimated daily costs into cells B4 - 12 and the long-term results, with and without the interest that could have been earned by investing these amounts, display in the tables and charts.
Financial Calculator: Cost of Raising a Child Calculator
This is to estimate the total estimated costs of raising a child, annually, total, and today (net present value).
Totals for each expense category are in rows 29, the NPVs are all in row 30, and combined annual totals are in column AE.
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 E31 (and then used up those savings slowly annually in the future years when needed).
Input estimated annual expenses into all of the appropriate expense columns. Account for annual inflation (cost of living increase) by using standard Excel formulas:
For example, assume cell G4 is $1,000 in the current year. If this were to grow at 5% a year, then you'd input this into G5: =G4*1.05 Then if you wanted to increase it for more years at the same rate, then you'd just use the drag handle (the little black cross bottom right of the cell - you'll know when you're on it when the cursor changes to +), and drag it down.
Keep in mind that you won't be paying every expense forever, so be sure to stop when you think each expense will stop. For example, you can probably stop Day Care in cell E9 (so ensure E9 on down are all empty).
So first input all of the annual estimated expenses into all of the columns in the appropriate year rows
Now input a savings account interest rate into cell E31 (e.g., 0.25 to 2%).
Columns K, M, Y, AA, and AC are the amounts of ADDITIONAL expenses 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 AE29. The present value, how much all of these cash flows are worth if you had all of this money today, and could invest it at the rate input into cell E31, is shown in cell AE30.
So when someone asks you how much do you think it will cost in total to raise a child, you can use cell AE29 to give them the answer. If you want to know how much it would cost if you had this pot of money is a savings account, then the answer is in AE30.
So the next time the spouse says, "Let's have another kid!," you can say, "And where are we going to get cell AE30's amount of money, DEAR"?
Consumer Price Index (AKA CPI) Price Calculator
Three inputs, and you can see pretty much anything you want adjusted for inflation.
Begin by inputting the start month. Then input the end month, Both have to be input using this format: MM/1/YEAR, or 10/1/2014. If you made an input error, then you'll get a red error message saying what you did wrong and how to fix it.
With valid input, the number of months in the inputted time horizon displays, along with the percent price change, and what the nominal dollar amount input changed to.
This is all there is to this money calculator. If you think there should be more, then suggest.
Financial Spreadsheet #8: Financial Planning Practice Valuation Calculator and Modeler
This financial calculator covers most of the bases when you want to estimate how much you may be able to sell your financial advisory practice for today.
You just input estimates of your gross incomes, how these will change in the future, how much of a selling-out haircut you'll take; and then input an estimated range of multipliers, and it shows you the bottom-line value.
Input into cell C5, the total amount of assets under management (AKA AUM) that you will receive trailers on. These are things that you get paid on, without having to "do more work," like 12b-1 fees, premiums of current insurance policies, and commissions when people are on systematic mutual fund purchase programs. Basically all of the commission income that would come in this year, if you were to totally stop selling.
Input into cell C6, the average percentage amount you estimate you'll receive on the above trailers. This is usually in the 0.05% to 0.2% range, and could be negative too if you stop servicing your client base.
Input into cell C9, the total amount of assets under management that you will receive fee income from.
Input into cell C10, the average percentage amount you estimate receiving in investment management fees. This is usually in the 0.5% to 4% range.
Cell C5 is for commissions and C9 is for fees.
If you have incomes other than (and in addition to) these two main items above, then input this estimated total annual amount into cell C13.
To estimate how much the first two income items will be next year, input how much you think your asset base will grow, or shrink (due to normal market fluctuations), over the next year into cell G7. This is usually in the -5% to 10% range in normal years.
The next three input cells don't have anything to do with business valuation, and are just FYI. Input how much you think your asset base will grow or shrink annually over the next three, five, and ten years into cells G8, G9, and G10, respectively. This is not cumulative, it's annually compounded.
Then input a present value discount rate into cell G12. Then these estimates are shown in cells I6 through I13.
If you're with a Broker Dealer, then more than likely, they're going to be legally able to "steal" some of your clients when you bail. So input how much as a percentage you think they (or anyone else, like a partner) will do that into cells C18, C18, and C24.
Then the total amount of gross income your successor will probably realize next year (without new sales) will display in cell C27, after these "haircuts."
The PV amounts for the next three, five, and ten years display in cells D29, G29, and I29, respectively.
You can also use cells C18, C18, and C24 for other things that will cut into your gross incomes here as well. Just remember to NOT deduct normal operating expenses, as this is already accounted for in the (low) multiplier below.
Then comes the painful part - the practice valuation multiplier. Current practice valuation models (2013) are saying that the range of multipliers are only in the 2 to 3 range. You will be able to see more if you have a truly unique practice, and you more than likely will see less if things don't go your way.
To account for this ever-changing range, this financial calculator allows you to input this range into cells C34 and E34. Ensure E34 is greater than C34 or you'll get an error message.
Then the three most-common things that effect where your practice will fall between this range are in cells C36 to C38. Read the text to figure it out as it's self-explanatory. Then input a number between 1 and 10 as explained there. You can use this for anything you want to as the three items are just the most commonly-used factors that change the multiplier range. As you can see, the higher the number you input between 1 and 10, the higher the multiplier.
The infamous multiplier is then shown in cell I34.
Then the bottom-line gross income value displayed in cell C27, is just multiplied by the multiplier value in cell I34. This is how much the financial tool estimated you can sell your practice for today, and it's displayed in cell H42.
Please note that this financial planning calculator is a very blunt and generic tool just based on what can be read on the Internet for free, and so the amount that you will actually be able to sell out for will guaranteed to NOT be even close to this amount! (How's that for an annoying disclaimer, huh!?)
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 things. To make a long story short, the present value of the cash flows are just added up to get an NPV (net present value), then compared to the ending period's value, and an overall rate of return is displayed.
This financial calculator sheet is designed to work well with investor's actual Real World monthly brokerage account statements. All of the information needed is there. So all you'll need to do is pull out all of them that you can find.
A unique feature of this tool is that it will calculate an overall rate of return on all of your investments 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 B18. 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 calculating numbers for.
If you have financial statements going back to 2013 for one account, and only going back to 2012 for the others, then you're either going to have to guess what happened in 2012 for the ones without statements, or just start with year 2012. 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 2014 is assumed to be $100,000, so this is what you'd input into cell B19.
Now add up all three end-of-year account values (12/31/2014) and input the total into cell B20. 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 (2014).
In cells B21 - B23, 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. These are dollar-weighted rate of returns for the year (AKA 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'll need to account for the ending year's values. 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 of the portfolios were assumed to be liquidated in March 2013. So nothing was input into cells B134 - B137.
Year #6 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. Please see the directions for the sheet above, as they're not repeated here.
First, you'll 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 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. See cell G171 on the demo.
This is a much more accurate method 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.
Practice Management Calculator Sheets 13 - 16: Investment Management Fee Calculators for Investment Advisors, with Performance Fees Calculator
These sheets are the same as sheets 17 - 20, but they have the additional feature of being able to calculate investment management performance fees. In addition to regular investment advisory fees, it calculates the additional annual fees some investment advisors charge when the investment portfolio performs above and beyond a certain dollar amount.
See the directions in the section below to operate the four regular tier fee schedules. There is not much here in the way of directions, because investment portfolio managers already know what this is all about.
It only calculates fees on an annual basis.
Input a High-water Mark. This is the threshold the investment portfolio value has to be before performance fees start to apply.
Input a Hurdle Rate. This is the amount of performance in annual APR the account has to grow to before the performance fees start to apply. In the demo, 4% was used.
So the first 4% over the High-water Mark has to be crossed before the 20% performance fee applies (so really the investment advisor only gets paid 16% of the amount over the High-Water Mark).
At the end of one-year, the High-water Mark is automatically raised to the new value
Investment Management Fee Calculators 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 of tedious work 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 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.
Just the section you want will print well if you use print selection described here.
Inflation Adjusted Income Stream Generator Sheet
This is also known as calculating an inflation-adjusted systematic withdrawal plan. We invented this from scratch in the late 80's due to Real World necessity.
In the Real World, people often want to know, "Whats the most money I can take out of this investment (or portfolio or account) every year, have this income stream keep up with inflation every year, and have it last until Im 100 years old?" In other words, how much can I take out annually, and not run out of money, while always 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.
Input the estimated average long-term tax rate into cell C30 if you want to account for taxes too.
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. The best way to do this work for real is to buy the right tool, which is a retirement calculator.
Portfolio Yield Calculator
This financial tool 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 investment is of the total portfolio; and how much each investment is estimated to pay out on a daily, weekly, monthly, and annual basis.
Input up to 100 investment names into the green-shaded cells of column C, the dollar amounts of their values into column E, and the investment's estimated annual yield into column G. Then select income payout time periods and month using the drop-down menus in columns E & 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, semi-annual, and annual basis.
All amounts are before-tax and do not account for inflation.
The last five investment input areas have their months set up so you can input odd monthly cash flows manually.
Age 70½ MDIB or MRD or RMD Calculator and Inherited IRA Minimum Distribution Calculator
Input the current IRA values into cell B7 or Q7. Input the current year into the cell below, and the investor's name if you want to, into the cell below. Input the age the investor will be at the end of the current year into cell B10 or Q8. 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 annual 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).
In the Real World, you'll have to use the actual account value on an actual date to calculate the actual amount of distribution. Then to be sure you don't get dinged penalties for not taking enough out; you may want to round up to the nearest thousand.
These future numbers are just estimates because they all depend on the actual value on an actual date and then the tax rate and rate of return the account will grow (or shrink) at, based on the IRS tables. The reason for this retirement income calculator is that you can manually use these estimates as input into other financial plan / retirement planning software programs. Then you can run long-term projections that answer the big questions.
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, because all of the ages will be blank until the current age.
The tax rate doesn't affect any of the distribution amounts, it's just used to estimate taxes on those two columns.
IRA 72t Early Withdrawal Calculators
Hopefully the inputs are self-explanatory. See the text on the product page for an overview of what this is all about.
The Age Distribution being in row 6 is year you want to retire. This should be before age 59½ because the whole point of this is to get money from your IRA BEFORE age 59½ without paying the 10% premature distribution penalty tax. After age 59½, you can take out all you want to whenever you want to and there are no penalties (so using this financial tool becomes irrelevant at 60 and above).
The tax rate doesn't affect any of the distribution amounts, it's just used to estimate taxes on those two columns.
The point is to just see which of the three methods you like the best.
Social Security Calculators
These six calculators compare the differences between starting benefits at 62 and 67 or 70. This is the great debate that these calculators finally resolve once and for all. You can read about it on the analysis of Social Security page.
Just input the pertinent data in cells B5 through B10.
B5 should be between 4% and 9%.
B6 should be between 2% and 4%.
B7 should be between 7% and 35%. The tax rate in cell B7 is an average rate, not a marginal rate (see and use the average tax bracket calculator below). The 28% shown in the demo is very high. Most retired people have an average federal rate between 5% and 15%. The demo tax rate and inclusion ratio are set to their maximum values because people like to say that after considering taxes, it's always better to wait than to collect reduced benefits ASAP. This high tax rate proves them wrong given the worst case scenarios.
The values input into B8 - 10 should come directly from your Social Security benefits statement.
Then either leave blank (which is 0%) or input either 50% or 85% into column N for the Social Security tax inclusion rate. This is how much of your benefits will be included in your ordinary taxable income. Most people won't be making enough money to have any of their PIA fall into either the 50% or 85% brackets. So you'll need to determine this yourself and see if you do or not. The higher the rate here, the more the argument tilts in favor of all of the clueless personal finance gurus that erroneously tout just the opposite, but still the bottom-line is that they're totally wrong (again and as usual).
The calculators will do their thing, then you just look at the ending values at age 100, the bolded present value figures in row 13 (columns E - T), and the charts.
Present value means how much all of the future years values compress into their worth today. This is the bottom-line in all of these scenarios. As you can see, most of the time, the PVs work out in favor of taking benefits ASAP (60 or 62).
The first chart is the same as the second chart, but the first chart ignores taxes, so you can see the differences easily.
So as you can see, there is not one Real World scenario using actual values that result in the best option being "waiting because benefits go up."
Golden Handshake Calculator
This is for comparing annual cash flows and present values to decide if you should take your employer's "generous" buyout offer to get you to quit your job.
Just input basic data as the input fields ask for in cells B10 through B34, then look at the bottom line in cell T21.
The area above T21 shows all of the present values of all of the annual cash flows. T21 just sums them all up for a bottom line.
If you don't like what numbers are automatically generated in a particular year, then just use the manual override to the right of that cell, and the number you input will be used instead.
Usually, the money coming in is a small pittance compared to the money lost, so it's your call whether or not this will be worth it to you or not.
If you bought support, then you can send it to us with your input and we'll look it over to ensure you did it right before you decide.
This Golden Handshake Calculator is the one and only employee benefit tool you can buy that will calculate the present value of a Golden Handshake offer from your employer. This is the awesome deal where they try to bribe you with an immediate huge lump sum payout to quit your job with minimum fuss, no lawsuits of any kind, and be permanently put out to pasture ASAP.
If you're wondering why their offer is so "generous," this money calculator will clear up that mystery in a New York minute. They've figured all of this out too beforehand, so they know getting you to quit voluntarily is the best way to get rid of you so they can hire "younger, cheaper, and more productive workers without any perks or benefits" (probably outsourced from China or India) to do your jobs for pennies on your dollar. All of this is AKA, "The race to the bottom."
Most naive people just see the huge lump sum and grab it before it "expires" (they're always going to put a deadline on it to get you to make a hasty bad decision by denying you the time needed to run the numbers).
With this calculator, now you can run the numbers and give them a decision an hour after buying it. News flash: If you can do that, and your answer is no, then you'll look like a "true loyal employee that loves their job and cares about the firm." When in reality you ran the numbers and saw what a pathetic joke their offer is! This will work in your favor, especially if you keep the real reasons to yourself. Then after everyone else bails, you may end up being the "big fish in a small pond." This sometimes leads to riches, or you could just be fired without any money or the proverbial gold watch.
News flash, they're offering you a Golden Handshake because it's the most pragmatic way to get rid of you ASAP. They know firing, laying off, and other terms used to get rid of employees causes too much hassle in legal bills, wasted time, reduced productivity (from you and everyone else sitting around whining in a pity party, when they should be working), and negative publicity associated with mass layoffs.
So Golden Handshakes are usually the best way to get someone to go quietly, happily, quickly and cheaply without the usual risks and fuss. They do it not because "they care," but because it costs them less money in the long-term and works the best out of all other options available.
Just so you know, in industries where this is happening, there's also usually going to be NO JOBS anywhere else after you quit either, so you're being permanently put out to pasture. If the employer knew you could just go across the street and get a better job, then they wouldn't be worried about you suing after a year when you realize you've been permanently put out to pasture. Go ahead and start "sending out resumes" ASAP, and you'll see soon enough - there's nowhere to go but the couch and TV after the Golden Handshake deal.
So if and when you do this, be prepared to be "unemployed" for the rest of your life, as this is usually the case, even in "good or normal times."
As soon as you get a Golden Handshake offer, check the "want ads" for your position (in any U.S. city), and you'll see there are none. It's not a mistake that there are no jobs in any print newspaper want ads. There's also no magic website that you just haven't found yet where all of the good jobs are listed. They're not hiding from you, there's just no jobs left on the planet unless you "know someone," or can move to communist China or India and work like prison slave labor for a penny an hour and no benefits. Even in a miserable places to live like Silicon Valley, where the media touts there's jobs a plenty, there's still no jobs. You'll see when you start reading the ads that unless you have experience using XmumbleFrats Version 2.15xa software (in other words, fresh experience with arcane tools that maybe two people alive have), they won't even interview you. Training new employee's that are capable of learning the job using XmumbleFrats Version 2.15xa software in a month, are also a 20th century historical event. All of this is known as "the race to the bottom," and you'll do better by counting on all of this only getting worse as time goes on.
Also, since accepting a Golden Handshake is considered "voluntarily quitting or resigning" and NOT being fired or laid off, you will NOT be able to collect unemployment benefits - in any state, ever (until you work somewhere else for months).
Use this money calculator to see exactly how expensive you are to your employer long-term. Prepare to be shocked both at your costs to them, and how much life will now cost you if you have to make your own way through it without a steady paycheck and/or employer benefits.
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. Then use the appropriate tax calculator.
Input your 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 or average it.
This is the estimate of how much Federal tax you'll pay as a percentage of your gross income.
This is the tax rate that's best used as input into financial plan and retirement planning software.
It does not account for state, local, Social Security, or any other taxes.
Please send e-mail if you'd like more clarification on these directions.
|Financial Planning Software Modules For Sale
(are listed below)
Financial Planning Software that's Fully-Integrated
Goals-Only "Financial Planning Software"
Retirement Planning Software Menu: Something for Everyone
Our Unique Financial Services
Mr. Market Timer's Unique Market-neutral Stock Market Timing Services
Miscellaneous Pages of Interest
© Copyright 1997 - 2017 Tools For Money, All Rights Reserved