Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Calculating Interest Rates Formula - Excel

VIEWS: 58 PAGES: 1309

Calculating Interest Rates Formula document sample

More Info
									                                                                  YIELD-X
                                                               BOND VALUATION

                                      Bond Code                     R186   3
                                      Bond Yield to Maturity            7.000%
                                      Valuation Date                   5-Nov-09

                                      Bond All-in-Price (par 100)      138.653410
                                                                                     Calculate
                                      Settlement Date                 10-Nov-09
                                      Rand per Point                     -1,228.19




Excel pricing software developed by
Financial Chaos Theory
http://www.quantonline.co.za
ON




     Calculate
                                                                      YIELD-X
                                                                    BOND FUTURE

                                      Bond Code                        R186   3
                                      Bond Yield to Maturity               8.000%
                                      Valuation Date                     10-Nov-10
                                      Future Expiry Date                 7-May-09
                                      Risk Free Interest (simple)         10.356%



                                      Bond Futures Yield                 7.91909%      Calculate
                                      Bond Futures Price (par 100)        128.368450

Excel pricing software developed by
Financial Chaos Theory
http://www.quantonline.co.za
Calculate
                                                           YIELD-X
                                              EUROPEAN OPTIONS ON BOND FUTURES

                                      Bond Code                     R186   3
                                      Bond Futures YTM                7.00000%
                                      Strike Yield to Maturity        9.00000%
                                      Valuation Date                  14-Jan-09
                                      Future Expiry Date              5-Nov-09
                                      Risk Free Interest (simple)      0.000%
                                      Volatility (annual)              27.050%
                                      Call or Put (C/P)                   p

                                      Bond Futures Option Price           4,427.21
Excel pricing software developed by   Delta                            -20.64%
Financial Chaos Theory
http://www.quantonline.co.za
D-X
ON BOND FUTURES
                                     NOTES and PROCEDURES

Input Parameters

ALL yields or yields to maturity (YTMs) and interest rates should be in percentage format e.g. 7.75% or 0
If you use the DDE links to the YieldX trading system, divide the yields by 100.
Dates should be in SA date format e.g. dd/mm/yy or dd-mmm-yyyy

Calculating

Choose the bond by clicking on the drop-down arrow and moving the cursor to chosen bond. Click the rig
Update the dates, YTMs, interest rate or volatility by changing the relevant cells
Click the button called "Calculate". This will run the software and calculate the answers
Answers are displayed in the yellow highlighted cells

Protection

All pages are protected.
Grey cells are used as inputs to market data - these cells can be changed and updated
Yellow cells are used for output - these can be changed but will be overwritten with every calculation
All other cells are protected

Public Holidays

This version of the bond calculator does not take any public holidays into account.
The settlement dates might thus be wrong if they fall on public holidays.
Get around this by shifting the valuation or expiry dates.
The pricers for the jFras and jNotes, however, do take public holidays into account.
Update the public holidays on the YieldX Yield Curve worksheet in range H2:H16.

TRI calculations

The weights of the bonds in the Govi index are specified by BEASSA on a quarterly basis.
This version of the software incorporates the weights for the period between 4 Aug 2005 and 3 Nov 2005
Contact Yield-X for updated software after 3 November 2005.

YieldX Yield Curve

YieldX publishes their yield curve daily on their web site at http://www.yieldx.co.za
Download the yield curve (in an Excel file) and copy and paste the data into columns A:E of the "Yieldx Y
Please ensure that it is copied exactly as downloaded and that the first date is in row 3 or in cell A3
NB: it is imperative to use the correct yield curve when pricing FRAs and Jnotes.

Common Setup Problems

Excel can be set to simplify ones yield and interest rate inputs. If one wants to enter just the number 8.8
for instance, instead of 0.0885 in any cell that is formatted to take percentages, do the following: in Exc
on the menu item Tools. In the drop down menu, click on Options. This brings up the Options dialog box
Click the Edit tab. Ensure that the “Enable automatic percent entry” box is ticked. Click OK.
 be in percentage format e.g. 7.75% or 0.075




g the cursor to chosen bond. Click the right mouse button.

d calculate the answers




 changed and updated
be overwritten with every calculation




days into account.


 days into account.




SSA on a quarterly basis.
 od between 4 Aug 2005 and 3 Nov 2005.




//www.yieldx.co.za
he data into columns A:E of the "Yieldx Yield Curve" page
he first date is in row 3 or in cell A3
 cing FRAs and Jnotes.



f one wants to enter just the number 8.85,
ke percentages, do the following: in Excel click
 s. This brings up the Options dialog box.
 ry” box is ticked. Click OK.
USER NOTES on Excel SETUP

