Total return index Bonds

Hi,

I am trying to model a total return index for bonds and hope someone here can help me. Hopefully someone can come up with a brilliant idea of how to do this. I have attached an example on my problem, which I will try to explain below. I completely understand that not all of you are familiar with financial expressions; thus I will try to explain as good as I can. However, please send me a reply if I am not able to this in good enough manner. No over to the very interesting problem where I need some intellectual power to solve;

Please open the attached spreadsheet.Bond total return index.xlsx

In Column A and B I have the bond price at each date (Janī12 - Aprī12). Furthermore, in column D and E I have the actual coupon payments and the date for which the coupons are paid. As you can see, the coupons are paid once a month. Also, to take the foreign exchange rate into account I have also the EURUSD FX rate in column G and H.

Here I assume that I buy a bond at 90 (%of par value) 01 Janī12 and sell the bond 14 Aprī12 at 92 (% of par value). Market value return is easy to solve, however, where it gets difficult is how to take the coupons into consideration. I want to assume that when I get a coupon paid I want to reinvest this coupon in the bond; hence buy a new bond at the current market price. In more detail, when I get paid 0.65 in coupons on 5 Janī12 I want to buy bonds for this amount at the current market price (100 - % of par value). Consequently, I now hold an higher amount of bonds, meaning that at the next coupon payment I want coupons for the current amount I now hold in bonds (original amount, plus the amount I bought for the paid coupons in January 2012). This continues going forward, i.e.; I accumulate bonds at each coupon payment, which the future coupon payments need to acknowledge.

Having done this, I also want to take the FX rate into account. Say that the bond is in EUR, but I want quote the return in USD terms. From a practical point of view, this means that when I pay the money lend to a company in EUR I firs exchange the amount from USD. When the bond is redeemed and repaid to me in EUR I exchange the amount back into USD. Furthermore, this will naturally affect the coupon payments as well.

What Iīm hoping for in the end is to build an index starting at 100 and ending at the number which is the % change (total return) from the original amount, taking into account the market value return, interest return and FX return (column J and K). When this is solved for one bond I hope to do the same for several bonds and sample them together in a portfolio. Therefore, I hope some come up with ONE formula in column K that takes all three return elements (for one bond) into account, in order to save space.

If possible, please help me with an example in a spreadsheet.

Hope someone can help me with this, it would be really helpful.

Thank you in advance and donīt hesitate to ask questions.

Brgds