Calculating Bond Price using Excel

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Could someone please give me detailed instructions on how to calculate the price of a bond (if given all the other elements, such as the coupon rate, etc) using excel? I know it has something to do with the payment function but I'm just really confused!:eureka:
    Thank you so much!

    use PRICE function:


    PRICE(TODAY(),EDATE(TODAY(), 36),3%,4%,100,2,0)


    This is a bond which matures in 3 years, with a semi annual, 30/360 coupon of 3% and 4% yield (formula result 97.19928 looks intuitively correct).


    Also use YIELD() to find yield if you know the price. Use help on these functions for extra info on day count basis etc...


    Joel

    No problem. Ensure you have the Analysis Tool Pack added in.


    Other useful functions:


    ACCRINT - calculates accrued interest
    COUPDAYBS, COUPDAYS, COUPNCD, COUPPCD - coupon dates and days
    DURATION - duration
    MDURATION - modified duration
    ODDFPRICE, ODDLPRICE - if you have short/long first or last coupon period

    it sounds like you know a lot about the financial functions... what sort of resources do you recommend for someone who's realtively new to excel?

    Trial and error and the help files is all I ever had till I found this forum... Once you have found a function that works for you, look at related functions in the help files and see what else you can do. I've not discovered a difficiency in Excel yet...

    I tried the formula presented above and it works. However, if you replace 36 (months, 3 years) with a month value that is not equal to a year, i.e., NOT divisible by 12, You get incorrect answers.


    Take this example.
    PRICE(TODAY(),EDATE(TODAY(), 36),4%,4%,100,2,0)


    This is a bond which matures in 3 years or 36 months, with a semi annual, 30/360 coupon of 4% and 4% yield (formula result 100 or PAR).


    Now try this:
    PRICE(TODAY(),EDATE(TODAY(), 35),4%,4%,100,2,0)


    This is a bond which matures in 35 months, with a semi annual, 30/360 coupon of 4% and 4% yield (formula result 99.9972557). Obviously if the CPN and Yield are =, then the price is 100.


    What am I doing wrong?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login