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!
Calculating Bond Price using Excel



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

thank you so much really appreciate it

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!