Bond Calculator Directions.

If you're still shopping, or making your first report, the best evaluation results are obtained by looking at the bond demo, while following along as you read the 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. 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.

Basic input for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets. This will populate the presentation sheets, 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.

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

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.

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.

You can copy the input sheet to the unprotected presentation 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).

Read about financial planning software support here.

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

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

Bond Calculator Directions

The Two 100-Bond Portfolio Sheets

Step 1: After you have unzipped Bond Calculators.zip and turned it into Bond Calculators.xls, 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 if 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. The 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 D35. 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.

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 cell G14 using the 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 that one. This is useful when contemplating buying or selling an individual bond. You can input the bond, then turn if on and off, so you can compare portfolio characteristics and how that bond impacts the 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 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, as 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).

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 is $999, then the bond's price is 99.90 in decimal format. If interest 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 wants to default to the 20th 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/10 Excel will think that date is 30 June 1910. So you'll need to input 6/30/2010 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 (G19 & G20) 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 in 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. 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? Use the sheet to the right, Duration & Convexity to Call.

All of the input from the Portfolio Duration & Convexity sheet are referenced into this sheet. The only input cells that are active are the switch 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.

Step 4: If you to want calculate these parameters from a valuation date other than today, then input that date into cell G10. 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 '08, and you need to report the portfolio's duration as of 12/31/07, then input 12/31/2007 into cell G10.

A tip is to use Excel's "Freeze Panes" when working with the 100-Bond 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 calculations 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 G10. 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 '08, and you need to report the portfolio's duration as of 12/31/07, then input 12/31/2007 into cell G10.

The Price | Rates | Yield Calcs Sheet

This sheet has the popular yield to maturity and yield to call calculator (which is the only part of these bond calculators that has a free calculator as part of the demo. In other words, this is the only thing that works on the demo). This free demo function expires weekly. You can either download it again or buy it to keep it from expiring.

Delete all of the sample input in the green-shaded cells from B5 to B11 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 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 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 sheet to the right.

The next input, Maturity Date, has an Excel quirk. It wants to default to the 20th 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/10 Excel will think that date is 30 June 1910. So you'll need to input 6/30/2010 instead. If you're getting DIV#0 errors, this is probably where they're coming from.

If you to want calculate these parameters from a valuation date other than today, then input that date into cell B8. 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 '07, and you need to report the portfolio's yield to maturity as of 12/31/06, then input 12/31/06 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 B8 & B9.

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

Input the amount of bond maturity proceeds into cell B11.

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

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 Discount or Premium Sheet

This sheet calculates the (annual) figures for: Accrued interest, net bond basis, original discount or premium, constant yield as required by the IRS, annual (pro-rated) amortization of bond premium or discount, basis, and finally sale capital gain or loss.

Yes, this is for municipal bonds too.

Input the current year into cell B4.

Input the first day of the current year into cell B5. For example, if it's currently 2008, input 1/1/2008.

Input your capital gains tax bracket into cell B5.

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

Start with Bond #1 in cell B9. Populate ALL of Bond #1's input fields with pertinent data from cell B9 to B25. You'll know when you've input valid data in ALL of the input fields, when the number of bonds shown in cell C6 is equal to the number of bonds you input.

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

Repeat the input process for up to 25 bonds.

Everything sums up in row 57.

If you want to go back in time, then input the previous year into cell B4, and the first day of that year into cell B5.

More information on calculation methodology from IRS Pub 550 is here:

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 person 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, 2004, you bought a taxable bond for $110,000. The bond has a stated principal amount of $100,000, payable at maturity on February 3, 2011, 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, 2005, 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.

The Zero Coupon Bond Accretion Sheet

This sheet is for calculating annual zero coupon bond accretion for a portfolio of up to ten 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 in cell E4 by entering the current year. Next, input the tax rate you'd pay on bond interest into cell G36.

Then continue your input with cells B5 through B8. Cell B9 shows the estimated yield to maturity. Continue inputting all of the rest of your bonds.

The table shows the compound accreted value, which is 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 is just the annual accretion times the tax rate.

All of the bonds in the portfolio are summed up and displayed in columns AK - AN and in the chart below.

The Total Return Sheet

This method of computing a bond's total return is the most accurate ever created. Use this when you want to see a more realistic total rate of return over the life of a bond than yield to maturity. A longer explanation of Horizon Return is in the text box at the top of the sheet. The 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 cash flows being reinvested at the same YTM rate.

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

The Multi-Year Bond Calc 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 C2).

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

The Single Year Bond Calc 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 Calc 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. You input three are the usual 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.

The Portfolio Yield Calc Sheet

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

Input the asset names into column B, the current market values into column C, and their yields into column F.

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

_________________________________

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.

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

Product Information

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

Financial Plan Tools

Family Budgeting Calculator

Financial Planning Fact Finders

Investment Policy Statement Software

Investment Software for Comparing 22 Methods of Investing

Buy Term Life Insurance and Invest the Difference in Mutual Funds vs. Whole Life Insurance Calculator