|
|
 |
 |
 |
 |
 |
 |
 |
|
 |
|
 |
 |
 |
 |
 |
 |
|
 |
 |
 |
 |
 |
|
 |
 |
|
 |
|
 |
|
 |
 |
|
 |
 |
 |
 |
 |
 |
 |
 |
 |
|
 |
 |
 |
|
 |
|
|
|
Free Bond Valuation - Yield to Maturity spreadsheet
A bond is a financial instrument issued by the government or corporations when they need to borrow money from the public on a long term basis to finance certain projects. Interest payments called coupons are typically paid out to bond holders on a regular basis while the entire loan amount called the Face or Par value is repaid at the end. The public can purchase a bond to collect the regular interests and hold the bond to the end called the maturity date to collect the principal amount. Otherwise, at any time before the maturity date, the bond holder can choose to sell the bond in the market at a market price.
Bonds issued by the government are called treasury bonds while bonds issued by the state or local government are called municipal bonds. Treasury bonds held to the maturity date are typically considered riskless as a government can choose to issue more money if it is unable to repay the coupons or the Face value. There is of course still a possibility that a new government regime may choose not to recognize the bonds issued by the previous government.
The BondValuation.xls spreadsheet is created to value the price of a bond if the bond is sold in the market before the maturity date. The most important factor affecting a bond price is the interest rate. In rising interest rates, bond price will drop while in declining interest rates, bond price will rise.
This spreadsheet also calculates the Yield to Maturity which is the interest rate that the bond holder receives if he holds the bond to maturity. The spreadsheet distinguishes between the Annual Percentage Rate and the Effective Annual Rate. When people talk about yield to maturity, they typically refer to the Annual Percentage Rate. The Effective Annual Rate basically takes into account the effect of compounding interests of the coupons.
Finally, this spreadsheet also illustrates how to plot the US Treasury Bond Yield Curve which is used by many analysts for understanding the current conditions in the financial markets.
Bond Valuation - Yield to Maturity
The Yield to Maturity is a common yardstick that a bond investor uses to measure the value of a bond. It is basically the rate of return, sometimes referred to the internal rate of return, when a bond is held to maturity. In simple words, it is the interest rate you will receive if you hold the bonds to the maturity date without selling it in the market. The Yield to Maturity is calculated in the Bond Valuation-Yield worksheet.
Input Values
- Coupon Payment Frequency (pf) - Whether the coupon is paid annually or semi-annually. The coupon rate is typically stated in an annual percentage. Thus if a coupon is paid out semi-annually, the coupon payments is equivalent to : (Coupon Rate / 2) * Face Value of the Bond
- Face Value (F) - The principal or loan amount of the bond to be repaid at the end of the maturity period.
- Number of Periods to Maturity (N) - This field is related to the Coupon Payment Frequency. If Coupon Payment Frequency is set to Semi-Annually, Number of Periods means number of Half-year period. If Coupon Payment Frequency is set to Annually, then Number of Periods means number of One-year period. This field is used in the calculation of the Bond's Yield to Maturity.
- Coupon Rate (I) - This is the stated annual interest rate payments for a Bond. This interest rate multiply with the Face value gives the periodic coupon payments.
- Bond Price (v) - The current price of the bond in the market. Bond prices fluctuates due to changes in interest rates and the price that the bond is purchased affects the Yield to Maturity.
- Yield to Maturity Type - This Bond Valuation spreadsheet distinguishes between the Annual Percentage Rate and the Effective Annual Rate. When people talk about yield to maturity, they typically refer to the Annual Percentage Rate. The Effective Annual Rate basically takes into account the effect of compounding interests of the coupons.
Output Values
- Discount Rate per period (r) - Yield to Maturity is typically quoted like an Annual Percentage Rate. This discount rate is the exact rate per period. For example, if the Coupon Payment Frequency is semi-annually, then this discount rate is the rate per six months.
- Yield to Maturity (Y) - The interest rate received if a bond is held to the maturity date.
Download Free Bond Valuation spreadsheet - v1.0
System Requirements
Microsoft® Windows® 2000 ,Microsoft® Windows XP® and 2003, Microsoft® Windows Vista®
512 MB RAM
5 MB of Hard Disk space
Excel 2002, 2003, XP or Excel 2007
License
By downloading this software from our web site, you agree to the terms of our license agreement.
Download
FreeBondValuation.zip (Zip Format - 94 KB)
Get the Professional version
Benefits
- Unlocked
- Allows removal of copyright message in the template
- Allows commercial use within the company
- Supports Yield to Maturity calculation
- Supports Bond Price Valuation
- Supports US Tresury Bond Yield Curve
- Full source code and documentation of important fields in the spreadsheet
Details PDF Specifications
Bonus
Limited Time Bonus
- Free Visual Basic for Applications Training worth USD$30 (Over 100 pages!)
Price
USD30.00 - Purchase
Back to Excel Templates and Add-Ins
Get all the Finance Spreadsheets in one professional package
Get the Finance Spreadsheets individually
Other Free Finance Spreadsheets
Other Free Spreadsheet Software
Other Commercial Software
|
|
 |
|
|
 |
 |
|
 |
 |
|
 |
 |
|
 |
|
|
Copyright (c) 2007-2009, ConnectCode Pte Ltd. All Rights Reserved.
Trademark Acknowledgements
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. Avery is the trademark of Avery Dennison Corporation. All other product names are trademarks, registered trademarks, or service marks of their respective owners
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
