This Excel spreadsheet is a comprehensive array of bond calculators that helps make bond (and bond fund) portfolio management quicker, easier, and 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.

You'll receive all of the following sheets in one spreadsheet when you buy it.

Functions These Bond Calculators Perform:

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, 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 click, you can deselect a single bond, so all of its input is ignored. Then bring it back with one click.

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

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.

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.

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.

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

Calculate Annual Amortization of Premium: Calculate 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 goes to zero, basis is par, and your capital loss is also 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 show 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

This fixed-income calculator allows you to calculate premium amortization for more than one bond at a time. Bloomberg only does one bond at a time.

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

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.

Zero Coupon Municipal Bond OID Accretion Calculator: Calculate the annual Original Issue Discount accretion for up to 25 zero coupon municipal bonds at once. It compounds semi-annually, it automatically organizes all inputted bonds according to acquisition year, all 25 bonds are totaled annually, and it has a 50-year window so it will handle a wide spread of maturities.

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.

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

Bond Total Return 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.

Multi-year Bond Investment Calculator: Input up to ten bonds and see an overall portfolio yield (IRR) up to 50 years.

Single-year Bond Investing Software: Input up to 50 bonds and get a one-year overall portfolio yield (IRR).

Basic Interest Rate Calculator: Calculate differences in interest rates given several different compounding periods. It has two graphs.

Fixed Income Calculator: 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.

Convertible Bond Calculators: An array of the usual bond calculators.

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.

Preferred Stock Calculator: 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.

The free thirty-day trial period is available for this bond calculator software

To download the demo, right click on the link below, and 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.

Be sure to see the sheet that shows what colors should look like.

Download the bond software's free demo

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.

It has the automatic updater spreadsheet, so it's only a few clicks to update from the old to new version (so there's no manual copying and pasting of inputs required, it's all automatic).

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)

Life Insurance Calculator (AKA Capital Needs Analysis Software)

Investment Software for Comparing the 27 Most Popular Methods of Investing