Financial Markets
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 threestep procedure they need to
follow. We hope that this will enable investors to price Tbonds 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 AddIns. 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 AddIns 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 AddIns 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
ddmmmyyyy format (like 28Feb2002).
Maturity: This is the maturity or
redemption date for the bond. Make sure you input the date in ddmmmyyyy format
(like 28Feb2002).
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 Tbonds 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 investorspecific
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 Tbonds prospectus for
details). For Tbonds 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, reopenings 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 semiannual 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 28Feb2002 is
purchased on 21Mar2002, DSLCD will be (21Mar2002 less 28Feb2002) = 21
days. But if the same bond is purchased on 21Dec2002, the relevant period
will be the number of days elapsed since the most recent coupon payment, i.e.
28Aug2002. Thus DSLCD will equal (21Dec2002 less 28Aug2002) = 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)
