A Model for Analysis of Bond Valuation By Using Microsoft Excel plus VBA

Monday, November 13, 2017

A Model for Analysis of Bond Valuation By Using Microsoft Excel plus VBA







A bond is a type of asset in which a government or a company issues these securities as a long – term debt to borrow money from institutional investors (Banks) or public sector. Each bond has a time to maturity which is usually 5, 10, and 20 or 30 years. Initial value of bond is named par value or face value equal to $1000 with a coupon interest rate on the bond which is the percentage of par value and it will be paid annually or semiannually (two times in one year). In fact, the government or the company is committed regularly and continuously to pay these payments and also repayment of initial value (par value) at maturity time.
The purpose of this article is to present a model for analysis of bond value where there are seven independent variables including current bond price (bond value), YTM, coupon rate, purchased year, purchased month, purchased day and time period (n). This model simultaneously solves an equation with three independent variables accompanied by generating maximum and minimum of this function for given domain and range and also non simultaneously analyzes seven independent variables. One of the most crucial applications of this model is to obtain YTM (return rate) for current price equal to bond value without using any trial and error.

A coupon interest rate always stay the constant while the purchasers of bonds strongly look at and compare it with premium risk of market which is named the return rate or required rate on the capital or yield to maturity (YTM). This is why the price of bond varied with bond value. Of course, there are two factors for this discrepancy: (1) the difference between coupon interest rate and return rate (YTM) and (2) entering time (the time of purchasing). Below diagrams as well as show us the impact of these factors for time periods of 30, 20 and 10 years:




Above diagrams say to us, when YTM (rd) is greater than the coupon rate, the bond value will be less than its par value (Discount Bond), when YTM (rd) is less than the coupon rate, the bond value will be greater than its par value and when YTM (rd) is equal to coupon rate, the bond value will be equal to par value.
Generally the basic valuation model for any asset can be made by using below equation:

Where:

V0 = value of the asset at time zero

CFt = cash flow expected at the end of year t

r = appropriate required return (discount rate)

n = relevant time period

But for each specific asset such as Bonds, Stocks, Real estate and so on, we have to change a little bit above basic equation. For instance, the formula to evaluate the bond value can be as follows:



B0 = value of the bond at time zero

I = annual interest paid in dollars

r = appropriate required return (discount rate)

n = number of years to maturity

M = par value in dollars

rd = required return on a bond

I also used above equation to make this model for analysis of the bonds. Below figure as well as shows the features of this model:



As you can see in the figure above, there are seven independent variables (Inputs) which have been highlighted by red color. First, we enter the period of maturity which is “n”. Then, according to the issue date and the maturity date, we enter Year, Month and Day as current date. After that, we choose a range for Current price, YTM and Coupon rate (Low and High) and next we consider a specific current bond price which is into the range of current price. Finally, this model gives us the outputs which are the minimum and maximum bond value with  the appropriate  YTM and Coupon rate. In the meanwhile, you can see that specific bond price (input) is approximately equal to specific bond value (output) that it says to us about the appropriate YTM and Coupon rate for the specific bond price where we do not need to use any trial and error to obtain YTM for a specific bond price.
You can see below clips as the examples for this model:

The model for n = 30



The model for n = 10



All researchers, investors and individual people who are interested in having this model, don’t hesitate to send their request to below addresses:

No comments:

Post a Comment