Excel 2002/XP/2003

Some of the calculators used in this workbook are written in VBA
Before any calculation can be undertaken, the following needs to be done
Step 1: Add-Ins
Add-in programs are small modules that extend Excel's power by giving you access to an array of features and cal
To access the required Add-Ins, open Excel. Click on the menu item Tools. In the drop down menu, click on Add-I
Ensure that there are ticks in front of the following add-ins as shown
              Ø Analysis Toolpak
              Ø Analysis Toolpak – VBA
              Ø Conditional Sum Wizard
              Ø Lookup Wizard
              Ø Solver Add-In
Click OK .




If some of the above add-ins are not available, it might be that you only operate off a partial MSOffice installation.
Either do a full installation or contact your IT department to upgrade your installation to a full MSOffice installation.
You might be prompted to insert the MSOfffice CD. Do this or contact your IT department.

Step 2: Security
Open Excel. Click on the menu item Tools. In the drop down menu, move your curser to Macro. This brings up a s
Click on Security…
This brings up the Security dialog box. Click on the Security Level tab.
Ensure that Medium is selected.
Click OK



Excel pricing software developed by
Financial Chaos Theory
http://www.quantonline.co.za
 array of features and calculating functions not otherwise offered in Excel.
own menu, click on Add-Ins . This brings up the Add-Ins dialog box.




 al MSOffice installation.
full MSOffice installation.




Macro. This brings up a sub-menu.
USER NOTES on Excel SETUP                                                                                                                                            Excel pricing software developed by
                                                                                                                                                                     Financial Chaos Theory
Excel 2007                                                                                                                                                           http://www.quantonline.co.za


If you battle to convert from MS Office 2003 to 2007, see http://office.microsoft.com/en-us/help/HA101491511033.aspx
Some of the calculators used in this workbook are written in VBA
Before any calculation can be undertaken, the following needs to be done

Step 1: Add-Ins
Add-in programs are small modules that extend Excel's power by giving you access to an array of features and calculating functions not otherwise offered in Excel.
To access the required Add-Ins, open Excel. Click on the menu item Tools. In the drop down menu, click on Add-Ins . This brings up the Add-Ins dialog box.

When you first install Excel 2007, the built-in add-in programs included with Excel are not loaded and therefore are not yet ready to use.
To load any or all of these built-in add-in programs, you follow these steps:

1. Click the Office Button,         then click Excel Options or press Alt+FI to open the Excel Options dialog box and then click the Add-Ins tab.

The Add-Ins tab lists all the name, location, and type of the add-ins to which you have access.

2. Click the Go button while Excel Add-Ins is selected in the Manage drop-down list box.

Excel opens the Add-Ins dialog box showing all the names of the built-in add-in programs you can load.

3. Click the check boxes for each add-in program that you want loaded in the Add-Ins Available list box.
Ensure that there are ticks in front of the following add-ins as shown
                   Ø Analysis Toolpak
                   Ø Analysis Toolpak – VBA
                   Ø Conditional Sum Wizard
                   Ø Lookup Wizard
                   Ø Solver Add-In

4. Click the OK button to close the Add-Ins dialog box.

An alert dialog box appears, asking you if you want to install each selected add-in.

5. Click the OK button in each alert dialog box to install its add-in.

After activating add-ins in this manner, Excel automatically places command buttons for the add-ins in either an Analysis group on the
Ribbon's Data tab or in a Solutions group on the Formulas tab, depending upon the type of add-in. For example, when you activate the
Analysis ToolPak or Solver Add-in, Excel places their command buttons in the Analysis group on the Data tab. When you activate the
Conditional Sum Wizard, Euro Currency Tools, or Lookup Wizard add-ins, Excel places their command buttons in the Solutions group on the
Formulas tab.

If some of the above add-ins are not available, it might be that you only operate off a partial MSOffice installation.
Either do a full installation or contact your IT department to upgrade your installation to a full MSOffice installation.
You might be prompted to insert the MSOfffice CD. Do this or contact your IT department.

Step 2: Security
Excel's security settings needs to be updated enable all macros.
The document at http://www.ksde.org/LinkClick.aspx?fileticket=BhomGawziW8=&tabid=1844
gives an excellent step by step description on how to achieve this.

Quick instructions: You can change the macro security settings in the Trust Center (Microsoft Office Button      , Excel
Options button, Trust Center category, Trust Center Settings button, Macro Settings category. Click beside the last
option of Enable all Macros. This setting can be changed back to Disable all macros with notification after completing
budget. Click on OK, and then click on OK again.) However, if you work in an organization, your system administrator
might have changed the default settings and prevented you from changing the settings. Check with system administrator
if this is the case.

								
To top