## INTRODUCTION (tnmea2019002) ## Source details **Canonical URL:** [INTRODUCTION (tnmea2019002)](https://www.imf.org/-/media/files/publications/tnm/2019/tnmea2019002.pdf) ## Other formats - [Markdown version](/-/media/files/publications/tnm/2019/tnmea2019002.pdf.md) - [Structured JSON version](/-/media/files/publications/tnm/2019/tnmea2019002.pdf.json) --- ### Purpose, role, and high-level guidance - Primary aim: raise the required amount of funding at the lowest possible cost over the medium to long run, consistent with a prudent degree of risk. - A Debt Management Strategy (DMS) operationalizes debt management objectives and feeds into borrowing plans and the budgetary framework; sound debt management: - contributes to macrofinancial stability, - complements fiscal and monetary policy, - supports market and institutional development. - Debt managers must ensure financing constraints do not lead to sharp reversals in fiscal policy or spikes in interest costs from realized market or contingent liability risk. - A DMS is one component of public debt management; other components include tactical decisions, coordination with other public sector policies, communication and accountability, and operations. ### MTDS framework and Analytical Tool (AT) - MTDS framework developed by the World Bank (WB) and International Monetary Fund (IMF) comprises: - Guidance Note for Developing a Medium-Term Debt Management Strategy (February 2009). - An associated Analytical Tool (AT) to assess cost-risk trade-offs. - The AT described is the MTDS Analytical Tool based on annual cash flow; discussion is also applicable to the quarterly cash flow version (differences in Annex A). - The manual is intended to be used with the Microsoft Excel-based AT. ### Role of the AT relative to qualitative analysis and DSA - The AT performs quantitative assessment of cost-risk trade-offs (step 6 of MTDS process) using information from steps 1–5. - Quantitative AT results should not be the sole determinant of strategy choice; essential qualitative factors (for example, domestic market development) not captured by the AT must be considered. - The AT is not a debt sustainability analysis (DSA) tool; interest payments and amortizations from the chosen DMS should feed into the DSA but the AT does not include DSA shock mechanics. --- ### OVERVIEW OF THE ANALYTICAL TOOL ### Purpose and functionality - The AT assesses and compares cost and risk characteristics of potential debt management strategies where a strategy is a combination of new borrowing across stylized debt instruments with varied financial characteristics. - It shows how composition of new borrowing influences the debt portfolio and how costs change under different interest and exchange rate assumptions. - Key limitation: The AT should not be used to analyze debt sustainability; use LIC-DSF or MAC DSA as appropriate. ### Main components and capacity limits - Main components: Inputs, Analysis, Outputs. - Capacity constraint: maximum of 20 stylized debt instruments; users must aggregate individual loans and securities into up to twenty instruments. --- ### INPUTS (types and specifics) ### Four types of inputs - Existing debt data: - Divided into principal and interest payments cash flows. - Used to calculate cost and risk indicators of the starting debt portfolio and over the strategy period including new debt. - Note: unless portfolio is entirely very short-term debt, some existing debt remains outstanding at end of strategy period. - Fiscal aggregates and debt service: - Primary deficit = difference between revenue and primary expenditure (primary expenditure and revenue are exogenous). - Forecasts of revenues and primary expenditures over the whole strategy period required. - Total financing needs = primary deficit + interest payments + debt amortization. - Future interest payments and amortization are calculated within the model as they vary with instrument composition and terms. - Borrowing strategies: - Specified as the proportion of financing need met through different stylized debt instruments. - Terms (borrowing rates and amortization) of each instrument are specified. - The model iteratively computes year-by-year interest and amortization cash flows and applies next year’s strategy until the end of the strategy period. - Interest and exchange rate assumptions: - Enter baseline scenario and a range of shock scenarios for each variable. - Interest rates (baseline and shock) on cash buffers also entered as exogenous variables. - Use of cash buffers is optional. ### Data entry specifics and timing - Key parameters entered in area A6:B14; time horizon selected in cell B6. - Template accommodates up to 10 years of strategy period. - Debt data should be latest available at end of fiscal or calendar year (example cut-off: December 31, 2017). - Base year is the year of cut-off date; the year after is analysis start year. - Users must specify fiscal/calendar years, base year, country name, domestic currency code, units for debt data (for example, millions, billions), and whether existing debt input is in foreign or local currency. ### Stylized instruments and special cases - Up to 20 stylized instruments; instruments capture maturity, grace period, currency, interest rate. - Instrument 1: assigned to AfDF-like features with unique principal repayment profile. - Instrument 20: assigned to indexed instruments allowing principal adjustment by relevant reference rates. - For variable-rate instruments use code "Var"; fixed-rate instruments use code "Fix"; T-bills use code "T-bills". - User must populate cells J7:J26 (instrument identifiers) even if unused to avoid lookup problems. - Present value discounting selectable per instrument (dropdown in F7:F26); default discount rate = 5 percent. - AT allows up to four foreign currencies plus domestic currency; currency codes must be three letters; base currency exchange rate is '1' in cell M7. --- ### ANALYSIS (model operation and scenarios) ### Process overview - For a chosen strategy and baseline market assumptions: - Aggregate new borrowing with existing debt to determine portfolio characteristics. - Compute cash flows, debt stocks, and risk indicators; total financing needs and borrowing adjust automatically. - Stress testing: - Repeat calculation under shock scenarios (exchange rate and interest rate shocks); shocks are one-off permanent percentage changes added to baseline trends. - Default shock magnitudes mentioned: first shock default 15 percent; second shock default 30 percent. - Strategy combinations: - Up to four strategies and five market rate scenarios yield 20 (4*5) sets of complete future cash flows saved. ### Execution mechanics - The Excel Macro automates selection of market inputs, a single strategy, and cash buffer targets, then runs cash flow calculations using standard Excel functions (CONCATENATE, HLOOKUP, IF, LEFT, MAX, MIN, RIGHT, SUMIF, SUMPRODUCT, VLOOKUP). - New debt cash flows generated in New_Debt (Original Currency) and New_Debt (Domestic Currency) worksheets; combined with existing debt in Total Debt worksheet; outputs saved into Strategy 1–4 worksheets. - Strategy worksheets are hidden by default and unhidden by Macro buttons from the READ ME worksheet. --- ### OUTPUTS (content and interpretation) ### Principal outputs and presentation - Typical outputs: - Size and composition of debt stocks and flows. - Cost and risk indicators. - Redemption profiles. - Output worksheets include: - Summary Results, Tables, Charts, Risk Indicators, Redemption, Pricing, Strategy 1–4 Results, New Debt (Original Currency), New Debt (Domestic Currency), Total Debt. - Summary Results lists scenarios per strategy: - Baseline interest rate; - Small interest rate shock; - Large interest rate shock; - Large exchange rate shock; - Combination of small interest rate and small exchange rate shock. ### Cost-risk presentation and interpretation - Cost vs risk visualization: - Scatter plot: y-axis = cost under baseline; x-axis = risk measured as difference between baseline and most-extreme shock scenario. - Stacked column bars: baseline cost with marginal additional cost under most-extreme cost scenario. - Users must consider absolute costs under baseline and shocks when interpreting risk; primary debt management objectives (e.g., domestic market development) may justify strategies not optimal on cost-risk grounds. ### Risk indicators and redemption profiles - Risk Indicators worksheet: year-by-year evolution of standard debt indicators for all strategies under baseline; indicators requiring cashflow-to-maturity reported only at end of strategy period. - Redemption worksheets: annual redemption profiles by currency and interest type for each strategy. --- ### STRATEGY DESIGN AND OPERATIONAL DETAILS ### Defining strategies and operational targets - Strategies defined in three stages; first stage is an operational target (row 29 of Strategy worksheet). Dropdown options (cells O29, AB29, AO29, BB29): - External borrowing as a fraction of gross borrowing requirement; - Net domestic financing as percentage of GDP; - Net domestic financing in millions of local currency; - Gross external borrowing in millions of base currency unit. - Values must be entered for all years of strategy; alternative financing amount computed as residual. ### Allocating across instruments and cash buffers - After operational target, define fraction of borrowing across instruments: - For Strategy 1 enter instrument percentages in Q56:Z75; sums: domestic instruments = 100 percent; external instruments = 100 percent; total across all instruments = 200 percent. - Converted strategy percentages reported in Q6:Z25 for Strategy 1 (analogous ranges for Strategies 2–4). - Cash buffers: - Can be built from new borrowing starting in first year; targets entered in local currency in Q30:Z30 (Strategy 1) and corresponding ranges for other strategies. - Existing cash buffer at start of strategy entered in cell E36 of Macro and Market Data worksheet. - Net borrowing requirement for cash buffer in first year = target − existing level; subsequent years = differences between cash buffer targets. - Preexisting cash buffers should not be entered in strategy ranges. ### Liability Management Operations (LMOs) - AT can analyze buybacks and exchanges of existing debt (not new debt). - Market prices for instruments to be bought back/exchanged must be determined externally. - Conceptual steps: adjust existing debt cash flows for source instrument and increase financing requirement (buyback) or allocate increase to destination instruments (exchange). - Example mechanics include use of binary control cell E47 and optional financing cell F50 to reflect buyback cost and the required adjustments to input rows. ### Running the model - Enable macros and press “Run all strategies” button (cell A26/A28) to run strategies under baseline and alternative scenarios. - Individual strategy macros available; some Excel versions may require running macro via View/Macros. --- ### QUARTERLY ANALYTICAL TOOL (QAT) — key differences - QAT uses quarterly cash flows; useful for short-term securities with maturities < 1 year. - Key differences: - Strategy period maximum 5 years; - Maturities and grace periods entered in quarters; coupon frequency specified (annual = 4, semi-annual = 2, quarterly = 1). - Macro data entered quarterly; exchange rate projections entered quarterly (example: four quarters of 2 percent gives annual 8.24 percent). - Operational and cash buffer targets require quarterly entries; net domestic financing as percentage of GDP operational target not available. - QAT outputs mirror MTDS AT but at quarterly frequency; additional worksheets Strategy 1QCF–Strategy 4QCF show quarterly cash-flow equivalents. --- ### ANNEX H AND OTHER ANNEXES — practical extensions and examples ### Annex B — summary of main changes (MTDS AT 2015 → current) - Representative instruments: maximum increased from 15 to 20; interest type now includes Variable, Fixed, T-bills. - New optional features: Non-budgetary sources (Annex C), Non-budgetary expenses (Annex D), Cash buffers, Indexed Instruments. - Inputs and guidance reorganized into READ ME, Instructions, and List of Outputs. ### Annex C — Non-budgetary funding sources - Allows inclusion of non-budgetary sources to reduce gross borrowing requirement (example: deposits UTP 5,000 million, available UTP 1,250 million per annum). ### Annex D — Non-budgetary expenses and contingent liabilities - Mechanism to include contingent liabilities cash flows and model probability of default (PoD) via Row 27 and Row 31. - Example PoD mappings: - Strategy 1 — Low PoD — 0% - Strategy 2 — Low-Medium PoD — 30% - Strategy 3 — Medium-High PoD — 60% - Strategy 4 — High PoD — 87% ### Annex E & Annex F — known disbursements and quantitative restrictions - Annex E: convert known loan disbursement schedules into strategy percentages with numeric example for USD instruments (derived strategy percentages such as 40.41, 38.40, 40.00, 12.11 for USD_1 across years). - Annex F: iterative approach to enforce quantitative domestic market caps and derive instrument percentages and nominal issuance (example domestic issuance in millions of UTP: UTP_11 T-bills 2018 4,292; 2019 4,108; 2020 4,622; 2021 6,474). - Illustrative gross financing and operational target numbers: - Primary deficit (Row 56): UTP 5,746 (cell F56) - Interest payment: UTP 1,819 for 2018 - Amortization: UTP 6,359 for 2018 - Gross financing for 2018: UTP 13,924 - External financing operational target (US$339 million ≡ UTP 5,339 million) → Gross domestic financing residual UTP 8,584 (cell Q43) ### Annex G — single residual instrument approach - Use a single instrument (e.g., T-bills) as residual financing to translate strategy into annual borrowing plan when gross financing needs are endogenous. ### Annex H — LMOs (additional numeric example) - Example market value for buyback: UTP 51 million; required adjustment steps described to include buyback cost and modified existing debt cash flows. ### Annex I — T-bills annualization and recording - Method to derive annualized yields and interest payments from T-bills with examples where totals produce Interest Payments 288 and Cash Raised 3,831 under different scenarios. ### Annex J — SDR-denominated debt treatment - Convert SDR amounts to major currency using SDR conversion rate (example: XDR 315 million = 449 USD using multiplier 1.424). ### Annex K — PPP-based exchange-rate projection (Utopia example) - PPP formula: S_{t+1} = (1 + Π_UTP) / (1 + Π_US) * S_t - Projected exchange rates (UTP/USD) 2017–2027 provided, including: - 2017 15.00; 2018 15.82; 2019 16.54; 2020 17.28; 2021 18.12; 2022 19.02; 2023 19.96; 2024 20.95; 2025 21.99; 2026 23.08; 2027 24.23 - Derived depreciation/appreciation percentages listed (e.g., 2017 5.50; 2018 4.51; 2019 4.49; 2020 4.85; 2021 4.96; subsequent years 4.96). ### Annex L — building a domestic yield curve (illustrative) - Method: start from U.S. Treasury par yield curve Y_USTreas,t_m, add sovereign credit spread δ_country,t_m, then add depreciation rate e_t to obtain domestic-currency borrowing cost. - Illustrative numeric example (Dreamland / Utopia): - Credit spread: 437 basis points (4.37 percent) - U.S. 10-year par yield (Nov 16, 2017): 2.383 percent → 238.3 bps - USD borrowing cost (10-year) = 675.3 bps - Depreciation adjustment: 550 bps - Domestic-currency borrowing cost estimate (10-year) = 1225.3 bps --- ### PRACTICAL RECOMMENDATIONS FOR USERS (implied by manual content) - Use the AT for quantitative cost-risk comparisons across candidate borrowing strategies, but incorporate qualitative objectives (for example, domestic market development) and DSA outputs before finalizing a DMS. - Populate inputs carefully: - Prepare existing-debt principal and interest schedules outside the AT when aggregating loan-by-loan data. - Enter interest-rate reference rates, spreads, and shock scenarios for at least 10 years; interest rates beyond year 10 remain at year 10 levels. - Populate all instrument identifier cells J7:J26 and currency code cells to avoid lookup errors. - When short-term securities dominate (maturities < 1 year), prefer the Quarterly AT (QAT) for accurate cash-flow timing. - Apply Annex guidance for special circumstances: - Use Annex C for non-budgetary funding sources and Annex D for non-budgetary expenses. - Use Annex E/F/G for known disbursements, quantitative restrictions, or residual-instrument approaches. - Model LMOs (buybacks/exchanges) by adjusting existing debt cash flows and explicitly adding buyback market value to financing needs. - Use Annex L methodology to construct domestic yield curves when domestic yield data are sparse, remembering to consider liquidity premium and data limitations. *Source: tnmea2019002 — INTRODUCTION and Analytical Tool manual sections.* ### INTRODUCTION _________________________________________________________________________________ 7 ### INTRODUCTION ### Purpose and role of debt management - Primary aim: raise the required amount of funding at the lowest possible cost over the medium to long run, consistent with a prudent degree of risk. - A Debt Management Strategy (DMS) is a plan for the evolution of the public debt portfolio that operationalizes debt management objectives given constraints and government preferences on cost-risk trade-offs. - A DMS is one guiding component of public debt management; other components include tactical decisions, coordination with other public sector policies, communication and accountability, and operations. - A DMS feeds into borrowing plans and the budgetary framework and has macroeconomic implications (for example, balance of payments). - Debt managers are responsible for ensuring financing constraints do not lead to sharp reversals in fiscal policy or spikes in interest costs from realized market or contingent liability risk. - Sound debt management contributes to macrofinancial stability, complements fiscal and monetary policy, and supports market and institutional development. ### MTDS framework and the Analytical Tool (AT) - The World Bank (WB) and International Monetary Fund (IMF) developed a systematic framework: the Medium-Term Debt Management Strategy (MTDS). - The MTDS framework comprises: - A methodology published as “Guidance Note for Developing a Medium-Term Debt Management Strategy” (February 2009). - An associated Analytical Tool (AT) to assess cost-risk trade-offs of alternative strategies. - The AT described in this manual is the MTDS Analytical Tool based on annual cash flow; discussion also applicable to the quarterly cash flow version (differences in Annex A). - The manual should be used in conjunction with the Microsoft Excel-based AT. ### Steps in designing a strategy (indicative sequencing) - Identify objectives for public debt management and scope of the MTDS and MTDS analysis. - Identify current debt management strategy and analyze cost and risk of existing debt. - Identify and analyze potential funding sources, including cost and risk characteristics. - Identify baseline projections and risks in fiscal, monetary, external, and market areas. - Review key longer-term structural factors. - Assess and rank alternative strategies on the basis of the cost-risk trade-off. - Review implications of candidate strategies with fiscal and monetary authorities and for market conditions. - Submit and secure agreement on the debt management strategy. ### Role of the AT relative to qualitative analysis and DSA - The AT conducts quantitative analysis of alternative borrowing strategies by assessing cost-risk trade-offs; it performs step 6 of the MTDS framework by using information from steps 1–5. - Quantitative AT results should not be the sole determinants of strategy choice; essential qualitative factors (for example, domestic market development) not captured by the AT must be considered. - The AT complements qualitative analysis and provides cost-risk characteristics of preferred strategies. - The AT is not a debt sustainability analysis (DSA) tool: - The DSA incorporates (a) fiscal policy assumptions, (b) existing debt profile, and (c) future financing assumptions to assess debt sustainability. - Interest payments and amortizations from the chosen DMS should feed into the DSA. - Shocks required for undertaking DSA are not included in the MTDS AT. *Box notes and references present in the source: “The Medium-Term Debt Management Strategy: An Assessment of Recent Capacity Building”, July 10, 2017; “Developing a Medium-Term Debt Management Strategy (MTDS)—Guidance Note for Country Authorities.” February 24, 2009.* --- ### OVERVIEW OF THE ANALYTICAL TOOL ### Purpose and high-level functionality - The AT assesses and compares cost and risk characteristics of potential debt management strategies. - Debt management strategies are combinations of new borrowing across a range of stylized debt instruments with varied financial characteristics designed to meet gross financing needs over a strategy period. - The AT shows how the composition of new borrowing influences the debt portfolio over the strategy period and how costs change under different interest and exchange rate assumptions. ### Key limitation - The AT should not be used to analyze debt sustainability; users should employ the appropriate DSA tool (LIC-DSF or MAC DSA depending on income category). ### Main components - The AT has three main components: - Inputs - Analysis - Outputs ### Capacity constraint on instrument types - The AT allows use of a maximum of 20 types of debt instruments (see section 4.2). This requires aggregation of individual loans and debt securities into twenty stylized debt instruments based on financial characteristics. --- ### 2.1 Types of Inputs - Four types of inputs: 1. Existing debt data - Divided into principal and interest payments cash flows. - Used to calculate cost and risk indicators of the starting debt portfolio and over the strategy period including new debt. - Note: Unless the debt portfolio is entirely very short-term debt, some existing debt will still be outstanding at the end of the strategy period. 2. Fiscal aggregates and debt service - Primary deficit = difference between revenue and primary expenditure (primary expenditure and revenue are exogenous to the model). - Forecasts of revenues and primary expenditures over the whole strategy period are required. - Total financing needs = primary deficit + interest payments + debt amortization. - Future interest payments and amortization are calculated within the model as they vary with instrument composition and terms. 3. Borrowing strategies - Specified as the proportion of financing need met through different stylized debt instruments (see ¶60). - Terms (borrowing rates and amortization) of each instrument are specified to allow calculation of debt service over the strategy period. - The model iteratively computes year-by-year interest and amortization cash flows, updates gross financing needs, and applies the next year’s strategy until the end of the strategy period. 4. Interest and exchange rate assumptions - Entered as exogenous variables. - User must enter both a baseline scenario and a range of shock scenarios for each variable. - These are used to compute cost and risk of strategies under different scenarios. - Interest rates (baseline and shock) on cash buffers are also entered as exogenous variables. - While the AT allows consideration of cash buffers, their use is optional. --- ### 2.2 The Analysis in Brief - Process steps the AT uses to combine inputs and produce outputs: - Calculate debt cash flows under a given financing strategy and baseline interest and exchange rate assumptions: - Aggregate new borrowing with existing debt to determine total portfolio characteristics during the analysis period. - Compute cash flows, debt stocks, and risk indicators for the total debt portfolio under the strategy; save results as output (e.g., Strategy 1 under baseline pricing). - Total financing needs and borrowing adjust automatically to reflect interest and amortization under the strategy. - Calculate debt cash flows for the same financing strategy under shock scenarios: - Repeat process for different exchange rate or interest rate shocks. - Changes in projected cash flows drive changes in total financing needs and borrowing. - Save results for same strategy under specific stress tests (e.g., Strategy 1 under currency depreciation). - Calculate debt cash flows under different strategies: - Repeat for up to four strategies (Strategy 1, 2, 3, 4) with the same baseline and shocks; save outputs. - Combinations computed: - Up to four strategies and five market rate scenarios yield 20 (4*5) sets of complete future cash flows, each saved. --- ### 2.3 Synopsis of Outputs - Outputs enable comparison of potential debt management strategies based on costs and risks of resulting debt portfolios and cash flows. - Typical outputs include: - Size and composition of debt stocks and flows. - Cost and risk indicators. - Redemption profiles. --- *Source: tnmea2019002 - INTRODUCTION (IMF manual section). The content above reproduces the structure, terminology, and numeric values as presented in the source.* ### 11. The output of the AT presents debt levels and standard debt portfolio risk exposure ### 11. The output of the AT presents debt levels and standard debt portfolio risk exposure ### Purpose and comparative outputs - The AT presents debt levels and standard debt portfolio risk exposure indicators under the four strategies to allow users to compare potential borrowing strategies taking account of associated costs and risks. - Comparisons of the cost of different strategies are presented under both the baseline (cost) and the most-extreme shock scenario (maximum risk). - These outputs allow comparison recognizing that the lowest cost strategy may differ under various interest and exchange rate assumptions and vary across cost definitions. - The AT enables assessment of how different strategies perform in managing or reducing risks that are of particular importance. ### Components of the tool (overview) - The AT is an Excel-based model; all steps and formulas are visible and can be followed and interpreted by the user. - An Excel Macro automates simulation and repetitive copying and pasting required to compare outputs across combinations of strategies and scenarios. - The AT calculates basic cost and risk indicators for different combinations of debt strategies and interest and exchange rate assumptions, stores outputs, and shows comparisons between outputs. ### Inputs (3.1) - Input data is entered into three separate worksheets identified by yellow tabs: - Existing Debt worksheet: current debt portfolio summary using stylized debt instruments. - Macro and Market Data worksheet: macroeconomic projections and projected interest and exchange rates under baseline and shock scenarios. - Strategy worksheet: financing strategies and cash buffers (external-domestic mix of new financing; composition of new external and domestic financing from stylized instruments; option to add cash buffers). - Stylized debt instruments: - The tool allows the use of 20 stylized debt instruments, including existing debt and any new borrowing instruments; the 20 available instruments cover both existing and new instruments. - Stylized instruments capture key financial features (for example, maturity, grace period, currency, interest rate). - Data entry specifics: - Key parameters are entered into area A6:B14; the user specifies a time horizon for the MTDS from the drop-down menu in cell B6. - The template can accommodate up to 10 years of strategy period. - Debt data should be the latest available at the end of fiscal or calendar year; example cut-off date: December 31, 2017. - Base year is the year of the cut-off date; the year immediately following is the analysis start year. - Users must specify fiscal or calendar years, enter base year, country name, domestic currency code, units for debt data (for example, millions, billions), and whether existing debt data input is in foreign or local currency (choice does not affect output). - Special stylized instruments: - Instrument 1: assigned to the African Development Fund (AfDF) with a unique principal repayment profile. - Instrument 20: assigned to indexed instruments allowing principal to be adjusted by relevant reference rates. - There is no minimum number of instruments to use; countries with simple portfolios may use few instruments. - Instrument 20 design can be applied to other indexed instruments; AT does not endogenize second-round macro effects. ### Analysis (3.2) - Execution flow when the AT is run: - The Excel Macro selects market inputs (for example, baseline exchange and interest rates), a single strategy from the four alternative choices, and cash buffer targets, and applies selections. - The AT uses standard Excel functions and cash flow formulas to calculate total cash flows for the selected strategy under baseline and shock exchange rate and interest rate scenarios. - Cash flows from new borrowing are calculated in new debt worksheets; cash flows of existing debt are calculated in the Existing Debt worksheet. - New debt worksheets: New_Debt (Original Currency) and New_Debt (Domestic Currency). - The cash flows of existing and new debt are combined in the Total Debt worksheet; the Excel Macro saves the output from Total Debt in the relevant strategy sheet (for example, Strategy 1). - The output in each strategy worksheet is the full cash flow information for that strategy under baseline and shock assumptions, taking into account cash buffer assumptions. - Scope of automated runs: - The AT repeats calculations for the other three strategies under baseline and shock scenarios. - Overall, the Excel Macro will calculate 20 combinations of borrowing strategy and macro-market scenario. - Cash flows for every combination are saved in Strategy 1, Strategy 2, Strategy 3, and Strategy 4 worksheets; these worksheets are hidden by default and can be unhidden with Macro buttons from the READ ME worksheet. - Standard Excel functions used in the AT: CONCATENATE, HLOOKUP, IF, LEFT, MAX, MIN, RIGHT, SUMIF, SUMPRODUCT, and VLOOKUP. ### Outputs (3.3) - Output worksheets and content: - Summary Results worksheet: key outputs regarding cost and risk indicators (also available in detail in other sheets). - Tables worksheet: key variables for debt stocks and flows, standard risk indicators at the end of the strategy period and each year in the strategy period; shows modeled shock impacts on cost under different strategies. - Charts worksheet: cost-risk results from strategy simulations as charts derived from Tables worksheet. - Risk Indicators worksheet: cost and risk indicators for all strategies under baseline scenarios at the end of the strategy period; organized by strategy and by risk indicators. - Redemption worksheets: redemption profiles at the end of each year of the strategy period for every strategy, presented by currency and interest type. - Pricing worksheet: pricing assumptions depicted. - Summary Results lists scenarios per strategy: Baseline interest rate; Small interest rate shock; Large interest rate shock; Large exchange rate shock; Combination of small interest rate and small exchange rate shock. - After running the AT: - Strategy worksheets (Strategy 1–4) and calculation worksheets (New Debt (Original Currency), New Debt (Domestic Currency), and Total Debt) become unhidden; users can re-hide using hide/unhide Excel-Macros in READ ME worksheet. ### Modus operandi and user process (4.1) - Required steps to run the AT: - Enter all required information into input worksheets. - Run the strategies by pressing the ‘Run all strategies’ button in the Strategy worksheet. - Evaluate the output tables and charts. - If adjustments are needed to a strategy or to other data (existing debt, macro, or financial—baseline or shock scenario), enter new inputs and rerun the Excel Macro by pressing “Run all strategies”. - Worksheet ordering and guidance: - Worksheets are ordered left to right beginning with guidance worksheets: READ ME, Instructions, List of Outputs, and Input Area Visual Maps. - List of Outputs and Input Area Visual Maps are hidden by default and can be unhidden using Macro buttons from the READ ME worksheet. - Users are strongly advised to read green colored worksheets before starting to use the AT. ### Worksheets, color coding, and specific guidance (4.2 and Box 2) - Color-coding rules: - Bright yellow cells: must be filled in or updated. - Pale yellow cells: can be used to enter additional working data and calculations; entries here will not be automatically picked up by the AT. - Non-colored cells: contain formulas or are used by the model and should not be edited. - Green cells: denote consistency checks. - Purple cells: used for copying and pasting values by the Excel Macro; alternative financing strategies and baseline/stress scenarios are copied here and will be overwritten by running the Excel Macro. - Grey cells: include formulas or areas used by the model and must not be changed by the user. - READ ME and Instructions: - READ ME provides important information and guidance including color coding rules and all automated Excel Macro. - Instructions include references to data input areas and key MUST Do’s and Don’ts. - Purpose of key worksheets (selected): - Existing Debt (Inputs): country and currencies, units, strategy period, starting year, stylized instruments for existing and new debt, and existing debt data; includes debt service cash flows of existing portfolio. - Macro and Market Data: input macroeconomic framework and exchange and interest rate assumptions; empty space for further analysis/assumptions. - Strategy: input proportions of financing needs from stylized instruments, cash buffer targets, and operational targets. - New Debt (Original Currency) and New Debt (Domestic Currency): used by Excel Macro to calculate new debt cash flows and convert to domestic currency. - Total Debt: sum of existing debt and new debt cash flows. - Strategy 1–4 Results: saved output of cash flows for each strategy under baseline and shock scenarios. ### The “Existing Debt” worksheet details (4.2.1) and stylized instruments - Structure of Existing Debt worksheet: - Five sections: (i) Key Parameters & Instruments; (ii) Cost Risk Indicators for Existing Debt; (iii) Existing Debt Cash Flow from Database; (iv) Existing Debt Cash Flow in Original Currency; and (v) Existing Debt Cash Flow in Domestic Currency. - Stylized instruments guidance: - Names and parameters entered in area C7:J26. - Stylized instruments represent financing terms for (i) existing portfolio instruments continuing through the strategy period; and (ii) new debt instruments considered over the strategy period. - Maximum of 20 stylized debt instruments. - Users should refer to Government Finance Statistics Manual 2014 for guidance on financial assets and liabilities. - Two instruments with specific assignments: - Instrument 1: restricted by default to AfDF-like features; color white and parameters not changeable unless user modifies formulas (instructions provided for deleting *30*2% in New Debt (Original currency) worksheet and copying revised formula across columns). - Instrument 20: allows principal adjusted by reference rates for indexed instruments; users can override currency code IDX and enter relevant codes and exchange rates in cells L11 and M11 in Existing_Debt worksheet; additional formula adjustments for cell F177 to equal F131+F154+F201 and copied across to BM177. - Indexed instruments notes: - For inflation-adjusted principal indexed instruments: choose “Var” from drop-down for interest type and enter real interest rate projections as credit spread in Macro and Market Data worksheet (E154:O154), leaving reference rates empty (E131:O131); reference rates should be entered in exchange rate depreciation area cells F85:O85. - For indexed instruments where indexation applies to coupon only: enter reference rates in reference row (E131:O131) leaving exchange rate depreciation area F85:O85 empty. - Instrument 20 can be designed for instruments indexed to inflation, GDP, commodity price-linked bonds, etc., but AT does not capture second-round macro effects; users can mimic second-round effects by creating similar instruments with different market rates and comparing strategies. *Source: tnmea2019002 - 11. The output of the AT presents debt levels and standard debt portfolio risk exposure* ### 36. Instruments’ identifier and financing terms (currency, grace period, and final maturity) ### 36. Instruments’ identifier and financing terms (currency, grace period, and final maturity) ### Instrument identification and financing-term inputs - Instrument identifier and financing terms (currency, grace period, and final maturity) must be entered by the user. - Instrument type codes: - Variable rate instruments: "Var" - Fixed rate instruments: "Fix" - Treasury Bills: "T-bills" - The tool supports up to 20 stylized instruments; all cells in J7:J26 need to be populated even if there are no corresponding debt instruments (Box 5). - For domestic-currency denominated debt instruments use the code in cell B11; for foreign-currency denominated debt instruments use the appropriate codes entered in cells L7:L10. - The tool will mark local currency debt as “DX” and other currencies as “FX” in column I. ### Present value and discounting (Box 4) - The user must specify whether a discount rate should be applied when calculating present value by selecting ‘yes’ or ‘no’ from the dropdown menu in cells F7 to F26 (Box 4). - Discounting can be used to assess concessionality of representative instruments and calculate present value of debt service repayments. - Borrowing from non-official creditors (debt securities, retail debt, and bank loans) should be assumed to have no concessionality and therefore should not be discounted. - Loans contracted from the official sector—both bilateral and multilateral—should be discounted to assess concessionality. - The discount rate is set by default at 5 percent. ### Currency assignment and exchange-rate inputs (Box 5; Exchange Rates) - The MTDS AT allows for up to four foreign currencies plus domestic currency (cells L7:M10, and L12:M12; domestic currency identified in M12). - Currency codes must consist of three letters. - Exchange rates are initially expressed in units of foreign or domestic currency per base currency (cells M8 to M10, and M12); the base currency exchange rate is '1' in cell M7. - Exchange rates in units of domestic currency per unit foreign currency are calculated by the AT (cells N7 to N12). - Users must specify currencies for unused instruments; even if not modelling all 20 instruments, cells J7:J26 should be filled with arbitrary currency codes to avoid lookup problems (Box 5). - Exchange rate information is added in area L7:M10, and L12:M12, with each stylized instrument assigned a currency in J7:J25. - Future exchange rates are derived by applying projected rates of depreciation/appreciation entered in the Macro and Market Data worksheet. ### Existing debt: data inputs and cash-flow projections - Existing debt is input as up to 20 stylized instruments, aggregated and entered in millions of base currency units (aggregation can be in foreign or local currency). - Required existing-debt inputs: - Projections of principal payments (up to maturity): cells F55:BM74. - Projections of interest payments: - Full projected interest payments for fixed-rate debt. - For variable-rate debt, enter the interest payments arising only from the spread component (see Box 7). Interest payments are entered in cells F101:BM120. - For T-bills, imputed interest payments in the first year should be entered. - Debt outstanding at the end of the base year are calculated in area E78:E97. - The data (debt outstanding, principal repayments, interest payments) must be prepared outside the MTDS AT by aggregating loan-by-loan data from the debt database. ### Interest rates: reference rates, spreads, and shocks (Box 7, Box 10, Box 11) - Fixed-rate interest payments: calculated by previous-period outstanding multiplied by the fixed interest rate. - Variable-rate interest payments: when preparing existing debt data, annual interest payments should be calculated by multiplying previous-year debt outstanding by the spread only; reference rates are entered in Macro and Market Data and added by the AT. - Interest rate projections are entered under baseline and two shock scenarios. - Interest rate components: - Reference interest rate (for example, U.S. Libor) - Risk spread representing the premium above the reference rate - An additional shock component (zero under baseline or positive under shock scenarios) - Interest rate projections must be entered for a period of 10 years in addition to the base year; for periods beyond the strategy period, interest rates are assumed to remain at the last year of the strategy period. Interest rates beyond year 10 are assumed to remain at the same rate as in year 10 (Box 11). ### Exchange-rate shocks and scenario mechanics (Box 6, Box 9) - Baseline exchange rate assumptions are entered in cells F81:O86 as percentage changes from base year exchange rates. - A positive number implies nominal depreciation of the domestic currency against the foreign currency; a negative number implies nominal appreciation. - The AT allows the specification of two shock scenarios in addition to the baseline. Shock years are specified in cells R81:R86 and AF81:AF86. Shock percentages are specified in S81:S86 and AG81:AG86. - Shock behavior: - A percentage change entered for a shock in a given year is sustained into the future as a one-off but permanent nominal appreciation or depreciation; shocks are added to baseline percentage changes so baseline trend is maintained. - Default shock magnitudes mentioned: - First shock default: 15 percent (simulated in combination with moderate interest rate shock). - Second shock default: 30 percent (reported as the only foreign exchange rate shock that is reported as a stand-alone shock). ### Macro and Market Data worksheet: macro and market assumptions - The Macro and Market Data worksheet has two sections: (i) macroeconomic (fiscal) information; and (ii) market rates (exchange rates and interest rate projections). - The user inputs baseline medium-term macro-framework in yellow cells in area E4:Q5, E7:Q9, E15:Q21, E27:Q33 and cell E36. - Cell E36 is reserved for the level of existing cash buffer available at the start of the strategy period (enter in local currency); cash buffers in foreign currency should be converted into local currency. - The AT calculates gross financing needs as the sum of: - (i) the primary deficit (public sector primary expenditure minus total revenue including grants), - (ii) interest payments on existing and new debt, - (iii) principal payments on existing and new debt. - Interest and principal payments on new debt are endogenously generated based on the borrowing strategy and exchange/interest-rate scenarios. ### Cash-flow reporting and conversion - Existing Debt Cash Flow in Original Currency: reports cash flows in the currency assigned to the instrument using end-of-base-year exchange rates (L7:L12). Fixed-rate interest payments are converted using end-of-base-year rates; floating-rate interest payments combine spread-based figures with market reference rates in Macro and Market Data. - Existing Debt Cash Flow in Domestic Currency: reports the above cash flows converted into domestic currency using exchange rate projections based on projected depreciation rates entered in Macro and Market Data worksheet, area F81:O86; derived exchange rates are in area E92:BM97. ### Strategy worksheet (overview) - In the Strategy worksheet, the user defines four alternative financing strategies to satisfy the gross borrowing requirement. - A strategy specifies the proportion of total gross financing need to be met from each of the 20 stylized debt instruments in each year of the projection period. *Source: tnmea2019002 - 36. Instruments’ identifier and financing terms (currency, grace period, and final maturity)* ### 56. Strategies are defined in three stages. First, an ‘operational target’ is defined in row 29 of ### tnmea2019002 - 56. Strategies are defined in three stages. First, an ‘operational target’ is defined in row 29 of ### Operational Targets - The operational target determines the external-domestic financing mix for each strategy (row 29 of the Strategy worksheet). - Dropdown menu options for defining the operational target are available in cells O29, AB29, AO29, and BB29. Options are: - External borrowing as a fraction of gross borrowing requirement; - Net domestic financing as percentage of GDP; - Net domestic financing in millions of local currency; and - Gross external borrowing in millions of base currency unit. - Values for the operational target must be entered for all years of the strategy as percentages or millions of currency units in the cells immediately to the right of the dropdown. - The alternative financing amount is computed as the residual (example: if operational target = 50 percent external, then 50 percent domestic). ### Components of External and Domestic Borrowing - After selecting the operational target, the user defines the fraction of borrowing allocated across instruments. - For Strategy 1, instrument percentages are entered in Q56:Z75 using simple percentages to distribute the gross financing need. - Requirement: sum of domestic instruments = 100 percent; sum of external instruments = 100 percent; total across all instruments = 200 percent. - Percentages in Q56:Z75 are expressed in terms of domestic borrowing and converted to percentages of total borrowing by: - multiplying external instrument proportions by Q52 (share of total external in total borrowing), and - multiplying domestic instrument proportions by Q53 (share of total domestic in total borrowing). - Converted results are automatically reported in Q6:Z25 for Strategy 1. Equivalent ranges for other strategies: - Strategy 2: AB56:AM75 (converted reported AB6:AM25), - Strategy 3: AO56:AZ75 (converted reported AO6:AZ25), - Strategy 4: BB56:BM75 (converted reported BB6:BM25). - The Excel Macro copies the four strategies into range D6:M25 (purple cells) one by one across scenarios and saves results in Strategy 1, Strategy 2, Strategy 3, and Strategy 4 worksheets. ### Cash Buffers - User may build cash buffers from new borrowing starting in the first year by setting cash buffer targets for each year in the strategy period; cash buffers are available for drawdown in subsequent years and can be used to reduce gross financing or buy back debt instruments. - Cash buffers are entered in local currency and in the units specified in the Existing Debt worksheet (cells B11 and B12). Example unit: millions of UTP. - Entry ranges for cash buffers: - Strategy 1: Q30:Z30, - Strategy 2: AD30:AM30, - Strategy 3: AQ30:AZ30, - Strategy 4: BD30:BM30. - Existing cash buffer at the start of the strategy period should be entered in cell E36 of the Macro and Market Data worksheet. - Net borrowing requirement for the cash buffer in the first year = difference between target and existing level; for subsequent years = differences between cash buffer targets. - Note: preexisting cash buffers should not be entered in these ranges; they should be entered in cell E36 in Macro and Market Data worksheet. ### Additional Considerations in Determining Borrowing Strategies - Quantitative Borrowing Restrictions: - If future disbursement amounts from some creditors are known, the user may use the working area in Macro and Market Data worksheet to compute instrument borrowing percentages (Annex E). - To define strategies in nominal values rather than percentages, the model can be run iteratively (Annex F). - Liability Management Operations: - Liability management operations (buyback and debt exchanges) can be included in a strategy; results can be compared with a similar strategy without LMOs (Annex H). ### Run the Model - The Excel Macro driving the AT is initiated from the Strategy worksheet; the user must enable the macro (Box 12). - To run the AT click the button in cell A26/A28 “Run all strategies.” This runs the model with newly defined strategies under the baseline and alternative scenarios. - Notes on macros: - Some Excel versions may require running the “Run_all_strategies” macro via View/Macros/View Macros or Developer/Macros. - Individual macro buttons are available to run each strategy separately. - Box 12 instructions to enable Excel Macros: File > Options > Trust Center > Trust Center > Trust Center Settings > Macro Settings > Enable All Macros. ### The Output Worksheets (Key Outputs) - MTDS AT calculates and displays cost-risk indicators at the end of the projection horizon (up to ten years) and for each year of the analysis period. - Tables and Charts worksheet outputs (some also in Summary Results worksheet) include: - Composition of existing debt and alternative strategies: stocks and flows by instrument, including (i) proportion of new borrowing by instrument and strategy over the projection period; (ii) proportion of outstanding debt by instrument and strategy over the projection period; and (iii) gross and net borrowing in domestic and base currency and as a proportion of GDP by strategy and instrument. - Cost-risk indicator table: cost-risk indicators under the baseline scenario of the current portfolio and each strategy at the end of the projection period (Box 13). - Debt stock and composition of gross financing at the end of the analysis period for each strategy. - Cost-risk indicators for baseline and shock scenarios at the end of the projection period. - Principal Repayment and Outstanding Debt Profile at the end of the strategy period by external/domestic currency and interest type. - Annual Gross Borrowing Requirement, Net Borrowing, and Outstanding Debt: composition of gross borrowing requirement by financing sources (by instruments) for each strategy; outstanding debt, overall deficit, and gross borrowing requirement by currency and in percent of GDP. - Annual Cost-Risk Indicators (baseline and shock scenarios) displayed for all strategies for indicators: debt to GDP, interest payment to GDP, present value of debt to GDP, debt service payment to GDP, and external debt service in millions of U.S. dollars (or the main base foreign currency). - Cash-flow and time-series tables and charts of cost indicators for each strategy to analyze evolution of headline debt ratios. - The Pricing worksheet contains assumptions about exchange rates and interest rates. ### The “Risk Indicators” Worksheet - Provides year-by-year evolution of standard debt indicators for all strategies under the baseline scenario. - Indicators that require cashflow to maturity are reported only at the end of the strategy period. - Organization: - Rows 1:344 present all risk indicators organized by strategy. - Rows below are organized by cost-risk indicator to facilitate cross-strategy comparison. ### The “Redemption” Worksheets - Present redemption profiles at the end of every year of the forecast period for each strategy, allowing review of redemption profile changes over time. - Two separate sheets are available, organized by currency and interest types. ### Interpreting Cost and Risk Charts (Box 13 summary) - Two chart types present cost vs. risk at the end of the financing period: scatter plot and stacked column bar. - Scatter plot: - y-axis = cost under the baseline scenario; - x-axis = risk measured as the difference in cost between baseline and the most extreme shock scenario. - Points further right imply wider difference between baseline and most extreme shock scenario; interpretation must consider absolute costs under shocks and baseline to assess risk meaningfully. - Stacked column bar charts use the same data and visually show baseline costs with marginal additional cost under the most extreme cost scenario added. - Users should also consider primary debt management objectives (for example, developing the domestic market) which may justify a strategy not optimal on cost-risk grounds. ### Strategy Result Worksheets and New Debt Worksheets - Strategy 1–4 worksheets save results of Total_Debt worksheet for a given borrowing strategy and the five scenarios for exchange rates and interest rates, in local currency; structure identical, with sets of 400 rows used to report results for the five scenarios. - New Debt (Original Currency) worksheet: - No data entry required; MTDS AT simulates cash flows for new debt disaggregated into 20 stylized debt instruments given a borrowing strategy and projected exchange and interest rates. - Reports for each stylized instrument: initial cash inflow at issuance, subsequent cash outflows at principal and interest payment dates, debt outstanding, and present value of debt. - Cash flows depend on: (i) financing terms of the instrument; (ii) amount disbursed in the first year; and (iii) projected interest rates. - Row structure covers new disbursement, principal repayment, total debt outstanding, interest payments, debt service, and present value of debt. - New Debt (Domestic Currency) worksheet: - No data entry required; converts original currency cash flows into domestic currency and aggregates cash flows across instruments. - Total_Debt worksheet: - No data entry required; consolidates cash flows from existing debt and new debt (New Debt (Domestic Currency)) disaggregated into the 20 stylized instruments. ### Quarterly Cash Flow Analytical Tool (QAT) — Key Differences from MTDS AT - QAT is based on quarterly cash flows (useful when short-term securities have maturities < 1 year). - Input differences (high level): - Existing_Debt: - Time horizon entries still in years; maximum period for strategy is 5 years. - Maturities and grace periods entered in terms of quarters; coupon payment period specified in column I (coupon frequency: annual = 4, semi-annual = 2, quarterly = 1). - Principal and interest repayments entered in the quarter they are projected to be paid. - Macro and Market Data: - Macro data entered quarterly apart from GDP (annual projections can be distributed across 4 quarters). - Exchange rate projections: quarterly depreciations entered in F81:Y86 (example: 2 percent each quarter yields annual depreciation of 8.24 percent if entered for four quarters consecutively). - Interest rates: annual interest rates entered; cash flow calculations account for coupon frequency from Existing_Debt entries. - Operational and cash buffer targets: quarterly targets required; operational target for net domestic financing as percentage of GDP is not available. - Strategy: - Quarterly inputs required with sums for external and domestic each totaling 100 percent to aid annual borrowing plan design. - QAT output worksheets mirror MTDS AT but New_Debt (Original Currency), New_Debt (Domestic Currency), and Total_Debt are based on quarterly cash flows. - Additional worksheets: Strategy 1QCF, Strategy 2QCF, Strategy 3QCF, Strategy 4QCF display quarterly equivalent cash flow of the corresponding annual strategies. *Source: tnmea2019002 - 56. Strategies are defined in three stages. First, an ‘operational target’ is defined in row 29 of the Strategy worksheet (excerpt).* ### ANNEX B ### ANNEX B A Summary of Main Changes ### Changes to the Analytical Tool (MTDS AT 2015 Version → Current Version) - Instructions - Previous: Key instructions summarized in a single worksheet. - Current: Divided into three worksheets: - READ ME - Instructions worksheet with all input cells identified - List of Outputs (hidden by default) - Existing Debt Data - Previous: Must be entered in the choice of one main foreign currency, often USD or EUR. - Current: Option to enter either in local currency or in the choice of one main foreign currency, e.g., USD. - Representative Instruments - Previous: Maximum of 15; Interest type: Variable or Fixed. - Current: Maximum of 20; Interest type: Variable, Fixed or T-bills (to easily distinguish T-bills). - Outputs and Redemption Profile - Previous: Outputs: charts and table; Risk Indicators: Tables by strategy and then by Indicators (baseline); Redemption profile. - Current: Outputs divided into: - tables of outputs - charts - pricing charts - Redemption profile divided by: - currency mix: FX-DX - interest mix - New optional features (listed in the current manual): - Non-budgetary sources of financing (Annex C) - Non-budgetary expenses (Annex D) - Cash buffers (Manual section 4.4.3) - Indexed Instruments (Manual section 4.2.1 and Box 3) ### Changes to the accompanying instruction document (May 2012 version → Current Version) - Style and Level of Detail - Previous: Mainly gave instructions on how to populate the tool, with brief descriptions of each worksheet and key inputs. - Current: In addition to describing each worksheet and data input required, provides more detailed descriptions of each input and worksheet, the structure of the tool and links between each sheet. - Introduction and Overview - Previous: Includes text on the MTDS framework. - Current: “Overview of the Analytical Tool” — provides a synopsis of the tool, describing inputs, analysis and outputs; useful as a brief summary for readers. - Process Overview - Previous: Describes how cash flows are generated for each strategy under the five scenarios including the baseline. - Current: Renamed “Components of the Tool”; preamble to detailed instructions, describes types of scenarios and informs the user what to expect as inputs. - Getting Started - Previous: Instructs how to populate each input worksheet and describes worksheets where cash flows are derived. - Current: Renamed “The Analytical Tool: Modus Operandi”; provides detailed (a) instruction on inputting data, and (b) information on special cases and particularities. - Appendix / Annexes - Previous: An appendix gave instructions on deriving borrowing strategies under quantitative restrictions. - Current: Excluding the annex on the Quarterly Cash flow (not applicable for the previous version), there are 12 annexes providing: - Guidance on incorporation of: - non-budget borrowing sources - non-budget expenses - strategies with known loan disbursements - strategies with quantitative restrictions - incorporating Liability Management Operations - strategies with a single instrument as a residual financing - Guidance on how to: - derive the annual interest payments and rate for outstanding T-bills on existing debt - analyze SDR denominated debt (in terms of exchange rate risk) - derive exchange rate depreciation based on purchase power parity theory - derive a domestic yield curve or USD yield curve for non-regular issuers of USD international bond (Eurobond) - derive forward yield curves - A list of: - all formulae used in the AT - all Excel Macros and functions used in the AT ### Annex C — Non-Budgetary Funding Sources (key mechanics and example) - Purpose: Allow inclusion of non-budgetary sources of funding to reduce the gross borrowing requirement (e.g., deposits, asset sale proceeds). - Note: Cell E36 in the Macro and Market Data worksheet is dedicated to Existing Cash Buffers; linked with cash buffer targets in the strategy period. - Illustrative scenario (Utopia, strategy period four years, currency UTP, units in millions): - Utopia has deposits of UTP 5,000 million (not included in existing cash buffer). - Available per annum to reduce gross borrowing: UTP 1,250 million per annum. - User entries to allocate UTP 1,250 million per annum: - in cells F19:I19, enter the number 25 (which translates into 25 percent) - in Row 15, enter 5000 for the strategy period from columns F to I - Guidance: Other non-budgetary financing sources (funds available, proceeds from asset sale) should be entered similarly. ### Annex D — Non-Budgetary Expenses (contingent liabilities example) - Purpose: Accommodate inclusion of non-budgetary expenses (contingent liabilities, arrears, overdraft repayments) to assess impacts on the debt portfolio cost-risk matrix. - Caveat: MTDS AT is not suited for detailed contingent liabilities analysis; useful for assessing portfolio impact. - Illustrative contingent liabilities (in millions of UTP): - 2018: 3,000 - 2019: 1,500 - Mechanism to model probability of default (PoD): - Enter entire cash-flow of contingent liability in Row 27. - Use Row 31 to capture different probabilities of default. - Each strategy must be run separately by changing Row 31 before running. - Example strategy PoD scenarios: - Strategy 1 — Low PoD — 0% - Strategy 2 — Low-Medium PoD — 30% - Strategy 3 — Medium-High PoD — 60% - Strategy 4 — High PoD — 87% ### Annex E — Strategies with Known Loan Disbursements (conversion to strategy percentages) - Purpose: Convert known absolute loan disbursement schedules into strategy percentages for entry into Strategy worksheet (Strategy 1 Q56:Z75; Strategy 2 AD56:AM75; Strategy 3 AQ56:AZ75; Strategy 4 BD56:BM75). - Illustrative scenario (Utopia, strategy period four years, local currency UTP, base currency USD, units in millions): - Known multilateral disbursements (USD, in millions) - USD_1 ADF_Fixed: 2018 137, 2019 96, 2020 102, 2021 85 - USD_2 IDA/IFAD/EDF_Fixed: 2018 93, 2019 61, 2020 78, 2021 53 - USD_3 IBRD/ADB/IDB_Fixed: 2018 75, 2019 49, 2020 57, 2021 42 - Other expected external borrowing (USD, in millions) - USD_5 Bilateral_Fixed: 2018 34, 2019 44, 2020 18, 2021 22 - USD_7 International Bond_Fixed: 2021 500 - Operational target option: set Gross External Financing (GEF) in millions of USD and derive strategy percentages. - Example GEF (millions of USD) entered in Strategy worksheet: - 2018 (Q29): 339 - 2019 (R29): 250 - 2020 (S29): 255 - 2021 (T29): 202 - Derived Strategy Percentages (Strategy 1, cells Q56:Z65 for each instrument, in percent) - USD_1 Q56:Z56 — 2018 40.41, 2019 38.40, 2020 40.00, 2021 12.11 - USD_2 Q57:Z57 — 2018 27.43, 2019 24.40, 2020 30.59, 2021 7.55 - USD_3 Q58:Z58 — 2018 22.12, 2019 19.60, 2020 22.35, 2021 5.98 - USD_4 Q59:Z59 — 0, 0, 0, 0 - USD_5 Q60:Z60 — 2018 10.03, 2019 17.60, 2020 7.06, 2021 3.13 - USD_6 Q61:Z61 — 0, 0, 0, 0 - USD_7 Q62:Z62 — 0, 0, 0, 71.23 - USD_8–USD_10 Q63:Z65 — 0, 0, 0, 0 - Note: In this illustration, the sum for each year may not add up to 100 because of rounding. - Additional guidance: - If strategy period longer than four years and disbursement info unavailable, enter best estimates for subsequent years. - Before running strategy, percentages for domestic instruments UTP_11 to UTP_20 must be populated so their sum equals 100 percent (¶60). ### Annex F — Strategies with Quantitative Restrictions (iterative approach) - Key point: Gross financing needs depend on the primary deficit and debt service from existing and new debt portfolios; absolute issuance by instrument in later years cannot be entered directly without iterative computation. - Iterative method: - Run model for year 1, observe gross financing need. - Allocate absolute values across instruments for year 1, convert to percentages, enter into strategy cells, and re-run. - Cash flows from new borrowing in year 1 feed into year 2 financing need; repeat iteratively until all years populated with absolute values by instrument. - Illustrative assumptions for domestic securities (percent) - UTP_11 T-bills: 50.00 in 2018–2021 - UTP_12 T-Bonds 3 YR_Fixed: 20.00 in 2018–2021 - UTP_13 T-Bonds 5 YR_Fixed: 20.00 in 2018–2021 - UTP_14 T-Bonds 10 YR_Fixed: 10.00 in 2018–2021 - Resulting domestic issuance in millions of UTP (when operational target sets gross external financing as in Annex E) - UTP_11 T-bills: 2018 4,292; 2019 4,108; 2020 4,622; 2021 6,474 - UTP_12 T-Bonds 3 YR_Fixed: 2018 1,717; 2019 1,643; 2020 1,849; 2021 2,590 - UTP_13 T-Bonds 5 YR_Fixed: 2018 1,717; 2019 1,643; 2020 1,849; 2021 2,590 - UTP_14 T-Bonds 10 YR_Fixed: 2018 858; 2019 822; 2020 924; 2021 1,295 - Gross Domestic Financing: 2018 8,584; 2019 8,215; 2020 9,245; 2021 12,948 - Example with domestic market capacity restrictions (maximum appetite in millions of UTP) - UTP_12 T-Bonds 3 YR_Fixed: 3,000 each year 2018–2021 - UTP_13 T-Bonds 5 YR_Fixed: 1,500 each year 2018–2021 - UTP_14 T-Bonds 10 YR_Fixed: 750 each year 2018–2021 - Working example values and mechanics (2018 base year): - Primary deficit (Macro and Market Data worksheet Row 56): UTP 5,746 (cell F56) - Interest payment (Existing_Debt worksheet or Macro and Market Data): UTP 1,819 for 2018 - Amortization (Existing_Debt worksheet or Macro and Market Data): UTP 6,359 for 2018 - Gross financing (sum of the three above) for 2018: UTP 13,924 - External financing operational target (US$339 million ≡ UTP 5,339 million) → Gross domestic financing residual: UTP 8,584 (cell Q43 in Strategy worksheet) - Initial calculated instrument percentages imply sum of 61.16 allocated to bonds and remaining 38.84 to T-bills. - Iterative recalculation yields Gross Domestic Financing series: 2018 8,584; 2019 7,273; 2020 7,381; 2021 11,843 - Resulting instrument percentages (in percent) after iteration: - UTP_11 T-bills: 2018 38.8, 2019 27.8, 2020 28.9, 2021 55.7 - UTP_12 T-Bonds 3 YR_Fixed: 2018 34.9, 2019 41.2, 2020 40.6, 2021 25.3 - UTP_13 T-Bonds 5 YR_Fixed: 2018 17.5, 2019 20.6, 2020 20.3, 2021 12.7 - UTP_14 T-Bonds 10 YR_Fixed: 2018 8.7, 2019 10.3, 2020 10.2, 2021 6.3 ### Annex G — Using a Single Instrument for Residual Financing - Concept: Use a single instrument (e.g., T-bills) as residual financing to translate the selected strategy into an annual borrowing plan. - Rationale: Because gross financing needs depend on primary deficit and debt service (existing and new), and future gross financing amounts are endogenous, using a residual instrument allows the user to plan amounts for other instruments while leaving one instrument to absorb the balancing amount each year. *Source: ANNEX B, tnmea2019002 - ANNEX B* ### Annex F showed how borrowing requirements can be specifically set by each instrument in ### tnmea2019002 - Annex F showed how borrowing requirements can be specifically set by each instrument in ### Setting borrowing requirements using a residual instrument (illustration) - Context and assumptions: - Operational target: sets gross external financing (financing by each creditor is known). - Domestic market capacity is shallow, limiting medium and long-term bonds. - Representative instrument schedules (as presented): - (millions of USD) - USD_1 ADF_Fixed: 2018 150; 2019 100; 2020 ; 2021 - USD_2 IDA/IFAD/EDF_Fixed: 2018 150; 2019 100; 2020 150; 2021 100 - USD_3 IBRD/ADB/IDB_Fixed: 2018 100; 2019 100; 2020 ; 2021 - USD_5 Bilateral_Fixed: 2018 100; 2019 100; 2020 50; 2021 - (millions of UTP) - UTP_12 T-Bonds 3 YR_Fixed: 2018 3,000; 2019 3,000; 2020 3,000; 2021 3,000 - UTP_13 T-Bonds 5 YR_Fixed: 2018 1,500; 2019 1,500; 2020 1,500; 2021 1,500 - UTP_14 T-Bonds 10 YR_Fixed: 2018 750; 2019 750; 2020 750; 2021 750 - Procedure to use T-bills as residual financing: 1. Set operational target (gross external financing) by summing USD_1, USD_2, USD_3, USD_5: - (millions of USD) GEF: 2018 500; 2019 400; 2020 200; 2021 100 2. Convert amounts into UTP using applicable annual exchange rates: - (millions of UTP) Financing from external: 2018 7,875; 2019 6,489; 2020 3,374; 2021 1,687 3. Compute T-bill financing as residual: difference between gross financing requirement in UTP and external financing plus UTP_12, UTP_13, UTP_14 (UTP 5,250 million every year): - (millions of UTP) Financing from T-bills: 2018 799; 2019 (2,996); 2020 (2,073); 2021 4,036 4. Determine percentages for representative instruments (enter into Strategy worksheet area Q56:T75): - For USD instruments (divide amounts by operational target): - (in percent) USD_1: 2018 30; 2019 25; 2020 ; 2021 - (in percent) USD_2: 2018 30; 2019 25; 2020 75; 2021 100 - (in percent) USD_3: 2018 20; 2019 25; 2020 ; 2021 - (in percent) USD_5: 2018 20; 2019 25; 2020 25; 2021 - For UTP domestic instruments, derive aggregate domestic financing as Gross Financing minus Gross External Financing: - (millions of UTP) Gross Financing Need: 2018 13,924; 2019 8,743; 2020 6,551; 2021 10,973 - (millions of UTP) Gross External Financing: 2018 7,875; 2019 6,489; 2020 3,374; 2021 1,687 - (millions of UTP) Gross Domestic Financing: 2018 6,049; 2019 2,254; 2020 3,177; 2021 9,286 - (in percent) UTP_12: 2018 49.6; 2019 133.1; 2020 94.4; 2021 32.3 - (in percent) UTP_13: 2018 24.8; 2019 66.6; 2020 47.2; 2021 16.2 - (in percent) UTP_14: 2018 12.4; 2019 33.3; 2020 23.6; 2021 8.1 - For T-bills, UTP_11, calculate percentage as residual: 100 percent minus sum(UTP_12, UTP_13, UTP_14): - (in percent) UTP_11: 2018 13.2; 2019 (133.0); 2020 (65.3); 2021 43.5 ### Incorporating Liability Management Operations (LMOs): buybacks and exchanges (Annex H) - Capabilities and limitations: - The AT can analyze LMOs involving buyback or exchange of existing debt; it does not analyze similar operations for new debt. - Operations change existing debt composition; instruments involved should be assigned unique stylized instruments. - Market price of instruments being bought back or exchanged must be determined outside the AT. - Two-step conceptualization of exchanges: adjust existing debt cash flows for source instruments; ensure adequacy of funding by increasing financing requirement (buyback) or allocating increase to destination instruments (exchange). - Example assumptions and data points: - Representative instrument UTP_12 represents two-year and three-year bonds. - Aggregate outstanding size of the two-year as at end 2017: US$15.1 million (UTP 255.7 million). - Interest payments for years 2018 and 2019 were US$2.1 million (UTP 31.53 million). - Out of outstanding UTP 255 million, UTP 45 million is a single instrument with interest rate of 6 percent: Bond A (source bond). - Option 1: Buybacks (Bond A bought back in 2018, Strategy 3) - Market value of Bond A (determined outside MTDS AT): UTP 51 million. - Required adjustments before analyzing Strategy 3: - Change cash flow (principal and interest) of UTP_12 in Existing_Debt worksheet. - Increase gross financing requirement for 2018 by UTP 51 million. - Amending existing debt cash flow (example entries in Macro and Market Data worksheet optional area): - UTP_12 without Bond A (In millions of USD): - Principal: 2018 ; 2019 3.69; 2020 8.35 - Interest: 2018 1.92; 2019 1.92; 2020 1.19 - Bond A (In millions of USD): - Principal: 2018 ; 2019 3.00; 2020 - Interest: 2018 0.18; 2019 0.18; 2020 - Use binary control cell E47 (enter “1” or “0”) to include/exclude Bond A via multiplication (e.g., Principal =$E$47*G43). - In Existing_Debt worksheet, set UTP_12 cash flows as sum of “UTP_12 without Bond A” and Bond A area (avoid linking to Existing_Debt entries to prevent circularity). - Increasing financing requirement: - Enter market price UTP 51 million in optional cell F50 as =$E$50*51, where E50 formula is =if(E47=1,0,1) so that when E47=0 the additional financing UTP 51 million is picked up. - Ensure primary expenditure row in Macro and Market Data worksheet (row 5) incorporates cell F50 (e.g., use non-budgetary expenses row F29 =F50 and set F33 =100 percent). - Run strategies with E47 =1 first; then set E47 =0 and run Strategy 3 individually. - Option 2: Exchanges - In addition to steps in Option 1, ensure destination bond increases by at least UTP 51 million in 2018. - Example: if UTP_13 is destination bond, adjust strategy percentage (cell AQ68) so UTP_13 raises at least UTP 51 million of gross financing (cell AQ96). ### T-bills: interest payments and average yields (Annex I) - Annual cash-flow methodology and limitations: - AT uses annual cash flows; T-bills are assumed one-year maturity and issued at par with interest accruing and paid following year. - For countries relying on T-bills with maturities < six months, annual cash flow may underestimate costs and risk; use quarterly cash flow tool in such cases. - Guidance to record interest payments and annualized yield: - Users should record tenor, issuance yield rate, and cash raised when available; alternative: discount rate and face value. - Scenario A (outstanding stock recorded as cash raised, yields recorded): - Calculations shown: - 3 months: Cash Raised 1,533; Tenor (Days) 91; Yields 3.00%; Annualized Yield 12.03%; Face Value 1,578; Interest Payments 46 - 6 months: Cash Raised 1,341; Tenor 182; Yields 7.05%; Annualized Yield 14.14%; Face Value 1,435; Interest Payments 95 - 12 months: Cash Raised 958; Tenor 365; Yields 15.35%; Annualized Yield 15.35%; Face Value 1,105; Interest Payments 147 - Totals: Cash Raised 3,831; Interest Payments 288 - Scenario B (outstanding stock recorded as cash raised, discount rates recorded): - Calculations shown: - 3 months: Cash Raised 1,533; Tenor 91; Discount Rates 2.91%; Face Value 1,578; Interest Payments 46 - 6 months: Cash Raised 1,341; Tenor 182; Discount Rates 6.59%; Face Value 1,435; Interest Payments 95 - 12 months: Cash Raised 958; Tenor 365; Discount Rates 13.31%; Face Value 1,105; Interest Payments 147 - Totals: Cash Raised 3,831; Interest Payments 288 - Scenario C (outstanding stock recorded as face value, yields recorded): - Calculations shown: - 3 months: Tenor 91; Yields 3.00%; Annualized Yield 12.03%; Face Value 1,578; Interest Payments 46 - 6 months: Tenor 182; Yields 7.05%; Annualized Yield 14.14%; Face Value 1,435; Interest Payments 95 - 12 months: Tenor 365; Yields 15.35%; Annualized Yield 15.35%; Face Value 1,105; Interest Payments 147 - Totals: Interest Payments 288 - Scenario D (outstanding stock recorded as face value, discount rates recorded): - Calculations shown: - 3 months: Tenor 91; Discount Rates 2.91%; Face Value 1,578; Interest Payments 46 - 6 months: Tenor 182; Discount Rates 6.59%; Face Value 1,435; Interest Payments 95 - 12 months: Tenor 365; Discount Rates 13.31%; Face Value 1,105; Interest Payments 147 - Totals: Interest Payments 288 - Recording guidance: - Enter derived interest payments in Existing_Debt worksheet against representative instrument for T-bills. - Weighted average annualized yield (for comparing strategy horizon projections with outstanding T-bills) is calculated using cash raised and annualized yield per tenor (multiply columns (A) and (D) and divide by sum of column (A)). - For scenarios where outstanding stock is face value, compute cash raised = Face Value - Interest Payments, then yields = (Face Value - Cash Raised) ÷ Cash Raised. - Note: weighted average annualized yield differs from implied rate in Existing Debt worksheet (areas: E101:E120; E219:E238; E313:E332). ### Debt denominated in Special Drawing Rights (SDR) (Annex J) - Treatment of SDR-denominated loans: - For existing debt: look up applicable exchange rate on appropriate date to convert SDR loans into major foreign currency used in AT (e.g., USD or euro), then convert into local currency if necessary. - For potential financing: use the major currency rather than SDR, keeping in mind exchange rate risk to five currencies. - Example conversion (aggregate SDR loans XDR 315 million as at end-Dec 2017): - SDR basket weightings and exchange rates (columns reproduced): - 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 - To convert XDR 315 million into USD, multiply by 1.424 (or into euro by 1.187). - Exposure to underlying currencies in XDR (multiplying 315 by weighting): - CNY 34; EUR 97; GBP 25; JPY 26; USD 131; Total 315 - Exposure in each currency denomination (equivalents shown): - 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 equivalents (multiplying by FX per 1 USD): - 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 (i.e., 315 * 1.424 = 449 USD) ### Projecting exchange rates using PPP (Annex K) - PPP principle: nominal exchange rate adjusts by ratio of domestic inflation to foreign inflation to keep real exchange rate constant: - S_{t+1} = (1 + Π_UTP) / (1 + Π_US) * S_t - Illustrative projection for Utopia (UTP per USD) using inflation series and base exchange rate: - Projected annual inflation rates (2018–2027): - US: 2018 2.13; 2019 2.61; 2020 2.40; 2021 2.21; 2022 2.30; 2023 2.24; 2024 2.24; 2025 2.24; 2026 2.24; 2027 2.24 - Utopia: 2018 7.74; 2019 7.24; 2020 7.00; 2021 7.17; 2022 7.37; 2023 7.37; 2024 7.37; 2025 7.37; 2026 7.37; 2027 7.37 - Base exchange rate end-2017: UTP/USD 15.00 - Example: 2018 exchange rate S1 = (1.0213 / 1.0774) * 15 = 15.835 - Projected exchange rates 2017–2027 (UTP/USD): - 2017 15.00; 2018 15.82; 2019 16.54; 2020 17.28; 2021 18.12; 2022 19.02; 2023 19.96; 2024 20.95; 2025 21.99; 2026 23.08; 2027 24.23 - Derived depreciation/appreciation (entered as positive depreciation or negative appreciation) in percent: - 2017 5.50; 2018 4.51; 2019 4.49; 2020 4.85; 2021 4.96; 2022 4.96; 2023 4.96; 2024 4.96; 2025 4.96; 2026 4.96; 2027 4.96 - Notes: - PPP-based projections can be period-average or end-of-period; end-of-period is relevant for projecting end-of-period stocks. - The AT requires market input projections for 10 years; absent projections, keep rates constant beyond strategy period so AT can generate cash flows until maturity. *Source: tnmea2019002 - Annex F showed how borrowing requirements can be specifically set by each instrument in* ### Annex L ### Annex L — Building a Domestic Yield Curve ### Methodology overview - Purpose: Derive a domestic yield curve (and forward yield curves in Annex M) when observable domestic yields are not available. - Approach: Use the US Treasury yield curve as a starting point and adjust for country-specific sovereign credit spread and inflation/exchange-rate depreciation differential to obtain domestic-currency yields. - Data frequency and availability: - The Federal Reserve Board publishes Treasury yield curve estimates at a daily frequency (from 1961 to the present). The data is updated regularly with a 3-day lag; http://www.federalreserve.gov/pubs/feds/2006/200628/200628abs.html. - The website provides par and zero-coupon yield curve data. - The “Data- Excel file (30MB XLS)” contains daily yield curves for maturities of 1 to 30 years: zero coupon yield curve in columns B:AE labelled SVENY01 to SVENY30, followed by par yield curve in columns AF:BI labelled SVENPY01 to SVENPY30. - Yield to maturity rates are also available at https://www.federalreserve.gov/releases/h15/, updated daily. ### Step-by-step construction (conceptual) - Start with the U.S. Treasury par yield curve for maturity m at time t: Y_USTreas,t_m (notation as provided). - Add the sovereign credit spread for the country (δ_country,t_m) to derive the country’s USD borrowing cost for maturity m at t. - Add the depreciation rate (e_t) to convert USD borrowing cost into domestic currency borrowing cost (coupon/interest rate for a domestic bond). ### Illustrative scenario (Utopia / Dreamland example) - Scenario facts (preserve exact figures as in source): - Dreamland issued an international bond in USD of 10-year maturity on November 16, 2017 at a credit spread of 437 basis points (4.37 percent). - U.S. Treasury par yield (10-year) on November 16, 2017: 2.383 percent. - Calculations (as presented): - 10-year U.S. Treasury rate = 2.383 percent → 238.3 bps. - Add Dreamland credit spread of 437 bps: USD cost of borrowing (10-year) = 675.3 bps. - Add depreciation rate of 550 bps (see Annex K) to derive Utopia’s 10-year domestic yield: 1225.3 bps. - Key numeric results (preserved): - Credit spread: 437 basis points (4.37 percent) - U.S. 10-year par yield (Nov 16, 2017): 2.383 percent - Converted 10-year par yield in bps: 238.3 bps - USD borrowing cost estimate: 675.3 bps - Depreciation adjustment: 550 bps - Domestic-currency borrowing cost estimate: 1225.3 bps ### Formal notation and formulae (as in source) - Let U.S. Treasury par yield curve with maturity m at time t be denoted by: Y_USTreas,t_m - Let the credit spread of a country (Utopia in the example) for maturity m at time t be: δ_Utopia,t_m - Let the depreciation rate at time t be denoted by: e_t - Then: - Utopia’s cost of borrowing at time t for maturity m, in USD is given by: - Y_USTreas,t_m + δ_Utopia,t_m - That is the coupon/interest rate on Utopia’s USD bond. - Utopia’s cost of borrowing at time t for maturity m, in UTP (domestic currency) is given by: - Y_USTreas,t_m + δ_Utopia,t_m + e_t - That is the coupon/interest rate on Utopia’s domestic bond. ### Practical considerations and caveats - Credit spread data availability: - Credit spread information is likely to be sparse, typically available only for a few maturities. Common maturities for international issuance: 5-year, 10-year, 15-year, 20-year. - Users may need to assume constant maturity across the yield curve or interpolate between available maturities. - Liquidity premium: - Users should consider adding a liquidity premium to capture differences in depth and liquidity between the U.S. sovereign debt market and the country being analyzed. - Methodological note: - This annex is not advocating for this particular methodology; users with other sources are encouraged to use those. For simplicity, the illustrations assume annual coupon payments; see The US Treasury Yield Curve: 1961 to Present for methodology that considers bonds with semi-annual coupon payment. *Annex L — Building a Domestic Yield Curve (from tnmea2019002 - Annex L).* --- _Source: https://www.imf.org/-/media/files/publications/tnm/2019/tnmea2019002.pdf_