Instructions for Use of Microsoft Excel's Bond Price Calculator

Microsoft Excel has an inbuilt bond price calculator that can be used to price any type of fixed coupon bond, including the Treasury bonds issued by GOT. To help educate bond investors on how they can make use of this calculator on their own Excel software, we have identified the simple three-step procedure they need to follow. We hope that this will enable investors to price T-bonds at their own comfort and convenience on their own personal computers.

  STEP I.      INSTALLING THE ANALYSIS TOOLPAK

If you do not have the Analysis Toolpak installed on your Microsoft Excel program, you cannot use Excel’s bond price calculator. To check whether or not the Toolpak is installed, open Excel and go to the Tools menu and choose Add-Ins. If the Analysis Toolpak box is checked, it means you already have the Toolpak installed. If it is not checked, check it and click OK. (You can also install the other Add-Ins like “Solver” etc.). If the program prompts you to insert the CD for Microsoft Office, you will have to locate the CD and proceed with the installation. If the Add-Ins are already stored on your hard disk, Excel will automatically load them from there. Once the Toolpak is installed, you can move to II below.

STEP II. CALCULATING THE CLEAN PRICE

1. Open your EXCEL programme for Windows.2. Go to the “Insert” menu and choose the “function fx” bar.

3. Go to the “Function Category” and select “Financial”. The relevant “Function” names will appear.

4. Scroll down with your mouse to “PRICE” and double click on it. A price screen menu will appear comprising of the following seven items: “Settlement”, “Maturity”, “Rate”, “Yield,” “Redemption”, “Frequency” and “Basis”. Note that you will have to scroll down to view the “Frequency” and “Basis” items.

5. Key in values for these items according to the following guidelines:

Settlement: This is the date on which the bond was (is to be) purchased by the buyer. Thus if the bond is purchased in a BOT auction, this will be the T+1 settlement date after the auction. If, however, the bond is purchased in the secondary market, it will be the settlement date for that transaction. Make sure you input the date in dd-mmm-yyyy format (like 28-Feb-2002).

Maturity: This is the maturity or redemption date for the bond. Make sure you input the date in dd-mmm-yyyy format (like 28-Feb-2002). 

Rate: This is the annual coupon rate on the bond. Enter in percentage, so that if the coupon rate is 7%, enter 7% or 0.07. 

Yield: This is the annual compounded rate of return the investor wants to earn over the remaining life of the bond. Enter in percentage, so that if the required return is 6.5%, enter 6.5% or 0.065. 

Redemption: This is par value for the bond, which is 100. 

Frequency: This is the number of coupon payments per year on the bond. Enter 2. 

Basis: This is the day count basis used for the compounding of interest. The day count basis used by BOT for T-bonds is Actual/365, which is equivalent to a basis 3. So enter 3. 

Click OK after inputting all these values, and the bond price will appear in cell A1. This is the price consistent with the particulars you entered for the bond. You can change these, especially the yield, which is an investor-specific parameter, to study the change in the bond price.

STEP III.  CALCULATING THE DIRTY PRICE FOR BIDDING

Note that the price you calculate using EXCEL is a clean price. However, what is needed for bidding in Treasury bond auctions is the dirty price, which is basically “the clean price + accrued interest” (see T-bonds prospectus for details). For T-bonds whose issue dates and settlement dates are the same (i.e. primary or parent bond issues), there is no accrued interest and therefore the dirty and clean prices align. But for tranched issues, re-openings or secondary market purchases, the dirty price is higher than the clean price by the amount of the accrued interest. To calculate the dirty price, we need to calculate the accrued interest, as follows:

 Accrued interest = (c / 2)*(DSLCD / DICR)

 where: 

“c” is the semi-annual coupon payment on TZS 100 worth of bonds. Thus if the coupon rate is 7%, c/2 = (.07*100)/2 = 0.035 or 3.5%;

 “DSLCD” is the number of days that have passed since the issue date or the most recent coupon payment, which ever is later. So if a bond with issue date 28-Feb-2002 is purchased on 21-Mar-2002, DSLCD will be (21-Mar-2002 less 28-Feb-2002) = 21 days. But if the same bond is purchased on 21-Dec-2002, the relevant period will be the number of days elapsed since the most recent coupon payment, i.e. 28-Aug-2002. Thus DSLCD will equal (21-Dec-2002 less 28-Aug-2002) = 115 days.

 “DICR” is 182.5, which is the length of a full coupon period (365/2).  

____________________

 It is now a simple matter to calculate the dirty price you need to bid in the auction.

 Dirty Price = Clean price (obtained from Excel’s Price function) + Accrued interest (as calculated from the formula above)