Accounting, Asset Management, Banking, Financial Model, Financial Services, General Excel Financial Models, Investing, Personal Budgets, Personal Finance

Accounting, Bonds, Budget, Budgeting, Calculator, CFO, Controlling, Excel, Financial Model, Financial Modeling, Financial Planning, Financial Reporting, Interest Accrual, Investors, Performance Tracking, Personal Finance, Profitability Analysis, Return on Investment (ROI)

Net total return is calculated:
• on the outstanding position
• in case of sale (total or partial)
• at expiry date (redemption)
• in trade currency (denomination currency) and in local currency
• for the present, past, or future transactions (for retrospective or forward-looking profitability analysis and simulations)

When buying an asset in foreign currency, there are 2 components:
• Trading gains/losses (difference between purchase price and sale price)
• Foreign Exchange gains/losses (delta between exchange rates at purchase and at the sale)

If, for example, you invest €10.000 to buy a bond denominated in USD at a price of, say, 125 and the exchange rate USD/EUR is, say, 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 subsequently sell your bond at a price of $130, generating $13.000. Suppose, in the meantime, the Euro appreciated vis-à-vis the dollar. The exchange rate now is 0,70. Your sale proceeds converted to Euro will, therefore, generate €9.100 (13.000*0,70). Since you originally invested €10.000, you realize a net capital loss of €900.

Now let’s analyze where this comes from:
• you realized a Trading Gain of $500 [(10000 * ($130-$125)] which converted at 0,70 will generate €350
• you realized a Forex Loss of €1.250 [($12.500*(0,80-0,70)], due to the USD devaluing
• in net terms you realized a capital loss of €900 (1.250-350)

Now, suppose the bond you invested in – expiring, say on 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 the sales proceeds of $13.000, in your holding period, 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 the 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)

Please note that this is only a Calculator; hence, you can only manipulate the Inputs but not the formulas or tables.

File Type: .xlsx (protected)