Overview

CALCULATE NET TOTAL RETURN ON BOND INVESTMENT DENOMINATED IN ANY CURRENCY,

broken-down by trading gains (realized & unrealized), foreign exchange gains (realized & unrealized), interest received & accrued, fees paid. For fixed rate and zero-coupon bonds.

Suppose you invest  €10.000 to buy a bond denominated in USD at a price of 125 and the exchange rate USD/EUR is 0,80 (Euros for 1 Dollar), by converting your €10.000  into \$12.500 (10.000/0,80) you will be able to buy \$10.000 face value (\$10000*125/100).

Suppose you sell your bond at a price of \$130, generating \$13.000, and in the meantime Euro appreciated vis-à-vis the Dollar, the exchange rate now being 0,70. Your sale proceeds converted to Euro will generate €9.100 (13.000*0,70). Since you originally invested €10.000, you realize a net capital loss of €900.

Now let’s see where this comes from:

• realized Trading Gain of \$500 [(10000 * (\$130-\$125)] which converted at 0,70 will generate €350
• realized Forex Loss of €1.250 [(\$12.500*(0,80-0,70)]
• in net terms you realized a capital loss of €900 (1.250-350)

Now, suppose the bond you invested in - expiring say July 7, 2030 - pays a coupon of 3% twice a year, on Jan. 7 and July 7. If you bought the bond say on March 10, 2019 and sold it on Jun 3, 2020, in addition to sales proceeds of \$13.000, you will get a net interest income of \$370,68 [or €259,48 (370,68*0,70)], composed as follows:

• -\$50,96 interest paid at purchase on March 10, 2019 from last coupon date of Jan. 7, 2019 (\$10000*3%*62days)/365)
• \$148,77 coupon received on July 7, 2019 (\$10000*3%*181days)/365)
• \$151,23 coupon received on Jan 7, 2020 (\$10000*3%*184days)/365)
• \$121,64 interest received at sale on Jun 3, 2020 from Jan.7, 2020 (\$10000*3%*148days)/365)

As a result, your total return on investment (ROI) will be:

• -6,41% in Euros (-900,00+259,48)/10.000)*100
• +6,97% in USD (500.00+370,68/12.500*100)
No reviews yet

## More From Domenico Cristarella

Browse our top rated business templates. See All
RETIREMENT PLANNING MODEL **ADJUSTED FOR INFLATION**
1,564
56
ESTIMATES THE EVOLUTION OF YOUR INVESTABLE ASSETS UNTIL RETIREMENT AND THROUGHOUT YOUR LIFESPAN. CALCULATES YOUR LEGACY NET WORTH, INCLUDING FUTURE…
SIMPLE ASSET ALLOCATION MODEL
3,524
175
This simple model makes it easy to determine how to best structure your asset portfolio, allowing effective risk and volatility…
5,350
237
Designed to support medium term investors who hold onto positions for days, weeks or even months. Best suited for investors…
ASSET PORTFOLIO REBALANCING TOOL (2022 RELEASE)
4,909
262
THIS TEMPLATE MAKES IT EASY TO PERFORM PORTFOLIO REBALANCING CALCULATIONS, IN ORDER TO DETERMINE QUANTITIES TO BE PURCHASED OR SOLD…
ASSETS CORRELATION MODEL
6,216
279
Achieve greater diversification in your asset allocation by using this tool to calculate the correlation coefficient between any two variables, including…
INFLATION CALCULATOR (NEW VERSION - 50 COUNTRIES)
5,900
260
This enhanced and updated version of the model includes two calculators: year-on-year inflation calculator flat rate inflation calculator The first…
See All