New Financial Planner Starter Kit

Professional Investment Portfolio Building Kit

If you're still shopping, or making your first report, the best results are obtained by looking at the bond demo, while following along as you read these directions.

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

If you try to print the demo from Internet Explorer, or you just click Print in Excel, it probably 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.

If you have Excel 2007, then first save the workbook as an xlsx workbook. When you get the info dialog box about "features that are not supported," just click OK.

Basic concept for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets. This data flows through the calculation sheets, which will then 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 (if it's not a presentation sheet). If you could, then 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 at the same time).

If you're seeing this: ####, then either increase your Zoom magnification number (View, Zoom), or make the column width wider (or use the slider bottom right in Excel 2007). 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 back using these original files. Then save files you've worked on using a different file name (and into a different folder). 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.

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

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 may 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 (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 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 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 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.

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 weekly (but it will still expire and need to be updated annually).

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 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 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 '09, and you need to report the portfolio's yield to maturity as of 12/31/08, then input 12/31/08 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.

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), 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, then you 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.

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

Everything is summed up and compared on the  Premium Amortization Results sheet. This sheet has no protection, so you can do anything you want with it.

Input areas for bonds one through three have hidden rows in them, so you won't be able to copy and paste the whole block of input from one bond to another (like you can with bonds four and below). If you do, then you'll get a protection error. So you'll have to copy and paste one cell at a time for bonds one through three.

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 in the current year.

If the bond's purchase price is less than par after returning earned interest to the seller, 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).

Start with Bond #1 in cell B10. Populate ALL of Bond #1's input fields with pertinent data from cell B10 to B26. 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 input. Bonds with incomplete input will be ignored here, and will result in errors to the right.

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

If there's a red Error! message to the left of the Sale Date input field, this is because you input a sale date that is after maturity (so you'll need to fix that or you'll get errors and bad results to the right).

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

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 Straight Line. 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.

Everything sums up at the bottom of each sheet, and the fifth sheet to the right displays totals for all 100 bonds. This is the only sheet that is not protected, so this is where you make notes, graphs, and can format results any way you want to.

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 2009, then just input it as 2010. 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 from IRS Pub 550 is here (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 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 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 in cell E4 by entering the current year. Next, input 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 is 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.

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

Sorry, but there's not enough demand to make it price options using Black-Schools, not 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.

_________________________________

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