## INTRODUCTION — tnmea2019003

## Source details

**Canonical URL:** [INTRODUCTION — tnmea2019003](https://www.imf.org/-/media/files/publications/tnm/2019/tnmea2019003.pdf)

## Other formats

- [Markdown version](/-/media/files/publications/tnm/2019/tnmea2019003.pdf.md)
- [Structured JSON version](/-/media/files/publications/tnm/2019/tnmea2019003.pdf.json)

---

### Overview
- Document structure: TYPE OF DATA TO USE; DEBT DATA PREPARATION STEPS (Step 1 through Step 6); TABLES; ANNEXES; GLOSSARY.
- Key sections: TYPE OF DATA TO USE; DEBT DATA PREPARATION STEPS (Step 1–Step 6); TABLES (1–5); ANNEXES A and B; GLOSSARY.
- Purpose: Guide preparation of debt data for the Medium-Term Debt Management Strategy Analytical Tool (MTDS AT), including setting up a “Data Preparation” Excel file, mapping instruments, computing and converting cash flows, aggregating by representative instruments, and producing portfolio composition charts.

### Objective and scope
- Objective: Guide preparation of debt data used in the MTDS AT.
- Scope:
  - What type of data are needed to prepare a debt management strategy, and how to prepare this data for the MTDS AT.
  - Assumes database accuracy has been examined by the analyst; process helps identify gaps and errors.
  - Methodology is one suggested approach, particularly for sizeable databases; alternatives are valid if key aggregation criteria (Step 2) are respected.
  - Illustrative Excel file uses a fictitious country Utopia (currency code UTP).

### Key inputs to the MTDS AT
- Required accurate data and fields:
  - Total amount of disbursed and outstanding debt (DOD).
  - Creditor’s name.
  - Loan or securities identification number (ID).
  - Currency.
  - First and last date of repayment.
  - Grace period.
  - Interest rate and type—fixed or variable (for variable instruments, separate reference rate and margin).
  - Total amount of debt outstanding at the end of the period (calendar or fiscal).
  - End of period exchange rates for all currencies in the debt portfolio.
  - Cash flow information on principal repayments and interest payments until final maturity for every debt instrument.
- Minimum coverage: external and domestic debt of the central government.
- Note: The definition of external debt in MTDS is currency-based rather than residency-based.

### Analytical focus and aggregation principle
- MTDS AT analyzes costs and risks (exchange rate, interest rates, refinancing) of the overall government debt portfolio as it evolves with future funding choices.
- Aggregate individual debt instruments into a maximum of 20 representative instruments that capture important features of alternative financing sources.
- Group instruments with similar characteristics (e.g., foreign versus domestic currency; commercial versus concessional interest rates).
- Do not combine instruments with different interest rate types (fixed versus variable) or domestic versus foreign currency instruments.
- For foreign currency instruments, choose one (or up to a few, but not more than four) representative foreign currencies.

### Reserved and special instrument codes
- Maximum of 20 representative instruments.
- Instrument code 1 is always assigned to ADF loans.
- Instrument code 20 is dedicated to index-linked instruments (principals or coupons adjusted by an index).

### Type of data to use (summary)
- Use debt data by instrument from the existing debt recording system in the original currency.
- Use data for disbursed and outstanding debt (DOD); exclude contracted but undisbursed debt.
- Extract as much information as possible to allow validation and consistency checks.
- Ensure exchange rates have the same cut-off date as the debt data; obtain end-of-period exchange rates if not provided.
- Special cases:
  - Loans with choice of repayment currency: use the contract currency.
  - SDR-denominated loans: use the SDR exchange rate reported by IMF or World Bank to convert to base currency.

### Preparation steps (overview)
- Step 1: Setting up the Data Preparation Excel File
- Step 2: Assigning Debt Instrument to a ‘Representative Instrument’
- Step 3: Calculating Cash Flows
- Step 4: Converting Cash Flows from Original Currency to Base Currency
- Step 5: Aggregating Cash Flows by ‘Representative Instruments’
- Step 6: Creating Charts for the Portfolio Composition

### Step 1 — Setting up the Data Preparation Excel File (key actions)
- Create worksheets:
  - Debt Original Currency
  - Debt_UTP, Debt_USD, or Debt_EUR (choose base; manual uses USD for illustration)
  - Representative Instruments_FX
  - Aggregated_UTP or Aggregated_USD
  - Working Sheet
  - Charts
- Download central government external debt (foreign currency) and domestic debt data with required parameters into Debt Original Currency.

### Step 2 — Assigning debt instruments to representative instruments (process & rules)
- Copy creditor names (Debt Original Currency, cells C5:C45) to Working Sheet; use Remove Duplicates to list unique creditors.
- Group creditors/instruments with similar terms into representative instruments (examples: multilateral concessional loans; bilateral concessional loans; international capital market bonds; syndicated loans; T-bonds; T-bills).
- Mapping procedure in Excel:
  - Paste representative instruments into Representative Instruments_FX.
  - In Debt Original Currency add columns ‘Representative Instruments-1’, ‘Representative Instruments-2’, and ‘Instrument Code’.
  - Use VLOOKUP to assign representative instrument (example): =VLOOKUP(C5,'Representative Instruments_FX'!$B$5:$C$24,2,FALSE).
  - Concatenate representative instrument with interest rate type for Representative Instruments-2.
  - Assign numerical instrument codes using Remove Duplicates and VLOOKUP back to Debt Original Currency.
- Rules and notes:
  - Do not group different interest rate types together.
  - Do not group domestic-currency instruments with foreign-currency instruments.
  - If multiple term descriptors exist for the same interest rate type, use a single code.
  - Leave some instrument codes available for future/new instruments.

### Example instrument codes and groupings (exact mappings)
- IDA/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: 14
- Treasury Bonds_3Y_Fixed: 12
- Treasury Bonds_5Y_Fixed: 13
- Treasury Bills_Fixed: 11

### Step 3 — Calculating cash flows (verification and formulas)
- Verify principal repayments until maturity; ensure sum of principal repayments equals DOD at the cut-off date.
- Use Equation (1) to check consistency between outstanding amounts and principal repayments; re-validate if result differs from zero.
- Calculate outstanding balance at end of each period using initial outstanding balance and principal payments.
- Calculate annual interest payments until maturity using Equation (2):
  - Fixed-rate instruments use contracted interest rate.
  - Variable-rate instruments use only the fixed margin initially; the reference rate will be added later in MTDS AT.
  - Example Excel implementation: =IF($T91="Fixed",$U90,$W90)*Y47 (example cell Z91).
- T-bills: treated as zero coupon and aggregated as one stylized instrument with maturity of one year in MTDS AT; Annex A details yield calculations.
- Recommendation: obtain principal repayment cash flows from debt recording system where possible.

### Step 4 — Converting cash flows to base currency (exchange rates and conversion)
- Ensure exchange rates use the same cut-off date as debt data.
- In Debt Original Currency add column ‘Exchange Rates’ (Column I); list tranche currencies and Remove Duplicates to get unique currencies.
- Example exchange rates (as at December 31, 2017) — preserve values exactly:
  - USD 15.00 1.00
  - XDR 21.37 1.42
  - EUR 18.03 1.20
  - JPY 0.13 0.01
  - DKK 2.42 0.16
  - CHF 15.39 1.03
  - IDB 21.37 1.42
  - KRW 0.01 0.00
  - UTP 1.00 0.07
- Use VLOOKUP to assign exchange rates depending on chosen base currency (units of base currency).
- Copy Debt Original Currency into Debt_UTP or Debt_USD per chosen base and convert principal repayments, outstanding amounts, and interest payments using the exchange rate in Column I.
  - Example conversion formula: =$I5*'Debt Original currency'!Z5

### Step 5 — Aggregating cash flows by representative instruments (method)
- Use Aggregated_UTP or Aggregated_USD to create three tables aggregating principal repayments, debt outstanding, and interest payments by representative instrument.
- List instrument numbers 1 to 20 (1 = ADF loans; 20 = index-linked).
- Enter currency abbreviations and use CONCATENATE to link instrument number to currency, except instrument 20 where code IDX is used.
  - Example formula: =CONCATENATE($B$5,"_",D5)
- Use SUMIF to aggregate instrument-level data by instrument code across years into UTP or USD millions.
  - Example SUMIF (Aggregated_UTP cell I5): =SUMIF ('Debt UTP'! $G$5:$G$45, Aggregated!$D5, 'Debt UTP'! Z$5 : Z$45)/(10^6)
  - Example for Aggregated_USD: =SUMIF ('Debt UTP'! $G$5:$G$45, Aggregated!$D5, 'Debt USD'! Z$5 : Z$45)/(10^6)
- Include a check row (red cells) to verify no cash flow information lost in aggregation.

### Step 6 — Creating charts for portfolio composition
- Use Charts worksheet and SUMIF from Debt_UTP or Debt_USD to aggregate outstanding amounts by currency for each instrument.
- Create charts showing composition of existing debt by:
  - currency
  - interest rate type
  - creditor type
- If many creditors, use representative instrument groupings to consolidate (e.g., group all T-bonds rather than separate maturities).

### Glossary (selected acronyms and terms preserved verbatim)
- ADF African Development Fund
- AT Analytical Tool
- CHF Swiss franc
- CNY Chinese yuan
- CS-DRMS Commonwealth Secretariat Debt Recording and Management System
- DKK Danish krone
- DMFAS Debt Management and Financial Analysis System
- DOD Disbursed and Outstanding Debt
- EDF European Development Fund
- EUR Euro
- GBP British pounds
- IBRD International Bank for Reconstruction and Development
- IDA International Development Association
- IDB Inter-American Development Bank
- IFAD International Fund for Agricultural Development
- IMF International Monetary Fund
- JPY Japanese yen
- KRW South Korean won
- MTDS Medium-Term Debt Management Strategy
- MTDS AT Medium-Term Debt Management Strategy Analytical Tool
- T-bills Treasury Bills
- T-bonds Treasury Bonds
- UNCTAD United Nations Conference on Trade and Development
- USD United States Dollar
- UTP Utopia
- WB World Bank
- XDR Special Drawing Rights

### Annex A — Treasury Bills: Interest Payments and Average Yields (scenarios and examples)
- MTDS AT uses annual cash flows; T-bills assumed maturity of one year.
- Analysts must record interest payment and interest rate associated with existing outstanding stock of T-bills.
- For each issuance users should have tenor, issuance yield rate, and cash raised. Annex provides calculations when only discount rate and face value are available.

- Scenario A — Outstanding stock recorded as Cash Raised; yields recorded
  - Calculations: = (C) * [365/(B)]; = (A) * [(1 + C)]; = (F) - (A)
  - Example data (UTP millions):
    - 3 months — Cash Raised 1,533; Tenor 91 days; Yields 3.00 percent; Annualized Yield 12.03 percent; Face Value 1,578; Interest Payments 46
    - 6 months — Cash Raised 1,341; Tenor 182 days; Yields 7.05 percent; Annualized Yield 14.14 percent; Face Value 1,435; Interest Payments 95
    - 12 months — Cash Raised 958; Tenor 365 days; Yields 15.35 percent; Annualized Yield 15.35 percent; Face Value 1,105; Interest Payments 147

- Scenario B — Outstanding stock recorded as Cash Raised; discount rates recorded
  - Calculations: = (A) ÷ [(1 – (E)]; = (F) - (A)
  - Example data (UTP millions):
    - 3 months — Cash Raised 1,533; Tenor 91 days; Discount Rates 2.91 percent; Face Value 1,578; Interest Payments 46
    - 6 months — Cash Raised 1,341; Tenor 182 days; Discount Rates 6.59 percent; Face Value 1,435; Interest Payments 95
    - 12 months — Cash Raised 958; Tenor 365 days; Discount Rates 13.31 percent; Face Value 1,105; Interest Payments 147
  - Note: Move towards recording yields for comparability.

- Scenario C — Outstanding stock recorded as Face Value; yields recorded
  - Calculations: = (C) * [365/(B)]; = (F) * [(1-(1÷(1+(C)))]
  - Example data (UTP millions):
    - 3 months — Tenor 91 days; Yields 3.00 percent; Annualized Yield 12.03 percent; Face Value 1,578; Interest Payments 46
    - 6 months — Tenor 182 days; Yields 7.05 percent; Annualized Yield 14.14 percent; Face Value 1,435; Interest Payments 95
    - 12 months — Tenor 365 days; Yields 15.35 percent; Annualized Yield 15.35 percent; Face Value 1,105; Interest Payments 147

- Scenario D — Outstanding stock recorded as Face Value; discount rates recorded
  - Calculations: = (F) * (E)
  - Example data (UTP millions):
    - 3 months — Tenor 91 days; Discount Rates 2.91 percent; Face Value 1,578; Interest Payments 46
    - 6 months — Tenor 182 days; Discount Rates 6.59 percent; Face Value 1,435; Interest Payments 95
    - 12 months — Tenor 365 days; Discount Rates 13.31 percent; Face Value 1,105; Interest Payments 147

- Weighted average annualized yield — guidance:
  - Using cash raised and annualized yield: multiply (A) and (D) for each tenor, sum results, and divide by sum of (A).
  - If outstanding stock in face value: calculate cash raised = Face Value - Interest Payments; then yields = (Face Value - Cash Raised) ÷ Cash Raised.

### Annex B — Debt Denominated in Special Drawing Rights (SDR) (overview and numeric illustration)
- SDR is based on a basket of five currencies: USD, EUR, CNY (RMB), JPY (YEN), GBP.
- SDR value calculated daily; SDR loans expose debt to five underlying currencies.
- Treatment guidance:
  - For existing debt: find SDR exchange rate on the appropriate date to convert SDR loans into the major foreign currency used in the AT (e.g., USD or euro), then convert into local currency if needed.
  - For potential financing: use the major currency rather than SDR, noting exposure to five currencies.

- Example converting XDR 315 million (as at end-Dec 2017)
  - Weightings and exchange rates; FX Per 1 USD (1/(C)):
    - CNY: Weighting 10.92%; Exchange Rate 9.273; FX Per 1 USD 0.154
    - EUR: Weighting 30.93%; Exchange Rate 1.187; FX Per 1 USD 1.199
    - GBP: Weighting 8.09%; Exchange Rate 1.054; FX Per 1 USD 1.351
    - JPY: Weighting 8.33%; Exchange Rate 160.785; FX Per 1 USD 0.009
    - USD: Weighting 41.73%; Exchange Rate 1.424; FX Per 1 USD 1.000
  - Notes: Weightings reviewed every five years; next revision not until September 2021. Currency unit of renminbi is yuan with code CNY.

- Steps and numeric illustration:
  - Convert XDR 315 million into USD: multiply XDR 315 million by 1.424.
  - Convert XDR 315 million into euro: multiply XDR 315 million by 1.187.
  - SDR exposures in XDR millions:
    - CNY 34
    - EUR 97
    - GBP 25
    - JPY 26
    - USD 131
    - Total 315
  - Exposure in each currency (equivalent in millions):
    - CNY 319 = 34 XDR
    - EUR 116 = 97 XDR
    - GBP 27 = 25 XDR
    - JPY 4,220 = 26 XDR
    - USD 187 = 131 XDR
  - Exposure to each currency in USD (equivalent in USD millions):
    - CNY 319 = 49 USD
    - EUR 116 = 139 USD
    - GBP 27 = 36 USD
    - JPY 4,220 = 37 USD
    - USD 187 = 187 USD
    - Aggregate = 449 USD
    - XDR 315 = 449 USD

*Source: tnmea2019003 - INTRODUCTION*

### INTRODUCTION _________________________________________________________________________________ 6

### INTRODUCTION

### Overview
- Document structure covers: TYPE OF DATA TO USE; DEBT DATA PREPARATION STEPS (Step 1 through Step 6); TABLES; ANNEXES; GLOSSARY.
- Page references appearing in the source: 6, 7, 8, 10, 12, 13, 14, 16, 18, 20, 21, 22, 24, 5, 6.

### Key sections listed
- TYPE OF DATA TO USE
- DEBT DATA PREPARATION STEPS
  - Step 1: Setting up the “Data Preparation” Excel File
  - Step 2: Assigning Debt Instrument to a ‘Representative Instrument’
  - Step 3: Calculating Cash Flows
  - Step 4: Converting Cash Flows from Original Currency to Base Currency
  - Step 5: Aggregating Cash Flows by ‘Representative Instruments’
  - Step 6: Creating Charts for the Portfolio Composition
- TABLES (listed by title)
  - 1. Original Creditors Grouped by ‘Representative Instruments’
  - 2. Assigning Instrument Code
  - 3. From Loans and Creditors to ‘Representative Instruments’
  - 4. Exchange Rates as at December 31, 2017
  - 5. Cash Flows Aggregated by 'Representative Instruments'
- ANNEXES
  - A. Treasury Bills: Interest Payments and Average Yields
  - B. Debt Denominated in Special Drawing Rights
- GLOSSARY

### Purpose (implied by structure)
- To guide preparation of debt data for use in analytical tools, including setting up a “Data Preparation” Excel file and steps to map instruments, compute and convert cash flows, aggregate by representative instruments, and produce portfolio composition charts.

### Document pagination cues
- Introduction and glossary entries indicate section pagination including “6” and “5” as shown in the source.

### Glossary (overview)
- Acronyms and terms included in glossary (preserved verbatim):
  - ADF African Development Fund
  - AT Analytical Tool
  - CHF Swiss franc
  - CNY Chinese yuan
  - CS-DRMS Commonwealth Secretariat Debt Recording and Management System
  - DKK Danish krone
  - DMFAS Debt Management and Financial Analysis System
  - DOD Disbursed and Outstanding Debt
  - EDF European Development Fund
  - EUR Euro
  - GBP British pounds
  - IBRD International Bank for Reconstruction and Development
  - IDA International Development Association
  - IDB Inter-American Development Bank
  - IFAD International Fund for Agricultural Development
  - IMF International Monetary Fund
  - JPY Japanese yen
  - KRW South Korean won
  - MTDS Medium-Term Debt Management Strategy
  - MTDS AT Medium-Term Debt Management Strategy Analytical Tool
  - T-bills Treasury Bills
  - T-bonds Treasury Bonds
  - UNCTAD United Nations Conference on Trade and Development
  - USD United States Dollar
  - UTP Utopia
  - WB World Bank
  - XDR Special Drawing Rights

### Immediate actions implied for users preparing debt data
- Prepare a “Data Preparation” Excel File (see Step 1).
- Assign each debt instrument to a ‘Representative Instrument’ (see Step 2).
- Calculate cash flows for each instrument (see Step 3).
- Convert cash flows from original currency to base currency using exchange rates (see Step 4).
- Aggregate cash flows by ‘Representative Instruments’ (see Step 5).
- Create charts for portfolio composition (see Step 6).

*Source: tnmea2019003 - INTRODUCTION*

### INTRODUCTION

### INTRODUCTION

### Objective and scope
- The objective of the data preparation manual is to guide the preparation of the debt data used in the Medium-Term Debt Management Strategy Analytical Tool (MTDS AT).
- The manual discusses: (i) what type of data are needed to prepare a debt management strategy, and (ii) how to prepare this data for the MTDS AT.
- The manual assumes the accuracy of the database has been examined by the analyst, although the process described will also help identify gaps and errors in the database.
- The methodology suggested is one of many possible ways of preparing the debt data for the MTDS AT and is particularly suggested when the debt database is sizeable. Alternative methodologies are valid provided the key aggregation criteria (described under Step 2) are respected.
- The manual should be used in combination with the Excel file MTDS Data Preparation File; the debt dataset in the illustrative Excel file is fictitious (the fictitious country is Utopia, currency code UTP).

### Key inputs to the MTDS AT
- Accurate data on the total amount of disbursed and outstanding debt (DOD).
- Detailed information on financial terms and conditions for each instrument, including at a minimum:
  - creditor’s name
  - loan or securities identification number (ID)
  - currency
  - first and last date of repayment
  - grace period
  - interest rate and type—fixed or variable (for variable instruments, separate reference rate and margin)
  - total amount of debt outstanding at the end of the period (calendar or fiscal)
  - end of period exchange rates for all currencies in the debt portfolio
- Cash flow information on principal repayments and interest payments until final maturity for every debt instrument.
- Minimum coverage: external and domestic debt of the central government.
- Note: The definition of external debt in MTDS is currency-based rather than residency-based.

### Analytical focus and aggregation principle
- The MTDS exercise analyzes costs and risks (exchange rate, interest rates, refinancing) of the overall government debt portfolio as it evolves with future funding choices.
- Aggregation of individual debt instruments into a few representative instruments (maximum of 20) that capture the important features of alternative financing sources facilitates analysis.
- Group debt instruments with similar characteristics (for example, foreign versus domestic currency denominated, commercial versus concessional interest rates).
- Groupings should respect the rule that instruments with different interest rate types (fixed versus variable) or domestic versus foreign currency should not be combined.
- For foreign currency instruments, choose one (or up to a few, but not more than four) representative foreign currencies.

### Reserved and special instrument codes
- Maximum of 20 representative instruments.
- Instrument code 1 is always assigned to ADF loans (MTDS AT programmed to capture ADF repayment specificities).
- Instrument code 20 is dedicated to index-linked instruments (principals or coupons adjusted by an index).

### Type of data to use (summary)
- Use debt data by instrument from the existing debt recording system in the original currency of the loan.
- Use data for disbursed and outstanding debt (DOD); exclude contracted but undisbursed debt.
- Extract as much information as possible from the debt recording system to allow validation and consistency checks.
- Ensure exchange rates have the same cut-off date as the debt data; obtain end-of-period exchange rates if not provided by the debt recording system.
- Special cases:
  - Loans with choice of repayment currency: use the contract currency (example given: contract currency is the U.S. dollar).
  - SDR-denominated loans: use the SDR exchange rate reported by IMF or World Bank to convert to base currency.

### Preparation steps (overview)
- Step 1: Setting up the Data Preparation Excel File
- Step 2: Assigning Debt Instrument to a ‘Representative Instrument’
- Step 3: Calculating Cash Flows
- Step 4: Converting Cash Flows from Original Currency to Base Currency
- Step 5: Aggregating Cash Flows by ‘Representative Instruments’
- Step 6: Creating Charts for the Portfolio Composition

### Step 1: Setting up the Data Preparation Excel File (key actions)
- Download central government external debt (foreign currency) and domestic debt data with required parameters to an Excel workbook; create worksheet Debt Original Currency.
- Create a worksheet to convert all debt cash flows into a common base currency (example base currencies: UTP, USD, EUR). Example worksheet names: Debt_UTP, Debt_USD, Debt_EUR. The manual uses USD as the base currency for illustration.
- Create worksheet Representative Instruments_FX to define and save representative instruments and exchange rates.
- Create worksheet Aggregated_UTP or Aggregated_USD (per chosen base) to summarize cash flows by representative instruments.
- Create worksheet Working Sheet for intermediate/preparatory steps.
- Create worksheet Charts to produce portfolio composition charts (currency, interest rate, creditor).

### Step 2: Assigning debt instruments to representative instruments (process & rules)
- Copy creditor names (Debt Original Currency, cells C5:C45) to Working Sheet; use Excel Remove Duplicates to list unique creditors.
- Analyze creditors to determine which instruments carry similar terms and conditions and group them under representative instruments.
- Representative instruments examples: multilateral concessional loans, multilateral semi-concessional loans, bilateral concessional loans, bilateral semi-concessional loans, international capital market bonds, syndicated loans, Treasury bonds (T-bonds), Treasury bills (T-bills) with different maturities.
- Mapping procedure in Excel:
  - Paste a table of representative instruments into Representative Instruments_FX.
  - In Debt Original Currency insert columns ‘Representative Instruments-1’, ‘Representative Instruments-2’, and ‘Instrument Code’ (Columns E, F, and G in Prepared_Debt Original Currency).
  - Use VLOOKUP to assign representative instrument (example formula provided): =VLOOKUP(C5,'Representative Instruments_FX'!$B$5:$C$24,2,FALSE).
  - Use CONCATENATE (or &) to link representative instrument with interest rate type in ‘Representative Instruments-2’ (concatenate columns E and T).
  - Use Remove Duplicates on the representative instrument list in Working Sheet and assign numerical instrument codes.
  - Use VLOOKUP to assign instrument codes back to Debt Original Currency (example formula provided): =VLOOKUP(F5,'Representative Instruments_FX'!$E$5:$F$14,2,FALSE).
- Rules and notes:
  - Debt instruments with different interest rate types (fixed vs variable) cannot be grouped in the same instrument.
  - Domestic-currency instruments cannot be grouped with foreign-currency instruments.
  - If multiple terms (e.g., fixed described as “amount”) exist, use a single code for the same interest rate type.
  - If warranted, leave some instrument codes available for future/new instruments not part of existing portfolio.

### Example instrument codes and groupings (as provided)
- Representative Instrument -> Instrument Code (exact mappings):
  - IDA/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: 14
  - Treasury Bonds_3Y_Fixed: 12
  - Treasury Bonds_5Y_Fixed: 13
  - Treasury Bills_Fixed: 11

- Example mapping from creditors to Representative Instrument-2 and Instrument Code is shown in the manual (Table 3). Examples include:
  - International Development Association -> IDA/IFAD/EDF_Fixed -> 2
  - ADF -> ADF_Fixed -> 1
  - ADB -> IBRD/ADB/IDB_Floating -> 4
  - ABC Bank -> Commercial Bank_Fixed -> 6
  - Treasury Bonds_10Y -> T-Bonds 10 YR_Fixed -> 14
  - Treasury Bills -> T-Bills_Fixed -> 11

### Step 3: Calculating cash flows (verification and formulas)
- Verify principal repayments until maturity are accurate; ensure sum of principal repayments equals DOD at the cut-off date.
- Enter a new column (example column Y) and apply Equation (1) (present in the manual) to check consistency between outstanding amounts and principal repayments; if the formula returns a value different from zero, re-validate total outstanding or principal repayments.
- Calculate outstanding balance at the end of each period using initial outstanding balance and principal payments (Equation (1) referenced).
- Calculate annual interest payments until maturity using Equation (2).
  - For fixed-rate instruments, interest payments use the contracted interest rate.
  - For variable-rate instruments, use only the fixed margin initially; the reference rate (e.g., LIBOR) will be added later in MTDS AT based on forward-looking projections.
  - Example Excel implementation: =IF($T91="Fixed",$U90,$W90)*Y47 (cell Z91 example) — picks contracted rate for fixed loans or margin for variable loans.
- Note on T-bills:
  - T-bills (zero coupon) are aggregated as one stylized instrument with maturity of one year in MTDS AT; Annex A in the manual illustrates how to calculate the appropriate yield for T-bills.
- Recommendation: obtain principal repayment cash flows from debt recording system where possible as they reflect the most accurate annual repayment profile.

### Step 4: Converting cash flows to base currency (exchange rates and conversion)
- Ensure proper exchange rate for each debt instrument (same cut-off date as debt data).
- In Debt Original Currency worksheet add column ‘Exchange Rates’ (Column I in Prepared_Debt Original Currency); list all tranche currencies and use Remove Duplicates to list unique currencies.
- Example exchange rates table (as at December 31, 2017) — preserve values exactly:
  - Currency | In units of UTP | In units of USD
    - USD 15.00 1.00
    - XDR 21.37 1.42
    - EUR 18.03 1.20
    - JPY 0.13 0.01
    - DKK 2.42 0.16
    - CHF 15.39 1.03
    - IDB 21.37 1.42
    - KRW 0.01 0.00
    - UTP 1.00 0.07
- Use VLOOKUP to assign the correct exchange rate to each tranche currency depending on chosen base currency (units of base currency).
- Copy Debt Original Currency worksheet into Debt_UTP or Debt_USD per chosen base and convert principal repayments, outstanding amounts, and interest payments using the exchange rate in Column I.
  - Example conversion formula: =$I5*'Debt Original currency'!Z5

### Step 5: Aggregating cash flows by representative instruments (method)
- Use Aggregated_UTP or Aggregated_USD worksheet to create three tables aggregating principal repayments, debt outstanding, and interest payments by representative instrument.
- List numbers 1 to 20 (maximum number of representative instruments); Instruments 1 and 20 are dedicated to ADF loans and index-linked bonds respectively.
- Enter abbreviations for domestic currency and representative foreign currency (e.g., UTP and USD) and use CONCATENATE to link instrument number to currency, except instrument 20 where code IDX is used.
  - Example formula in Aggregated_UTP cell E5: =CONCATENATE($B$5,"_",D5)
- Use SUMIF to aggregate instrument-level data by instrument code across years into UTP or USD millions.
  - Example SUMIF formula (in Aggregated_UTP cell I5): =SUMIF ('Debt UTP'! $G$5:$G$45, Aggregated!$D5, 'Debt UTP'! Z$5 : Z$45)/(10^6)
  - Example for Aggregated_USD: = SUMIF ('Debt UTP'! $G$5:$G$45, Aggregated!$D5, 'Debt USD'! Z$5 : Z$45)/(10^6)
- Include a check row (red cells) that verifies no cash flow information has been lost in aggregation compared to instrument-by-instrument data.

### Step 6: Creating charts for portfolio composition
- Use Charts worksheet. Aggregate outstanding amounts by currency for each debt instrument using SUMIF from Debt_UTP or Debt_USD, depending on chosen base currency.
- Create charts showing composition of existing debt by:
  - currency
  - interest rate type
  - creditor type
- If too many creditors, use representative instrument groupings to consolidate (for example, group all T-bonds rather than separate maturities).

*Source: INTRODUCTION, tnmea2019003 - INTRODUCTION*

### Annex A

### Annex A — Treasury Bills: Interest Payments and Average Yields

### Overview and purpose
- MTDS AT uses annual cash flows; T-bills are assumed to have a maturity of one year, the shortest maturity.
- Analysts must correctly record the interest payment and interest rate associated with existing outstanding stock of T-bills.
- This annex illustrates how to calculate the interest payment from an outstanding stock of T-bills and provides guidance on calculating the annualized yield rate to record in the MTDS AT.
- For each issuance, users should have information on the tenor, issuance yield rate, and the cash raised. When only discount rate and face value are available, the annex provides illustrations covering both scenarios.

### Scenario A — Outstanding stock recorded as Cash Raised; yields recorded
- Available columns: (A) Cash Raised, (B) Tenor (Days), (C) Yields (Percent).
- Calculations shown:
  - = (C) * [365/(B)]
  - = (A) * [(1 + C)]
  - = (F) - (A)
- Example data (UTP millions):
  - Instruments: 3 months — Cash Raised 1,533; Tenor 91 days; Yields 3.00 percent; Annualized Yield 12.03 percent; Face Value 1,578; Interest Payments 46
  - Instruments: 6 months — Cash Raised 1,341; Tenor 182 days; Yields 7.05 percent; Annualized Yield 14.14 percent; Face Value 1,435; Interest Payments 95
  - Instruments: 12 months — Cash Raised 958; Tenor 365 days; Yields 15.35 percent; Annualized Yield 15.35 percent; Face Value 1,105; Interest Payments 147

### Scenario B — Outstanding stock recorded as Cash Raised; discount rates recorded
- Available columns: (A) Cash Raised, (B) Tenor (Days), (E) Discount Rates (Percent).
- Calculations shown:
  - = (A) ÷ [(1 – (E)]
  - = (F) - (A)
- Example data (UTP millions):
  - Instruments: 3 months — Cash Raised 1,533; Tenor 91 days; Discount Rates 2.91 percent; Face Value 1,578; Interest Payments 46
  - Instruments: 6 months — Cash Raised 1,341; Tenor 182 days; Discount Rates 6.59 percent; Face Value 1,435; Interest Payments 95
  - Instruments: 12 months — Cash Raised 958; Tenor 365 days; Discount Rates 13.31 percent; Face Value 1,105; Interest Payments 147
- Note: Debt analysts should move towards recording yields, which are more readily comparable to other instruments’ cost of borrowing.

### Scenario C — Outstanding stock recorded as Face Value; yields recorded
- Available columns: (B) Tenor (Days), (C) Yields (Percent), (F) Face Value.
- Calculations shown:
  - = (C) * [365/(B)]
  - = (F) * [(1-(1÷(1+(C)))]
- Example data (UTP millions):
  - Instruments: 3 months — Tenor 91 days; Yields 3.00 percent; Annualized Yield 12.03 percent; Face Value 1,578; Interest Payments 46
  - Instruments: 6 months — Tenor 182 days; Yields 7.05 percent; Annualized Yield 14.14 percent; Face Value 1,435; Interest Payments 95
  - Instruments: 12 months — Tenor 365 days; Yields 15.35 percent; Annualized Yield 15.35 percent; Face Value 1,105; Interest Payments 147

### Scenario D — Outstanding stock recorded as Face Value; discount rates recorded
- Available columns: (B) Tenor (Days), (E) Discount Rates (Percent), (F) Face Value.
- Calculations shown:
  - = (F) * (E)
- Example data (UTP millions):
  - Instruments: 3 months — Tenor 91 days; Discount Rates 2.91 percent; Face Value 1,578; Interest Payments 46
  - Instruments: 6 months — Tenor 182 days; Discount Rates 6.59 percent; Face Value 1,435; Interest Payments 95
  - Instruments: 12 months — Tenor 365 days; Discount Rates 13.31 percent; Face Value 1,105; Interest Payments 147

### Weighted average annualized yield — calculation guidance
- Using cash raised and annualized yield for each tenor (e.g., from Scenario A):
  - Multiply columns (A) and (D) for each tenor, sum the results, and divide by the sum of column (A).
- For cases where outstanding stock is in face value (Scenarios C and D):
  - Calculate cash raised by subtracting interest payments from face value.
  - Then compute yields = (Face Value - Cash Raised) ÷ Cash Raised.

*Source: Annex A, tnmea2019003 - Annex A*

---

### Annex B — Debt Denominated in Special Drawing Rights

### Overview and treatment
- Loans from major multilateral institutions are often denominated in special drawing rights (SDRs).
- SDR value is based on a basket of five currencies: U.S. dollar (USD), the euro (EUR), the Chinese renminbi (RMB / CNY), the Japanese yen (YEN / JPY), and the British pound sterling (GBP).
- SDR currency value is calculated daily; information can be accessed on the IMF and World Bank websites.
- SDR loans give rise to exchange rate risk exposure originating from the five underlying currencies.
- Treatment guidance:
  - For existing debt: find the SDR exchange rate on the appropriate date to convert SDR loans into the major foreign currency used in the AT (for example, USD or euro), and convert further into local currency if existing debt data are entered in local currency.
  - For potential financing: use the major currency as opposed to SDR, while keeping in mind the instruments pose exchange rate risk to five currencies.

### Example: Converting SDR-denominated loans (aggregate XDR 315 million as at end-Dec 2017)
- Weightings and exchange rates (columns B and C); FX Per 1 USD (column D calculated as 1/(C)):
  - CNY: Weighting 10.92%; Exchange Rate 9.273; FX Per 1 USD 0.154
  - EUR: Weighting 30.93%; Exchange Rate 1.187; FX Per 1 USD 1.199
  - GBP: Weighting 8.09%; Exchange Rate 1.054; FX Per 1 USD 1.351
  - JPY: Weighting 8.33%; Exchange Rate 160.785; FX Per 1 USD 0.009
  - USD: Weighting 41.73%; Exchange Rate 1.424; FX Per 1 USD 1.000
- Notes:
  - The weightings are reviewed every five years; next revision not until September 2021.
  - Currency unit of renminbi is yuan with the code CNY.

### Steps and numeric illustration
- Convert XDR 315 million into USD or euro:
  - Multiply XDR 315 million by 1.424 to convert into USD.
  - Multiply XDR 315 million by 1.187 to convert into euro.
- Calculate SDR amounts exposed to each currency (multiply XDR 315 million by each currency weighting):
  - Exposure to the underlying five currencies in XDR (XDR millions):
    - CNY 34
    - EUR 97
    - GBP 25
    - JPY 26
    - USD 131
    - Total 315
- Exposure in each currency in its own denomination (Equivalent in millions):
  - CNY 319 = 34 XDR
  - EUR 116 = 97 XDR
  - GBP 27 = 25 XDR
  - JPY 4,220 = 26 XDR
  - USD 187 = 131 XDR
- Exposure to each currency in USD (Equivalent in USD millions):
  - CNY 319 = 49 USD
  - EUR 116 = 139 USD
  - GBP 27 = 36 USD
  - JPY 4,220 = 37 USD
  - USD 187 = 187 USD
  - Aggregate = 449 USD
  - XDR 315 = 449 USD

*Source: Annex B, tnmea2019003 - Annex A*

---


_Source: https://www.imf.org/-/media/files/publications/tnm/2019/tnmea2019003.pdf_