User Manual: Government Debt Data Preparation for the MTDS Analytical Tool
A. Introduction
The objective of the data preparation manual is to assist in preparing the debt data for use in the Medium-Term Debt Management Strategy Analytical Tool (MTDS AT). This would normally take the form of manipulating the loan by loan data extracted from the debt recording system to a format that conforms to the MTDS AT input requirement. This involves aggregation of the loan by loan information into up to 15 representative debt instruments. A database with too many individual loans is not conducive to broad policy analysis. Aggregation will facilitate the analyst to better understand the main drivers of cost and risk of the debt portfolio.
The MTDS exercise is focused on analyzing the risks (exchange, interest rate and refinancing) of the total debt portfolio. In this respect we are not interested in how individual loans react to these risks, but in the reaction of the overall portfolio. We are also interested in simulating funding strategies. Rather than evaluating a change in any individual loan, we are interested in examining the interactions arising from different portfolio mix. Therefore, all loans that have similar characteristics – foreign currency denominated loans versus domestic currency loans, short term versus long term maturities, commercial versus concessional interest rate loans can be grouped together.
One of the key inputs to the MTDS AT is accurate data on the total amount of debt outstanding and detailed information on the terms and conditions (e.g. currency of repayment, maturity, interest rate and instrument type) of the debt outstanding. With this information, cash flow projections for interest payment and principal repayment can be generated.
This manual assumes that the accuracy of the database has been examined, although the process described below will also help to identify gaps and errors in the database.[1]
In this Manual we will discuss (i) what type of data to use for the MTDS purpose and (ii) how to prepare this data for the MTDS analytical tool. This Manual should be used in combination with the excel file ‘MTDS Data Prep. File’. The excel file should help to visualize how the file should be set up. The Manual will make references to specific cells and formulas that will correspond to the excel file. It should be noted that the debt data set utilized in MTDS Data Prep.File is fictitious, and therefore some details that would otherwise be available from debt recording systems are missing.
What type of data to use?
v Use loan by loan debt data from the existing data recording system (e.g. DeMFAS, CS-DRMS or in-house database) in original currency of repayment. Country can work with the latest available fiscal or calendar year end of period data.
v Use data on debt which is disbursed and outstanding (the DOD). This does exclude contracted amounts of debt that are still in pipeline (undisbursed). Undisbursed debt in the pipeline is not part of the existing liability; those are future sources of financing.
v Data should include all terms and conditions of existing debt. As a minimum, data should include – creditor’s name, loan ID number, contract currency,[2] the first and the last date of repayment, grace period, interest rate and type (fix or floating), for floating interest rate loans – a reference rate and a margin have to be separated, the total amount of debt outstanding at the end of a period, and end of period exchange rate. In terms of features of each loan, it is recommended that as much detail as possible is extracted from the system.[3]
v Loan by loan cash flow information on principal repayment and interest payment until the final maturity year should be included.
v Minimum coverage – central government external and domestic debt.
B. How to Prepare Debt Data for the MTDS Analytical Tool?
Step 1: Preparing an Excel Workbook
Download central government external and domestic debt data including the parameters outlined in Section I above from the debt recording system to an excel workbook – and create a worksheet called ‘Debt Original Currency’.
Create a new worksheet called ‘Debt_USD’. In this worksheet you will convert all external debt cash flows calculated in the ‘Debt Original Currency’ worksheet into US dollars.[4]
Create a new worksheet called ‘Representative Instruments_FX’. In this worksheet you will save information on user defined representative instruments, their codes, as well as on foreign currency exchange rates.
Create a new worksheet called ‘Aggregation’. In this worksheet you will summarize cash flows in representative instruments.
Create a new worksheet called ‘Working Sheet’. In this worksheet you will perform all intermediate/preparatory steps for the data aggregation.
Step 2: Assigning each Creditor / Loan to a ‘Representative Instrument’
Go to the worksheet ‘Debt Original Currency’ and copy the column that includes names of all existing creditors (in our example cells C4:C44). Do not make any changes in this list; keep the names of the creditors as they appear in your database. Paste this information in the worksheet ‘Working Sheet’. (See Step 1 in the excel file’s ‘Working Sheet’). Use excel’s function ‘Remove Duplicates’ to create a column that will list all existing creditors by the original creditor name once.
Analyze your existing creditors; determine which loans carry similar terms and conditions. Terms and conditions do not have to be identical but they should be similar enough to group loans under the same representative instrument. As an example, the International Development Association (or IDA) and the International Fund for Agricultural Development (IFAD) and the European Development Fund (EDF) offer loans with long maturities (around 40 years) and highly concessional fixed interest rates (1 percent), therefore you might consider grouping these loans together and creating an instrument that will represent all three existing creditors – ‘IDA/IFAD/EDF’.
Loans from the African Development Fund (ADF) have specific repayment profile, for this reason, we have created a separate instrument for the ADF loans called – ‘ADF’.[5]
Following these examples, assign a representative instrument to each of the existing creditor. (See Step 3 in the excel file’s ‘Working Sheet’).
Copy the created table (such as Table 1 below) and paste in the worksheet ‘Representative Instruments_FX’. Go to the worksheet ‘Debt Original Currency’. Insert three new columns -‘Representative Instruments-1’, ‘Representative Instruments-2’, and ‘Instrument Code’. (See columns E, F and G in the excel file’s ‘ Debt Original Currency’ Sheet).
In the first column ‘Representative Instruments-1’ use the VLOOKUP function to assign a representative instrument (from Table 1), to each existing loan / creditor listed in the worksheet ‘Debt Original Currency’.
Table 1: Original Creditors Grouped by ‘Representative Instruments’
Original creditor name / Representative InstrumentInternational Development Association / IDA/IFAD/EDF
European Development Fund / IDA/IFAD/EDF
Int'l Bank for Reconstruction and Devpt. / IBRD/ADB/IDB
ADF / ADF
ADB / IBRD/ADB/IDB
African Development Bank / IBRD/ADB/IDB
African Development Fund / ADF
Export-Import Bank of Korea / Bilateral
IDA / IDA/IFAD/EDF
Int'l Fund For Agricultural Development / IDA/IFAD/EDF
ABC Bank / Commercial Bank
DEF Bank / Commercial Bank
Exim Bank of China / Bilateral
ISLAMIC DEVELOPMENT BANK / IBRD/ADB/IDB
Treasury Bonds_10Y / Treasury Bonds_10Y
Treasury Bonds_2Y / Treasury Bonds_3Y
Treasury Bonds_3Y / Treasury Bonds_3Y
Treasury Bonds_5Y / Treasury Bonds_5Y
Treasury Bonds_7Y / Treasury Bonds_5Y
Treasury Bills / Treasury Bills
In the second column called ‘Representative Instruments-2’ (in the example, excel file column F), combine the new representative instrument with the interest rate type of the loan. Use excel function CONCATENATE to link the representative instrument and the interest rate type (in our example those are columns E and T worksheet ‘Debt Original Currency’).
In the third column - ‘Instrument code’, assign a numerical value to each representative instrument. First, copy the column ‘Representative Instruments-2’ and paste it in the worksheet ‘Working Sheet’. (See Step 4 in the Working Sheet’). Use excel function ‘Remove Duplicates’ to list each instrument once.
Highlight pasted column and go to excel command –‘Data’ in the upper ribbon on the screen; click on it and select the function ‘Remove Duplicates”. If you have a header in the copied column, mark the box – My data has headers. Now execute the function and excel will display information on how many duplicates were removed. (The output should look similar to step 5 in the ‘Working Sheet’).
Now we have representative instruments and the interest rate types attached to them (see column A in Table 2). Some countries may interchangeably use terms ‘fixed’ or ‘amount’ to describe fixed interest rate loans or use ‘variable’, ‘floating’ to describe variable interest rate instruments. If countries have such instruments, for example, ‘IBRD/ADB/IDB_Fixed’ and ‘IBRD/ADB/IDB_Amount’ they should be assigned the same instrument code since both instruments include the same creditors and fixed rate loans.
There are a few rules on assigning instrument codes. African Development Fund (ADF) loans always should have instrument code - 1. This is because the MTDS analytical tool is programmed to capture specificities of the ADF loan repayment structure under the instrument code 1.
Continue assigning instrument codes to all of your existing external instruments. In our example, these are instruments from 2 to 6. If warranted, leave some instrument codes (in our example 7; 8) for the new instruments that you would like to include in the medium term debt strategy and that are not in your existing debt portfolio. Such instruments could be – International Bond – 7; Syndicated Loan – 8. Then continue assigning codes for domestic instruments. See Table 2, second column.
Table 2: Assigning Instrument Code
Representative Instrument / Instrument CodeIDA/IFAD/EDF_Fixed / 2
IBRD/ADB/IDB_Fixed / 3
ADF_Fixed / 1
IBRD/ADB/IDB_Floating / 4
Bilateral_Fixed / 5
Commercial Bank_Fixed / 6
Treasury Bonds_10Y_Fixed / 12
Treasury Bonds_3Y_Fixed / 10
Treasury Bonds_5Y_Fixed / 11
Treasury Bills_Fixed / 9
When you have finished assigning instrument codes, the table (e.g. Table 2) in the worksheet ‘Representative Instruments_FX’ will reflect these codes. (See Step 6 in the ‘Working sheet’).
Go to the worksheet ‘Debt Original Currency’ column ‘Instrument Code’. In this column use the VLOOKUP function to assign the instrument code from the worksheet ‘Representative Instruments_FX’ Table 2, to each existing loan / creditor listed in the worksheet ‘Debt Original Currency’.
Now when we have a correct formula in the first cell of the column, extend (drag) this formula across the whole column.
With these steps we have assigned each loan / creditor to a representative instrument and created a code for each instrument (see Table 3). This will allow us to aggregate cash flows by representative instruments. Now let’s calculate the right cash flows.
Table 3: From Loans and Creditors to ‘Representative Instruments’
Step 3: Calculating Cash Flows
Go to the ‘Debt Original Currency’ worksheet. In our example section A4 to X44 contains general terms and conditions of existing loans, while section Z4:BW44 includes information on principal repayments of existing loans until the final maturity.
Copy the section on loans’ general terms and conditions (columns A3 to X44) and paste it twice below the original section (in our example these are sections A46:X87 and A89:X131) to create space for generating the debt outstanding and interest payments at the end of each year.
In most instances, the country’s debt database will contain the information on the loan principal repayments until maturity. However, if this information is not available, we can calculate it based on the debt outstanding at the end of a period, the remaining grace period and the last payment date of a loan. See section CC4:DZ44 for the formula to calculate principal repayments.
Let’s assume that your database contains information on the loan principal repayments. Now we want to verify that the sum of principal payments indeed equals to the debt outstanding and disbursed (DOD) at the end of the period, which is our data cut-off date.
Enter a new column (in our example it is a column Y). Use the formula:
If the formula returns a value different from zero, then you should re-validate the total amount outstanding or the principal repayments.
Our next task is to calculate the outstanding balance at the end of each period (in our example it is done in the section A47:BW87). Outstanding balances can be projected on the basis of the initial outstanding balance and the principal payments. Use the following formula:[6]
Our final task, in this section, is to calculate interest payments on a yearly basis (in our example it is done in the section A90:BW130). Use the following formula:
Recall that for the fixed rate loans we use contracted interest rate, while for the floating rate loans we use only the margin. Use the Excel IF function to differentiate which interest rate to pick depending on whether the loan is fixed and floating interest rate. For example, cell Z90 has the formula: =IF($T90="Fixed",$U90,$W90)*Y47.[7] This instructs Excel to look for the applicable interest rate from column U if it is fixed rate debt, otherwise (i.e., if it is floating), then Excel will look for the margin in column X. In both cases, it is multiplied by the amount outstanding in the previous period.