Bonds A Step by Step Analysis with Excel

Chapter One describes how to price a bond and calculate the different measures of return working with real bond examples and Excel spreadsheets. Beginning with hypothetical examples, we then work with a real bond, describing a step by step procedure to design the cash flow in a spreadsheet, compute the yield to maturity and other measures of return, according to the bond indenture. After reading this chapter, you will be capable of:•Designing the cash flow for a particular investment amount•Pricing a bond and calculating its yield to maturity, using an Excel spreadsheet.•Calculating the total return for an investment horizon.•Performing a sensitivity analysis of price, yield and total return.Chapter Two describes in detail two measurements to estimate the volatility of a bond price: duration and convexity. After reading this chapter you will be capable of: •Understanding the price-yield relationship of an option-free bond.•Calculating the duration, modified duration and convexity for real bonds using Excel spreadsheets.•Understanding why duration is a measure of a bond´s price sensitivity to yield changes.•Understanding the limitations of the duration as a measure of price volatility and how the duration estimation can be adjusted for a bond´s convexity.Chapter Three describes some real-world situations in which the yield cannot be interpreted directly. The examples discusses some important issues such as the need to equalize the amount of the investment, the calculation of the total return for a certain investment horizon and the portfolio’s return calculation with external cash flows. Also, there are anomalous situations that lead to a fictitious yield, such as the case of the bond prices that assume a strong probability of default. After reading this chapter you will be capable of: •Understanding that the IRR is not always a good measure of the annual return.•Identifying situations in which the yield requires an additional interpretation.•Calculating a portfolio return, when there are deposits and withdrawals in a brokerage account.Chapter Four describes the measure to calculate a portfolio return. After reading this chapter, you will be capable of: •Calculating the different measures of a portfolio return, with and without external cash flows.•Understanding their limitations, advantages and disadvantages.Chapter Five explains the spot rates, forward rates and the famous yield curve. Although the calculations to obtain the theoretical spot rate can be tedious and cumbersome, we describe a technique that uses matrixes which is simpler and faster. We hope the methodology will be helpful for practitioners.Chapter Six describes how to immunize the return of a bond portfolio using the concepts of duration and convexity, how to rebalance the duration of the portfolio and the cash flow matching technique.Chapter Seven describes the mechanics of neutral-probability approach to value an option and address the pricing of an option-free bond using forward rates binomial trees.