Instructions for Use of Microsoft Excel's Bond Price Calculator
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.
INSTALLING THE ANALYSIS TOOLPAK
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
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.
III. CALCULATING THE DIRTY
PRICE FOR BIDDING
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:
interest = (c / 2)*(DSLCD / DICR)
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%;
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.
is 182.5, which is the length of a full coupon period (365/2).
is now a simple matter to calculate the dirty price you need to bid in the
Price = Clean price (obtained from Excel’s Price function) + Accrued interest
(as calculated from the formula above)