How to Calculate Market Value of Fixed Income Securities

Spreadsheets are useful for developing, saving and improving on valuation models.

Spreadsheets are useful for developing, saving and improving on valuation models.

Calculating the market value of fixed income securities, also known as bonds, can be done using a variety of methods and models ranging from the simple to complex. Your own level of comfort with computer programming, statistics, and finance may dictate a model’s complexity, but anyone can build an adequate valuation model using a spreadsheet and accurate market data. Your first step should be to become somewhat acquainted with basic spreadsheet functions. Over time, save your models as your own spreadsheet skills improve so that you can re-use them as templates.

Review Promissory Note Agreement

Obtain and review the relevant promissory note agreement, which will detail nearly all of the important assumptions and inputs that you must build into your model. The agreement will describe the type of fixed income security you are valuing, the stated coupon rate if applicable, when interest payments will be made, and how they are to be calculated. You should also review the agreements for any provisions that you feel could affect value. More advanced bond valuations must also account for conditions such as callability or embedded options.

Obtain an Accurate Yield to Maturity

There is a difference in meaning between the stated yield of a bond, also referred to as coupon rate, and its yield to maturity. If the two are equal, the bond is valued at par, which is the principal amount of the loan. When yield to maturity is higher than the coupon rate, the bond is valued at a discount to par. The bond is valued at a premium when the yield to maturity is lower than the coupon rate. The yield to maturity is the prevailing market rate applicable to the bond based on a variety of risks, including default and bankruptcy risk. You must either spend time reviewing the yields of comparable bond offerings or obtain the yield to maturity from a data provider, but it is critical to that all characteristics of the bond match those of the comparable bonds from which you derived a market yield to maturity. This means comparable bond type, maturity, and issuer characteristics such as line of business and default risk.

Model out Cash Flows

Using the promissory note agreement as a guide, create a spreadsheet model covering a number of time periods that equals the number of cash flows that will be made. If the bond makes quarterly payments for five years, including a lump sum payment during the last quarter, the model should contain twenty rows in the spreadsheet. Enter a formula in each time period’s cells that calculates the exact cash flow to be made based on the interest rate and the principal. It is always preferable to enter this figure as a formula rather than a simple value, so that someone can review your model and the assumptions behind the values it contains.

Calculate Present Value of Cash Flows

The formula for calculating present value factor is: 1/(1+r)^n, where "r" equals the yield to maturity, and "n" equals the time period. Each cash flow payment must have its own present value factor to reflect its unique present value based on the timing of that cash flow. For example, in keeping with the example of a bond making quarterly payments for five years, "n" for month two of the second year equals 2.5. The coupon rate only dictates the amount of the cash flow payments. The yield to maturity is used to calculate the present value of the cash flows to be made based on prevailing market sentiment. This is a difficult concept, and you should experiment with simple bond valuation examples from the Internet to gain valuable experience.

Video of the Day

Brought to you by Sapling
Brought to you by Sapling

Photo Credits

  • Stockbyte/Stockbyte/Getty Images