Bond Calculators for Portfolio Management.

This Excel spreadsheet is a comprehensive array of bond calculators that helps make bond (and bond fund) portfolio management quicker, easier, and a whole lot cheaper

This bond software does not have a database with bond data. So you can't input CUSIPs and have individual bond data come up. You'll have to get the data yourself and input them manually, or via online download.

Customers' say they had to spend $250 to get YieldBook or Bloomberg to run these numbers just once. Now you can do it yourself, as many times as you need, for less than a penny on their dollar, without the hassle, any time, in minutes.

You get all of the following sheets in one spreadsheet when you buy it.

What these Bond Calculators Do:

Bond Calculator Sheet 1: 100-bond portfolio duration and convexity calculator. Calculate all of the usual Macaulay / modified duration and convexity numbers, and see how assumed changes in interest rates change the price of each bond, and the combined portfolio of up to 100 bonds. It calculates:

• Macaulay and modified duration and convexity of each bond, and the whole bond portfolio combined (both weighted using market values, and unweighted). It calculates effective duration calculator and modified duration calculator via Macaulay duration.

• Estimated percentage, and current market price changes in dollars, due to both modified duration and convexity, separately, and then both of them combined. All at the individual bond level, and at the overall bond portfolio level.

• The bond portfolio's unweighted average credit quality rating, and then weighted average credit quality rating, using both Moodys and S&P credit rating schedules.

• The average inputted yield to maturity of the bond portfolio.

• Average maturity, and weighted average maturity, of the combined bond portfolio.

• Average current market price of all of the bonds entered (input in percentage of par format).

• Average coupon of all of the bonds entered.

• Average bond portfolio yield and weighted average bond portfolio yield.

• Total number of bonds included in the portfolio.

• Total current market value of all of the bonds included in the portfolio.

• Total maturity value of all of the bonds included in the portfolio.

• Each bond's percentage make up of the total bond portfolio.

• Each bond's estimated average total return over its life.

• Excel's IRR formula is used to estimate amortized premiums and discounts.

• How many coupons it takes to recoup premium.

• Each bond's cash flows over the whole thirty-year window. This is great for analyzing laddered bond portfolios.

• Year-by-year total cash flows from all of the bonds combined for all thirty years. Also shown are the cash flows from just the coupons, and just the maturity proceeds. All are also summed so you can see the total amount of net inflows into the portfolio each year over the thirty-year window.

• You can turn individual bonds on or off with one switch to simulate adding or deleting them from the portfolio. With one keystroke, you can deselect a single bond, so all of its input is ignored. Then bring it back with one keystroke.

