Calculator for foreign currency bonds.

The universal Excel calculator is designed to estimate the possible yield corridor of foreign currency bonds, including Eurobonds and replacement bonds. At the initial assessment stage, the result presented is a forecast based on the exchange rate scenario that, in the investor´s opinion, will be established during the circulation period of the security. The table uses the traditional approach to calculations. However, the unknown exchange rate in the future does not allow us to accurately assess the result. Therefore, Excel uses the yield corridor in negative and positive scenarios.

For the underlying mathematical model to work correctly, all gray cells must be filled in. In the "General Information" block, information about the name of the selected bond is entered. Its ISIN number, date of purchase and the amount of commission on the transaction amount.

At the next stage, the “Full value” of the foreign currency bond is determined. To do this, you need to enter information in the gray cells about the amount of the par value, the exchange rate on the day of purchase, the amount of accumulated coupon income and the value of the security as a percentage of the par value. All numbers will be automatically converted into rubles.

The third stage of calculations “Amount to be repaid” is final. On the “Coupon” sheet, in the gray cells, line by line you need to enter information about the current coupon period, the date of payment of such a coupon, the payment amount in foreign currency and the predicted exchange rate. Let me remind you that payments on foreign currency bonds (euro and equivalent) are made in rubles. To predict the exchange rate for the maturity of the selected bond, investors use the median according to a macroeconomic survey regularly published by the Central Bank.

Taxes on coupons and positive exchange rate differences are calculated automatically. This version of the Excel calculator can be used exclusively for foreign currency bonds (euro and equivalent) placed on the Moscow Exchange stock market. This mathematical model cannot be used for foreign currency bonds on other exchanges due to the peculiarities of double taxation with friendly and unfriendly countries. ISIN numbers for such securities begin with the letters RU.

When all the gray cells in the “Yield to maturity” block are filled in, the investor will be able to see the forecast for possible net profit. It will be expressed as an annual percentage and relates to ruble profit or loss. To obtain an accurate assessment of profitability, it is necessary to enter the actual exchange rate at which the payment was made on the “Coupons” sheet.

The use of this mathematical model does not guarantee income. The main risks when owning any security are bankruptcy of the issuing company or broker, blocking of the exchange, long-term depreciation of the nominal currency and other fundamental factors.