# How to calculate monthly return on Shiller's "Long Interest Rate GS10" (1871-2016)? I hope someone out there can help with some formulas and excel procedure to calculate the total return on bonds. I am doing some research and empirical studies for my bachelor project based on Shiller data:  http://www.econ.yale.edu/~shiller/data.htm This is a great and easy spreadsheet to work with. However, I am facing problems with the 'Long interest rate GS10'. There's very little info related to this piece of data. All I have is the monthly YTM, and I find it difficult to calculate the monthly return. I assume I have to "pretend" that I'm buying one month and selling the next. Repeatedly. I need it for a calculation of Excess return on stocks in the same period. Your intuition is correct; the monthly return is essentially what you'd get each month if every month you cashed out and then bought back in. In fact, most people consider the yield-to-maturity a better measure of return than monthly return, but you said you wanted monthly return, so here goes.

For example, if the price for the same asset went up from \$50 to \$100 in the first month, that would be a 100% monthly return. Then if it went back down to \$75, that would be a -25% monthly return. (Notice that if you use percentages, you can't simply average monthly returns to get the overall return. In this case the overall return is 50%, or 22% per month. If you used log points, you could average them---this is why in my humble opinion returns should be measured in log (logarithm) points.)

The key here is to understand what the yield-to-maturity actually means.

Yield to maturity is the discount rate at which the sum of all future cash flows from the bond is equal to the price of the bond.

For instance, suppose we have a zero-coupon bond that pays \$100 at maturity, which is 12 months from now. Initially it's priced at \$90; this means that we have a discount rate such that \$90 today is exactly as good as \$100 in 12 months. Thus our annual discount rate is 10%---this is our yield to maturity. Now in the following month suppose the price rises to \$91. This now means our discount rate must be such that \$91 today is worth \$100 in 11 months. Our yield to maturity must therefore be 1 - (91/100)^(11/12) = 8.3%.

Since you're given the yield-to-maturity and you want the price, you have to do this backwards, solving for the current price P where P_0 is the nominal value of the payment at maturity (\$100 in my example) and n is the number of months remaining until maturity:

YTM = 1 - (P/P_0)^(n/12)

(1 - YTM)^(12/n) = P/P_0

You may not know P_0, but that's okay; just make one up, because you're actually interested in the relative return. You'll be dividing out P_0 anyway.

I've been assuming a zero-coupon bond. If there are coupon payments, you have a much trickier problem, because you also discount the future coupon payments:

P = sum C_0 + C_1 / (1+YTM)^(1/12) + C_2 / (1+YTM)^(2/12) + ... + P_0 / (1+YTM)^(n/12)