This assignment is a practice using excel (or other software or programming language of your choice) to build a pricing tool for bonds

Subject:BusinessPrice: Bought3

Share With

This assignment is a practice using excel (or other software or programming language of your choice) to build a pricing tool for bonds.

 

What to submit?

Excel file: this should include your Excel calculations

 

Data source:

 

FINRA’s market data center http://finra-markets.morningstar.com/BondCenter/Default.jsp

 

1. Build the tool to price corporate bonds.

 

Inputs for the bond valuation (price) should include:

par value (face value), coupon rate (for zero-coupon bonds, coupon rate =0), number of compounding periods per year, time to maturity in years, YTM (APR)

 

2. Use the excel function or Excel solver add-in to solve for YTM given the bond price. If you do not see the “solver” in the “Data” tab, you need to add it as an “add-in”.

 

3. The quoted bond prices are “clean prices” which do not include accrued interest. Build a separate function to calculate accrued interest, then calculate dirty price.

 

Accrued interest = (days since last coupon/number of days in the coupon period)*next coupon payment

Dirty price = clean price + accrued interest

 

4. Check to make sure that your bond price function works correctly using price-yield pairs in class examples or real data from the bond database.