Share
Explore BrainMass

Price of Bonds using Excel

How would set the following problem up in excel? I understand longhand YTM & Current yield but am lost in excel.

XYZ issued a new series of bonds on January 1, 1981. The bonds were sold at $1,000 par, have a 10% coupon, and mature in 2010. The coupon is semi-annual. What was the YTM of the bonds on January 1, 1981? What was the price of the bond on January 1, 1986, five years later, assuming that the level of the interest rates had fallen to 10%. Find the current yield and capital gains yield on the bond in 1986, given the price just determined. On July 1, 2004 XYZ bonds sold for $916.42. What was the YTM? Now assume that you purchased one of these bonds on March 1, 2004, when the going rate of interest was 14.5%, what did the bond cost you?

Solution Preview

See attached file for complete solution.

XYZ issued a new series of bonds on January 1, 1981. The bonds were sold at $1,000 par, have a 10% coupon, and mature in 2010. The coupon is semi-annual. What was the YTM of the bonds on January 1, 1981? What was the price of the bond on January 1, 1986, five years later, assuming that the level of the interest rates had fallen to 10%. Find the current yield and capital gains yield on the bond in 1986, given the price just determined. On July 1, 2004 XYZ bonds sold for $916.42. What was the YTM? Now assume that you purchased one of these bonds on March 1, 2004, when the going rate of interest was 14.5%, what did the bond cost you?

Date of issue= January 1, 1981
Coupon= 10% Semi-annual
Par value= $1,000.00
Date of maturity= January 1, 2011

What was the YTM of the bonds on January 1, 1981?

Since the bond is selling at par , YTM = coupon rate= 10%

What was the price of the bond on January 1, 1986, five years later, assuming that the level of the interest rates had fallen to 10%.

Date= January 1, 1986
Level of interest rate= 10%
Coupon rate= 10%

Therefore the bond will sell at par = $1,000.00

Find the current yield and capital gains yield on the bond in 1986, given the price just determined.

Current yield = Coupon payment ...

Solution Summary

The solution provides answers to questions on bonds in Excel using the Excel worksheet functions.

$2.19