Government security Valuation (for an Indian regulatory authority)

Introduction

A government security (G-sec) is a tradable instrument issued by the Central and State governments. It is an acknowledgement of the government’s debt obligation. New Securities are issued through an auction process conducted by RBI (Indian Banking Regulatory Authority) which happens on a weekly basis. Interest is usually calculated on a half yearly basis and the principal is repaid on maturity.

Problem

One of our clients is a regulatory authority regulating the dealings of fixed income instruments. The client was finding it challenging to issue timely and accurate valuation of securities daily. Since the valuation methodology followed is very complex, the manual calculation is prone to errors. SheetKraft had already automated the valuation of Corporate Bonds for the client and so they approached us to automate this as well.

Valuation Methodology

• Every day a dump of all the traded and auction securities is made available on the CCIL website from where it is downloaded. These securities are then grouped into Buckets based on their maturity and segregated into two categories on the basis of number of trades.
• Some trades are classified as Outliers (these won't be considered for valuation) based on a list of criteria. Market Yield Movement (MYM) is calculated for the remaining securities.
• Out of the remaining ~3500 active securities, maturing securities are removed from this list on the last working day before their maturity. Auction securities are added to this list. Everyday MYM and Yield calculation is done for all the securities. A methodology is followed for calculating the MYM and Yield for the non-traded securities. There is also a calibration methodology followed to calculate the yields of securities which have not been traded in three months.

All the conditions and criteria used for valuation of the securities change on an Auction day which is once a week. The new securities issued have a base rate which is decided by the RBI. These securities may or may not be traded on the day of their issuance. Different methodologies are followed for each.

Impact of SheetKraft

The main complexity of the process is in the sheer number of criteria for classification of securities, that can lead to a lot of variance in the results. RBI has set some guidelines for trading in the bond market.

• The prices at which the bonds are being traded need to be within a certain range of the previous trading day’s valuation. If the results are not published on time, then the range is fixed based on the available prices (if the current valuation day is 10 January, previous day is 9th but since valuation wasn’t done prices taken will be of 8th). This results in trades happening at the same level for two consecutive days leading to decrease in returns for investors.
• If the results are not accurate, trading happens on the wrong level. This has a huge impact on the returns. It also impairs regulation of trades because it can lead to a false trade being accepted and influencing the market in a negative manner. That is why the results mustbe published for the markets’ use by the end of day and need to be accurate.

Using SheetKraft has reduced the time taken for manually calculating the yields from 2-3 hours per day to under a minute per day. The solution provided is SheetKraft is also error free since it eliminates the possibility that some criteria would have been missed.