Explore BrainMass

Explore BrainMass

    Price of Bonds using Excel

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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?

    © BrainMass Inc. brainmass.com June 3, 2020, 6:17 pm ad1c9bdddf
    https://brainmass.com/business/bond-valuation/price-of-bonds-using-excel-54441

    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

    ADVERTISEMENT