• It calculates days to maturity (or call on the next sheet) automatically (because it uses your computer's current date as a reference). It automatically determines the numbers of days until maturity when you input the maturity date.

• You can input an alternative valuation date, if other than today. For example, if you need to calculate duration as part of year-end reporting in January, and the bond's valuation date needs to be the last day of the previous year, you can do that.

Bond Calculators Sheet 2: 100-bond portfolio duration and convexity to call calculator. This is the same as the 100-bond calculators sheet described above, but it calculates duration to call, and convexity to call, numbers based on data input into the regular 100-bond calculator sheet. Just input the call dates and proceeds and you can see worst-case call values.

Bond Calculator Sheet 3: Single-bond duration calculator and bond convexity calculator: Easily calculate all of the usual duration and convexity numbers, and see how changes in interest rates change the price of a bond in greater detail. You can input an alternative valuation date, if other than today. For example, if you need to calculate duration as part of year-end reporting in January, and the bond's valuation date needs to be the last day of the previous year, you can do that.

Bond Calculators Sheet 4: Bond yield calculator - Determine before- and after-tax bond yield to maturity (or bond yield to call) down to a very high level of accuracy (third decimal place). The YTM calculator works with interest rates from 1% to 20%.

• You can input an alternative valuation date, if other than today. For example, if you need to calculate yield to maturity as part of year-end reporting in January, and the bond's valuation date needs to be the last day of the previous year, you can do that.

• Calculate a coupon bond's current fair market value, and total return considering the reinvestment of coupons.

• Calculate a zero coupon bond's current fair market value and bond yield to maturity (or bond yield to call).

• Calculate a zero coupon bond's accretion (annual inputted tax liability).

• Muni vs. taxable bond breakeven rate calculator.

• T-bill yield and/or T-bill current market price finder.

• Bond equivalent yield - used to turn the 360-day T-bill rate into the 365-day rate.

• Simple current yield calculator.

• CD equivalent yield (AKA money market equivalent yield - used to compare T-bills to 360-day money market instruments).

Bond Calculator Sheets 5 - 12: Calculate annual amortization of premium and see basis on up to 100 coupon bonds.

The concept is basically this: You'll have a capital loss on bonds if bought at premium and held to maturity. So the IRS allows you to take this locked-in loss, divide it into annual chunks, and then use the loss on each chunk to offset taxes due on coupon interest along the way (these annual chunks are calculated using two methods, but they both end up with the whole premium being amortized at maturity. There may be tax advantages in using one method over another if sold before maturity, and this will calculate and show that).

The total capital loss ends up being the same, just not as painful when it's taken in chunks annually. So at maturity, premium should go to zero, basis should be par, and your capital loss therefore should also be zero.

Discounts are not amortized (although people have bought this to amortize discounts of distressed mortgage portfolios using the constant yield method. You basically reverse the two inputs so it will see the discount as premium. This world is too complex to say that this works from an IRS point of view, but people have used it for this to get into the ballpark).

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 get help to figure this out.

These sheets calculate the (annual) figures for:

• Accrued interest that needs to be returned to the seller after settlement
• Net bond basis
• Original discount or premium
• Annual (pro-rated) amortization of bond premium using both Constant Yield and Straight Line amortization, as required by the IRS
• End-of-year basis
• Annual coupons
• Estimates of taxes due on coupons
• Estimates of differences in taxes paid vs. not amortizing premiums
• Capital loss or gain upon sale before maturity

As far as we know, this is the only bond calculator that allows you to calculate premium amortization for more than one bond at a time. Bloomberg only does one bond at a time, and we haven't seen any other competing product that even makes these calculations at all.

Because of the complexity, there are eight sheets: Four input and calculation sheets using 25 bonds each. Four calculates the Constant Yield method and another four uses Straight Line, so you can make comparisons.

It first strips off any accrued interest the buyer has to refund to the seller. Then it displays all of the annual amortization, interest, basis, and taxes for every year to either sale or maturity, up to 20 years. Then it pro-rates everything given purchase and sale / maturity dates. It keeps track of basis and the amount of capital loss if sold before maturity. It works with any type of coupon bond, including municipals. Zero coupon bonds use a process called accretion, and is calculated on sheet 13 (not on sheets 5 - 12).

All 100 bonds are totally independent of each other.

You can assign a different tax rate to the four different input / calculation sheets (with 25 bonds each).

You can choose to sell bonds before maturity to see what the numbers look like, including basis and capital loss.

You can "go back in time" by inputting current dates with the same data as what actually happened (see the directions).

Bond Calculator Sheet 13: Zero coupon bond accretion calculator. Input up to 25 zero coupon bonds, and see the annual accretion / basis, and taxes due, on all 25 bonds together and all individual bonds, up to 50 years. There's a graph to show all of the accretion and taxes combined. It works with any type of coupon bond, including municipals.

Bond Calculators Sheet 14: Bond yield calculator - This calculates bond total return using the most accurate method - Horizon Return. Calculate total return considering everything including controlling the compounding of reinvested coupon interest and capital gain / loss when sold. Compare bond prices and yields in great detail before purchasing or selling them.

Bond Calculator Sheet 15: Multi-year bond investment calculator. Input up to ten bonds and see an overall portfolio yield (IRR) up to 50 years.

Bond Calculators Sheet 16: Single-year bond investing software. Input up to 50 bonds and get a one-year overall portfolio yield (IRR).

Bond Calculator Sheet 17: Calculate differences in interest rates given several different compounding periods. It has two graphs.

Bond Calculators Sheet 18: Input and compare, on the same page, the growth (or shrinkage) of money given the three compound interest rates of return. It has one column for simple interest; one compound interest column has a year-by-year manual override (so you can have a different rate of return every year), and a graph sheet. You can also add new money to the picture every year.

Bond Calculator Sheet 19: An array of the usual convertible bond calculators.

Bond Calculators Sheet 20: Bond yield calculator / Portfolio Yield Calculator - This fixed income software calculates 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, semi-annual, and annual basis; how much as a percent each asset is of the total portfolio; and how much each security is estimated to pay out on a daily, weekly, monthly, semi-annual, and annual basis.

You just enter five data points (asset name, dollar amount of it held, when it pays, and its annualized estimated yield - get it from the fund's prospectus, website, other source, or guess) into the green-shaded areas, and it automatically calculates all of the income generated in all of the different time frames (along with all of the totals). You can enter up to 100 investments.

Customers have said they've found this sheet to be sufficient to model bond ladders and bond laddering strategies.

Bond Calculators Sheet 21: Preferred stock yield to maturity, and yield to call, calculator. Will handle yields from 2% to 35%, and is accurate to the third decimal place.

To download the demo, right click on the link below, then choose "Save (Target) As..." to save to your hard drive. Then find and open with Excel.
Answers to frequently asked demo questions and how to use demos.

This bond software's free demo has the most functional and accurate free bond yield to maturity calculator on the web!

Only the bond yield to maturity calculator part of the spreadsheet has its formulas intact. The rest of "demo" is non-functional. So you won't be able to do anything but look at it. You'll receive a functional bond calculator spreadsheet after purchase.

It helps to read the directions while you look at the bond software demo.

Please send e-mail if you'd like to suggest new features. If you have a good idea, you may get it for free.

This is one of the few personal finance modules that has a "trial period"

Personal Finance Software Modules For Sale
(are listed below)

Fully-Integrated Financial Planning Software

Menu of Retirement Planning Software

Asset Allocation Overview with Historical Portfolio Performance

Comprehensive Asset Allocation Calculator

Asset Allocation Models with Investment Track Record

Monthly Updated Mutual Fund Picks

TVM Financial Tools

Free Personal Budget Software