Comprehensive Bond Valuation: Time Value of Money & Excel Report

This report provides a comprehensive analysis of bond valuation using time value of money techniques, including present value, future value, and rate calculations. It focuses on both callable and non-callable bonds, demonstrating practical applications using Excel functions such as "Rate," "PV,"...Read More

BUSINESS FINANCE

Introduction
One of the most significant concepts of financial management is the valuation of the
securities using the techniques of time value of money. The following work is aimed at
evaluating the various techniques of time value of money such as present value, future value,
and the computation of the rates, in the context of the valuation of the bonds. The practical
application is demonstrated using the Excel functions like that of “Rate,” “PV,” and “FV.”
The valuation is divided into two categories for the callable and the non-callable bonds and
comparisons are demonstrated for the better understanding of the valuation in the real life
scenario.
Analysis
The following segment is descriptive of the analysis of the varied calculations
performed. It is to be noted that as the interest rate for the coupon payment is semi-annually,
the periodic rate is computed by dividing the rate into half, and multiplying the number of
years by 2. Thus in the Part 1, A) the actual number of periods, the amount of annual and
periodic coupon payments, the periods until calling of the bonds are computed.
In the Part 1, B) the “rate” function has been employed for the computation of the
periodic YTM. It is to be noted that the amount of coupon payment has been divided by 2, to
make it equivalent to the periodic YTM. Further, the annualized YTM has been computed by
taking the number of years, instead of periods and the annual coupon payment. It must be
noted that the periodic YTM turns out to be exact half of the annualized YTM.
The current yield as calculated in the Part 1, C) refers to the percentage of the annual
income as against the current price of the bond. Thus, the current yield has been computed
out to be 5.26%. As the coupon rate is lesser than the current yield and annualized YTM, it is
evident that the bond is selling at discount (Hopewell & Kaufman, 2017).
In the Part 1, D) it has been computed that the bonds are trading at a slight loss in the
form of the capital loss percentage of 0.15%. Thus, the market price is slightly lower than the
ideal prices as per the YTM. Part 1, E) further sheds light on the Yield to Call, that has been
computed on the basis of the number of years till the bonds can be called. As the yield to call

is lesser than the yield to maturity, it is evident of the fact that the act of calling would lead to
the limitation on the price appreciation of the bond, as per its potential.
Part F, G, H of the Part 2 shed light on the relationship of the bond value in context of
the market rate. As the market rate is further lower than the YTM of the bond, the present
value of the callable bond as computed with the aid of the “PV” function in excel is more
than the present value of the non-callable bond. This means, in the given scenario, calling the
bond would be a more efficient decision (Van Binsbergen, Hueskes, Koijen & Vrugt, 2013).
This is because of the time difference therein.
The bond value table further leads to the observation that lower the market rate, the
higher the present values. The said fact has been further presented in the form of a graph,
presented below.
Conclusion
It is concluded from the discussions in the previous parts that the excel functions used
above lead to the comprehensive evaluation of the performance of the bond and the
comparison of the same in context of the varied market rates.
0% 2% 4% 6% 8% 10% 12% 14% 16%
$0.00
$500.00
$1,000.00
$1,500.00
$2,000.00
$2,500.00
Actual Present Value
Actual Present Value

References
Hopewell, M. H. & Kaufman, G. G. (2017). Bond price volatility and term to maturity: A
generalized respecification. In Bond Duration and Immunization, UK: Routledge, 64-
68.
Van Binsbergen, J., Hueskes, W., Koijen, R. & Vrugt, E. (2013). Equity yields. Journal of
Financial Economics, 110(3), 503-519.