If you're still shopping, or making your first report, the best results are obtained by looking at the bond software 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, so you won't be able to do anything but look at it. You'll receive a working program after purchase.

If you try to print the demo directly from your browser, or you just click Print in Excel, it probably won't work well. Tips for working with and printing in Excel are here, here, and here.

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

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.

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 back using these original files. Then save files you've worked on using a different file name (and into a different folder). Tips for advisers saving and organizing client 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.

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.

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. More generic free PC tips are here.

You can copy the input areas to the unprotected Scratch Pad sheet, then make notes there, so you'll know why you did what you did in the future.

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

This is a very large spreadsheet, so it may take a few seconds to calculate. Look for the word "Calculate" at the bottom left and wait for it to go away before inputting more data.

About financial planning software support.

About getting custodian brokerage account data to download into spreadsheets.

About financial plan software updates.

About financial planning software integration (sharing data between modules)

Read how to present sample reports to prospects and clients (and it's the place to start if you're a financial adviser wanting to learn what's going on at the highest level).

How to send financial plans to clients via e-mail, without sending the whole program.

Bond Calculator Directions

The Two 100-Bond Portfolio Duration Sheets

Step 1: After you have unzipped Bond Calculators.zip and turned it into Bond Calculators.xlsx, open it with MS Excel. It will open up to the 100-Bond Portfolio sheet (sheet tab name is Portfolio Duration & Convexity).

This sheet works in conjunction with the sheet to the right of it - the Duration and Convexity to Call sheet. Don't input anything into this sheet until all of the input is done on the Portfolio Duration & Convexity sheet.

This sheet will determine several important averages for a bond portfolio of up to 100 bonds. These results for each individual bond are shown under each bond's input area: Cash flows of both coupon and principal repayment, total return, and how many cash flows it takes to pay off any premium paid.

The results are averaged and displayed starting in cell D9. Each bond's market value percentage of the total portfolio is shown starting in cell D36. All of the bond portfolio's combined cash flows are shown starting in cell G187. There is a chart to show this graphically on cell V186.

Step 2: Start by inputting your first bond into Bond #1's input area. Cell G13 is the bond's name to help keep track (e.g. IBM).

If you hold more than one of essentially the same bond, you should aggregate them all together and input them as just one bond. All of the individual bonds should have the same issuing company, face value, coupon, credit rating, and maturity date. For example, if you own 10 of the same issue of Intel bond, then multiply the current market price and maturity value of the individual bonds by ten, and input those figures.

Next, choose between "Y" and "N" in cells like G14 using its drop down menu. This includes this bond into the portfolio, so its data will be used in calculating all of the averages. Choosing "N" this field will remove the bond from the portfolio, and the resulting averages will still be computed for the all of the bonds input, except for that one. This is useful when contemplating buying or selling an individual bond. You can input the bond, then turn it on and off, so you can compare portfolio characteristics and how that one bond impacts the whole portfolio.

Next, input the "Required Yield to Maturity" for this bond. Those of you with CFA or investment portfolio management training know this to be the ubiquitous magical term called, "k."

This is needed to calculate duration and convexity. This is not the bond's coupon yield, nor the yield to maturity of the bond when you bought it. It can be calculated on the Price | Rates | Yield Calcs sheet.

This required yield, is the yield brought about by the current market yield for similar bonds trading today. In other words, the yield this bond would return if it were bought in the marketplace today. So if you bought a new issue 10-year bond at par yielding 5%, and now the market price is 98, this means that interest rates rose since you bought it (because the price went down - the old "bond prices move inversely with interest rates" saying). So, in the case, k will be a little more than 5%.

This is its current market yield that's to be input into this input cell. You can determine this by using the yield to maturity calculator on the Price | Rates | Yield Calcs sheet. If the bond's current market price is the same as when you purchased it, then its yield to maturity will be the same as the coupon yield. The reason this is not automatic, is because it would increase the size of the spreadsheet 10Mb for every bond, and a 1Gb spreadsheet wouldn't work.

The next input, "Current Market Price" is a little tricky for non-professionals. More bond managers buy this set of bond calculators than individual bond investors, and so the input convention used in this cell is what they're used to using.

The decimal format is used most often in pricing bonds. For example, a bond that was issued with a market value, and maturity value of $1,000, would be trading initially at par, or 100.00 in decimal format. As soon as it starts trading, market interest rate fluctuations will change the current market price.

For example, if interest rates rise, and the resulting price of the bond in the markets falls to $999, then the bond's price is 99.90 in decimal format. If interest rates fall and the current market price of the bond is now $1,050, then the bond's price is 105.00 in decimal format.

The next input, Maturity Proceeds, is easy to understand. Input the amount in dollars that you'll be receiving when the bond matures.

The next input, Maturity Date, has an Excel quirk. It may want to default to the 20^th century, so you'll need to type in the whole year instead of just the last two numbers. For example, if you input 6/30/14 Excel may think that date is 30 June 1914. So you'll need to input 6/30/2014 instead. If you're getting #DIV/0! errors, this is probably where they're coming from.

The next input field, Coupon Yield, is also in "professional bond format." If your bond pays $25 semiannually and its par value is $1,000 then the total amount of annual coupons are $50, or 5%.

This 5% is what needs to be input here. The program is set to detect zeros, so do not input 0% if you're inputting zero coupon bonds. Instead, input a tiny amount like 0.0001%, to include a zero coupon bond into the portfolio.

The next two input fields (G20 & G21) use the two most-common ways to rate the quality of bonds. Moodys is the most popular and S&P (Standard & Poors) is the second. They both have the same number of quality increments, but their symbols are a bit different.

Choose between the ratings via the drop-down menus in the input cells. You can determine these values online, by calling a broker, looking it up in a newspaper, or contacting the issuing company. Input the number 0 if you want to skip inputting credit quality for this bond. Any other character may cause an error message, and should be corrected.

Complete inputting all of the bonds into your portfolio until finished before going to Step 3.

Step 3: Now that all of the bonds have been input, most all of the resulting averages can be seen in column D. But before any of the duration and convexity values can have any Real World meaning, an estimated change in prevailing market interest rates must be input.

Use cell G10 to input the forecasted change in prevailing market interest rates. This change will flow through the duration and convexity formulas and the resulting change in the market value of each bond, and the bond portfolio as a whole, will be displayed both in dollar amounts and percentages.

This is the most important feature of this sheet - calculating the resulting market value of a bond portfolio assuming interest rates change. This allows the user to estimate how much money they'd lose or gain if they sold bonds after the impact of this new prevailing market interest rate. As you probably know, holding bonds until they mature negates all of these interim gains or losses.

Step 4: Knowing these values are important, but what if the bonds are callable and probably won't be held to maturity because of this Real World risk? For that, use the sheet to the right, Duration & Convexity to Call.

All of the input from the Portfolio Duration & Convexity sheet are automatically referenced into this sheet. The only input cells that are active are the switches to turn the bond on or off, the amount of the bonds' proceeds if called, and the estimated bond call date.

Input all of the data for all of the bonds that you want to, and that are callable. Then the resulting averages can be seen assuming all of the callable bonds are called on the dates you input, and you received the amounts inputted.

For bonds input into the previous sheet that are not callable, you may want to turn them off on the Yield To Call sheet to keep things simpler.

Step 5: If you to want calculate these parameters from a valuation date other than today, and then input that date into cell G11, here's how.

For example if you're a P&C company that needs to do its annual reporting in January for the year just ended, then you can do that. So if it's 15 January '15, and you need to report the portfolio's duration as of 12/31/14, then input 12/31/2014 into cell G11.

A tip is to use Excel's "Freeze Panes" when working with the 100-Bond duration software, so you can always see the main results and bond credit ratings input data.

The One-Bond Duration and Convexity Portfolio Sheet

The duration and convexity calculators work exactly like they did on the sheets discussed above, but with only one bond at a time.

You can also calculate Dollar Duration, Modified Duration given Macaulay Duration, and the resulting change in value given changes in interest rates.

Input call dates and call proceeds in the place of maturity dates and proceeds to determine data assuming the bond will be called.

If you to want calculate these parameters from a valuation date other than today, then input that date into cell B9. For example if you're a P&C company that needs to do its annual reporting in January for the year just ended, then you can do that. So if it's 15 January '15, and you need to report the portfolio's duration as of 12/31/14, then input 12/31/2014 into cell B9.

The Price | Rates | Yield Calcs Sheet

This sheet has the popular yield to maturity and yield to call calculator.

Delete all of the sample input, if any, in the green-shaded cells from B5 to B12 before inputting your data.

Start input in cell B5, Current Market Price.

This a little tricky for non-professionals. For example, a bond that was issued with a market value, and maturity value of $1,000, would be trading initially at par, or 100.00 in decimal format. As soon as it starts trading, market interest rate fluctuations will change the current market price.

For example, if interest rates rise, and the resulting price of the bond in the markets is $999, then the bond's price is 99.90 in decimal format. If interest rates fall and the current market price of the bond is now $1,050, then the bond's price is 105.00 in decimal format.

Please note that YTM will usually only change when market price changes whole numbers. For example, all of these will result in no change of YTM: 96.0, 96.2, 96.4, 96.6, 96.8, 96.9. But when you input 97.0, YTM will change.

This is because your maturity value is too low to make a difference in the calculations (you're tinkering with fractions of pennies, so this is just an Excel iteration limitation).

To get YTM to change for small changes in market price, just increase the maturity value to $10,000 (so you it won't be working with pennies anymore).

The next input field, Coupon Yield, is also in "bond professional format." If your bond pays $25 semiannually and its par value is $1,000 then the total amount of annual coupons are $50, or 5%.

This 5% is what needs to be input here. Do not attempt to input a zero coupon bond here, as it will stop functioning if zero is input into this field. The zero coupon bond calculators are here on this same sheet to the right.

The next input, Maturity Date, has an Excel quirk. It wants to default to the 20^th century, so you'll need to type in the whole year instead of just the last two numbers. For example, if you input 6/30/14 Excel will think that date is 30 June 1914. So you'll need to input 6/30/2014 instead. If you're getting DIV#0 errors, this is probably where they're coming from.

If you want to calculate these parameters from a valuation date other than today, then input that date into cell B9. For example if you're a P&C company that needs to do its annual reporting in January for the year just ended, then you can do that. So if it's 15 January '15, and you need to report the portfolio's yield to maturity as of 12/31/14, then input 12/31/2014 into cell G10.

The next input, Call Date, has the same input format as above. If the bond is callable, then input its call date and amount of call proceeds into cells B9 & B10.

For maximum accuracy in calculating yield to maturity, input how many days are in the year into cell B11. If you're not a professional bond manager, then input 365. Some professionals (mostly bankers) do their work in 360-day years, so that's why this switch is there.

Input the amount of bond maturity proceeds into cell B12.

The main result is the bond's yield to maturity shown in cell B15. The other results in the yellow cells above and below are hopefully self-explanatory.

If you want to see the yield to maturity (or call) accounting for capital gains taxes due on the difference between purchase price and maturity (or call), and then you can use input cell D8. Since this is a low-demand feature, it's not automatic (it would also make things more confusing).

Just input the estimated capital gains tax rate into cell D8, and everything is calculated below. The after-tax maturity amount is shown in cell D12.

You then manually input this amount in cell B12, and then the after-tax YTM value will display (yes you're deleting the maturity value in cell B12 and using the value in cell D12 instead).

You can also use it on the call section, but you'd need to calculate and input the dollar amount of the call proceeds into cell D12 to get the values first, and then you'd input this amount into cell B12 (then replace cell B12 with the pre-tax maturity value).

The zero coupon bond calculators to the right have detailed input instructions and results, so they're self-explanatory.

There are also two calculators for converting a municipal bond's tax-free yield to a taxable one, and vice versa.

The Amortization of Bond Premium Sheets

We were going to make this so input data automatically flowed from the CY sheets to the SL sheets, but there were too many formatting errors with that. So you'll need to copy and paste input from the CY to the SL sheets manually to ensure they're the same so you can compare.

Input your capital gains tax bracket into cell B6. Yes, this assumes that the tax rate for interest and capital gains are the same, which they are probably not.

Each of the eight sheets has the same independent input field here, so if you want to make comparisons, then you'll need to input the exact tax rate manually into all of the other sheets you use.

This only works with bonds maturing after the current year. Don't input bonds that have already matured, nor mature (or are sold) in the current year.

Please note that years (e.g., 2014) don't matter. All that really matters are the year numbers (e.g., year #1, year #2, etc.). So you can "go back in time" just by ignoring the years and focusing only on the year numbers.

If the bond's purchase price is less than par after returning earned interest to the seller, and then it will be at a discount. If so, then column J will be negative, which will make everything right of that be zero (discounts are not amortized which makes all of the numbers to the right meaningless). This function has an on and off toggle switch that you should not ignore.

Start with Bond #1 in cell B9. Populate ALL of Bond #1's input fields with pertinent data from cell B9 to B18. You'll know when you've input valid data in ALL of the input fields, when the number of bonds shown in cell C7 is equal to the number of bonds you've input. Bonds with incomplete input will be ignored here, and will result in errors to the right.

Don't input anything into the return coupon to seller manual override input field, if you did not choose "Y" to use this function via the input area (B14).

There is input validation everywhere except the bond's name to help guide your input and minimize common mistakes.

Please note that it's going to take some tinkering to calculate amortization on the current year if the bond was sold in the past year, as the program doesn't allow sales or maturities in year 1 (the current year).

So if you're scrambling at the last minute to calculate premium amortization on bonds you sold in the last year to do this year's taxes, then you'll need to buy phone support to figure this out.

The calculated data appears in row 8 column F+. Each column heading has an explanation of each step in the process.

Repeat the input process for up to 25 bonds on the first sheet, CY or SL Premium Amortization (1 - 25).

If you have more bonds, input them in the sheets to the right. These sheets are the same except for the sheet tab names, and no data flows between any of the other eight input / calculation sheets.

There is also a duplicate set of sheets showing amortizations using the Straight Line method.

We're not tax experts nor bond managers, so we don't know when using and reporting using each method is used in the Real World (so please don't ask!). Bloomberg uses both methods, so we just did the same thing to compete.

If you want to run numbers on a bond that you held in the past, then all you have to do is input the dates as if they were current, and then input the rest of the data normally.

The numbers will be the same, but just the years will be off. So if you want to input a bond you sold in 2014, then just input it as 2014. There's nothing the difference in years make in the calculations (only time, difference in dates, matters, not year numbers).

Please note that calculations are performed assuming only one annual coupon payment. So if your bond pays semi-annually, just add them together.

More information on calculation methodology using the Constant Yield method copied from IRS Pub 550 is below (Bloomberg also has a Straight Line method, so we added that too):

If you pay a premium to buy a bond, the premium is part of your basis in the bond. If the bond yields taxable interest, you can choose to amortize the premium. This generally means that each year, over the life of the bond, you use a part of the premium to reduce the amount of interest includible in your income. If you make this choice, you must reduce your basis in the bond by the amortization for the year.

If the bond yields tax-exempt interest, you must amortize the premium. This amortized amount is not deductible in determining taxable income. However, each year you must reduce your basis in the bond (and tax-exempt interest otherwise reportable on Form 1040, line 8b) by the amortization for the year.

Bond Premium: Bond premium is the amount by which your basis in the bond right after you get it is more than the total of all amounts payable on the bond after you get it (other than payments of qualified stated interest). For example, a bond with a maturity value of $1,000 generally would have a $50 premium if you buy it for $1,050.

Basis: In general, your basis for figuring bond premium amortization is the same as your basis for figuring any loss on the sale of the bond. However, you may need to use a different basis for: Convertible bonds, Bonds you got in a trade, and Bonds whose basis has to be determined using the basis of the investor who transferred the bond to you. See Regulations section 1.171-1(e).

Dealers: A dealer in taxable bonds (or anyone who holds them mainly for sale to customers in the ordinary course of a trade or business or who would properly include bonds in inventory at the close of the tax year) cannot claim a deduction for amortizable bond premium. See section 75 of the Internal Revenue Code for the treatment of bond premium by a dealer in tax-exempt bonds.

How To Figure Amortization: For bonds issued after September 27, 1985, you must amortize bond premium using a constant yield method on the basis of the bond's yield to maturity, determined by using the bond's basis and compounding at the close of each accrual period.

Constant Yield Method: Figure the bond premium amortization for each accrual period as follows:

Step 1: Determine your yield. Your yield is the discount rate that, when used in figuring the present value of all remaining payments to be made on the bond (including payments of qualified stated interest), produces an amount equal to your basis in the bond. Figure the yield as of the date you got the bond. It must be constant over the term of the bond and must be figured to at least two decimal places when expressed as a percentage. If you do not know the yield, consult your broker or tax advisor. Databases available to them are likely to show the yield at the date of purchase.

Step 2: Determine the accrual periods. You can choose the accrual periods to use. They may be of any length and may vary in length over the term of the bond, but each accrual period can be no longer than 1 year and each scheduled payment of principal or interest must occur either on the first or the final day of an accrual period. The computation is simplest if accrual periods are the same as the intervals between interest payment dates.

Step 3: Determine the bond premium for the accrual period. To do this, multiply your adjusted acquisition price at the beginning of the accrual period by your yield. Then subtract the result from the qualified stated interest for the period.

Your adjusted acquisition price at the beginning of the first accrual period is the same as your basis. After that, it is your basis decreased by the amount of bond premium amortized for earlier periods and the amount of any payment previously made on the bond other than a payment of qualified stated interest.

Example: On February 3, 2014, you bought a taxable bond for $110,000. The bond has a stated principal amount of $100,000, payable at maturity on February 3, 2021, making your premium $10,000 ($110,000 - $100,000). The bond pays qualified stated interest of $10,000 on February 3 of each year. Your yield is 8.07439% compounded annually. You choose to use annual accrual periods ending on February 3 of each year. To find your bond premium amortization for the accrual period ending on February 3, 2015, you multiply the adjusted acquisition price at the beginning of the period ($110,000) by your yield. When you subtract the result ($8,881.83) from the qualified stated interest for the period ($10,000), you find that your bond premium amortization for the period is $1,118.17.

Special Rules: For special rules that apply to variable rate bonds, inflation-indexed bonds, and bonds that provide for alternative payment schedules or remote or incidental contingencies, see Regulations section 1.171-3. End of the copied IRS text.

The Zero Coupon Bond Accretion Sheet

This sheet is for calculating annual zero coupon bond accretion for a portfolio of up to 25 zero coupon bonds.

One would do that to determine how much in imputed taxes are due on interest that accumulates inside the zero coupon bond, and thus is not actually received. This is sometimes called a "phantom tax" because one pays taxes on something they don't receive.

Start your input with the tax rate you'd pay on bond interest into cell B4.

Then continue your input with cells B7 through B11. Cell B12 shows the estimated yield to maturity. Continue inputting all of the rest of your bonds.

The table shows the compound accreted value, which in English, means the estimated future annual market values. The annual accretion is the amount interest earned, but not received. This is the amount of taxable interest that is added to the current market value of the bond. The taxes column to the right of that are just the annual accretion times the tax rate.

All of the bonds in the portfolio are summed up and displayed in columns CD & CE and in the chart below.

Zero Coupon Municipal Bond Original Issue Discount Accretion Calculator

In order to avoid capital gains taxes on pre-maturity sales of tax-exempt bonds (zero coupon or not) with an OID, the IRS provides that the holder's basis increases (accretes) semi-annually.

This bond calculator determines these accretion amounts using the Constant Yield Method. So the purpose of this is to account for the full OID, so it's all treated as tax-exempt interest and not artificial capital gains upon the sale.

The IRS allows treatment of OID as tax-exempt interest and not capital gains, because the "profit" was the result of actions by the issuer, and not market forces. De minimis rules and alternative minimum taxes are ignored.

There is no such thing as OID on a coupon muni. For municipal bonds, OID is amortized to increase basis (tax-free income).

Market Discount is not amortized, rather it is recognized as income when the bond is sold (pro-rata) or matures (in full). However an election may be made to include market discount as income each year.

Original and Market Premium must be amortized to decrease basis (reduction in tax-free income).

Input the capital gains tax rate into cell B4. This is the rate applied to calculate taxes on the right column of the results.

There are 25 blocks of inputs for each bond. For example, the first bond's inputs are from B7 to B12. There's no protection on column B, so you can copy and paste the whole range (from B4 to B204).

Inputting the bond's name is just for keeping track of where it is on results.

Input the remaining data, and then look at the results on the columns to the right. If it was input correctly, and then the results will display accretion in every year, semi-annually, until it matures.

After there is sufficient input, the constant yield used will display at the bottom of that bond's input area - e.g., C13 for Bond #1.

End of period basis displays on the left column, the amount of period accretion in the middle, and taxes on the right. Basis should equal maturity in the final period, If not, and then there was an input error of some kind made.

When bonds start and stop on the results depend on the first year of all inputted bonds.

After inputting all bonds, the program will determine the earliest year. Then that will be the first year shown in the results.

This is so a wide range of maturities can be input, and everything will all line up correctly, so the summing columns - EQ & ER (which show each year's annual accretion and taxes).

The Horizon Total Return Sheet

This method of calculating a bond's total return is the most accurate. Use this when you want to see a more realistic total rate of return over the life of a bond than just yield to maturity. A longer explanation of Horizon Return is in the text box at the top of the sheet.

This sheet has more detailed instructions on every input cell, so no further directions are needed here. The result is the bond's total return in cell B40. This is what you would compare to yield to maturity, which is not very accurate because of several reasons, like the assumption that cash flows will be reinvested at the same YTM rate (which won't happen).

Input all of the parameters into the green-shaded input cells, and then the results display.

The Multi-year Bond Calculator Sheet

This sheet is helpful in seeing the annual cash flows of a portfolio of up to ten bonds. It sums the bond portfolio's cash flow, calculates the annual present values in column R, and calculates the overall internal rate of return (cell C3).

Input all of the parameters into the green-shaded input cells, and then the results display.

The Single-year Bond Calculator Sheet

This sheet shows the monthly cash flows of a bond portfolio of up to 50 bonds.

Input bond names into column B. Input each bond's market value at the beginning of the year into column C. Then input all of the coupons in dollars received by month. Then input the estimated market value of each bond at the end of the year into column P.

Column R shows the simple yield for each bond from the beginning of the year to the end, considering the coupons received. Column S displays the percentage each bond represents of the portfolio. Cells S55 and S56 shows the current yield of the combined bond portfolio. The chart starting in cell B58 shows the total monthly coupons.

The Rate Calculator Sheet

This sheet isn't really for bonds, but it has something to do with interest rates. Its purpose is to compare the growth of money using five different interest rates.

The first inputs are three compound interest rates, column P&Q are used for changing the interest rate in any year, and column S uses simple interest (and ignores compounding).

Start by inputting the current year into cell D5.

Then input annual deposits into column F and five interest rates into cells J, L, N, P, & S in row 5. Use column Q to manually override the interest rate in cell P5 to any rate in any year.

The results are the end-of-year values

The Compounding Converter Sheet

This sheet is for calculating the one-period interest rates that correspond to the base annual compounded interest rate input into cell C6.

For example, if 10% is input into cell C6, then 4.88% is the semi-annual compounding rate that would translate into a 10% annual rate. You can easily verify this by starting with $1,000, adding 4.88%, which results in $1,048.80, and then add 4.88% to it again yields $1,100. This is the equivalent of $1,000 at 10% annual interest.

The Convertible Bond Sheet

This sheet has nine of the most-commonly-used convertible bond calculators. All of the inputs and result cells have text to explain their purposes.

Ironically, these are used mostly by equity managers.

It does not use Black-Schools, nor any other method.

The Portfolio Yield Calculator Sheet

This sheet is for calculating estimated portfolio yield and how much money the portfolio will yield in several time frames.

Input the investment names into column C, the current market values into column D, and their yields into column G. Then select income payout time periods and month using the drop-down menus in columns E & F.

Then it calculates each investments' percentage of the portfolio, and how much one would expect to receive in each time period. It then shows this information for all investments in the portfolio combined.

The last five investment input areas have their months set up so you can input odd monthly cash flows manually.

Preferred Stock Calculator Sheet

In addition to generic input in columns C through E, when you're inputting the Annual Yield into column G, iterate (keep changing) the Annual Yield until the dollar amount of the Annual Coupon shown in column F is correct. You should know this because this is how much money in cash they send you (or plan to send you) per share.

Also dates need to be input this format: Month / Day / Year, like this, 12/31/2014, not like this, 31/12/2014, nor like this, 12/31/14.

About the Far Right or Left Input Sheet:

Ignore that, it doesn't do anything. It's the start of a huge addition to the program that will be on the back burner for years.

About the Automatic Updater

This is a separate spreadsheet that makes updating to a newer version easy. It's just a macro that copies all of your inputs from the old expired program into the new version. The directions on how to use it are on the updater spreadsheet. So you'll only get this updater spreadsheet after you update.

_________________________________

Please let us know if any of this doesn't make sense, or if you feel more detailed directions should be written. Most people that buy this set of bond calculators are professionals that don't need to read instructions, because they're familiar with bond terminology and the reasons for calculating all of these numbers.

About an up and coming fixed-income asset class for aggressive investors

An informative link about bonds in general is here: http://www.investinginbonds.com/story.asp?id=52

Financial Planning Software Modules For Sale
(are listed below)

Financial Planning Software that's Fully-Integrated
(the IFP is the NaviPlan alternative for 1/6th the price)

Goals-Only "Financial Planning Software"
(the MoneyGuidePro alternative for 1% of their price)

Retirement Planning Software Menu: Something for Everyone
(the RWRs, RP, and SRP)

Comprehensive Asset Allocation Software

Model Portfolio Allocations with Historical Returns

Monthly-updated ETF and Mutual Fund Picks

DIY Investment Portfolio Benchmarking Program

Financial Planning Fact Finders for Financial Planners Gathering Data from Clients

Investment Policy Statement Software (IPS)