---
tags: bonds
---
# Bond Yield Calculator and Formulas

**Bond Basics**

Reiterating from the [bond primer](https://hackmd.io/@indiainvestments/HkWha3old), a bond is a debt instrument in which an investor loans money to an entity (typically corporate or government) which borrows the funds for a definite period of time at a specified interest rate.

Because the investor has "loaned" money, they will expect a return associated with giving that capital for a set period from the entity availing that loan.

Bond prices fluctuate with the interest rate movements. The yield of a bond is inversely related to its price so if market interest rate levels rise, the price of a bond falls and if the interest rate falls, the price increases.

There are so many varieties of bonds such as fixed rate, floating rate, inflation indexed, call/put options, STRIPS, sovereign gold bonds but for this article, we will consider fixed rate bonds.

**Basic Definitions**

> **Coupon yield** - also called coupon rate; it is a fixed return that the issuer commits to pay the investor usually as a percentage of the face or par value in a fixed time.
>
> **Current Yield** - the return for a bond holder as a percentage of the bond purchase price without accounting for reinvestment of the interest income periodically.
>
> **True Value** - also called par or face value; the amount of money that bond issuers agree to repay to the investor at bond maturity
>
> **Yield to Maturity (YTM)** - the expected rate of return on a bond if it is held until its maturity
>
> **Macaulay Duration** - this calculates the weighted average time before a bondholder would receive the bond's cash flows; also defined as the time taken to recover the initial investment in present value terms
>
> **Modified Duration** - this measures the price sensitivity of a bond when there is a change in the yield to maturity; it is an adjusted version of the Macaulay duration, which accounts for changing yield to maturities

**Bond Nomenclature and Interpretation**

Investors, who put their money in Treasury Bills (or T-Bills) and bonds, have the option to invest through Reserve Bank of India (RBI) auctions in different variants based on the maturity period. The biggest difference between T-bills and bonds are that T-bills do not have an interest component and have maturities of one year or less. T-bills are issued at a discount to their true value and redeemed at the same true value upon expiry. Hence, the yield applies for bonds and measures the return on investment on an annual basis.

All bonds issued by the RBI have an unique name or symbol associated with them and managed by the respective exchange for bookkeeping purposes. All bonds are traded on the BSE and the NSE. One can access the details related to traded bonds from these links – [BSE](https://www.bseindia.com/markets/debt/debt_corporate_EOD.aspx?curPage=1) and [NSE](https://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm?cat=SEC).
For e.g. on the NSE link, one may see a code such as 520GS2030A.
So breaking this code results in
1. 520 refers to the annualized interest of 5.20% (also called coupon rate)
1. GS refers to government securities
1. 2030 is the year the bond will mature
1. A refers to a new bond issue

Hence, this particular bond expires in 2030 (i.e. 9 years from now in 2021). If one invested in this bond, they would receive a 5.2% interest every year until its maturity in 2030. The interest will be paid semi-annually such that it will be 2.6% interest every six months in a year. Finally, upon maturity, the investor will also get back their principal amount.

Depending on when one buys or sells the bond, the return earned would be different from the coupon rate that the instrument carries. A bond's coupon rate is expressed as a percentage of its par value (and influenced by government interest rates) while the return earned is the yield to maturity (YTM).

Bond traders and institutional investors look at YTM because this is the true comparable value between two different types of bonds. A bond investor is more likely to base their decision on an instrument's coupon rate. However, one should remember that the YTM calculation assumes that one reinvests the interest payment back into a similar bond, which further generates interest on interest.

At the time it is purchased, a bond's YTM and its coupon rate are the same. However, interest rate movements from the government dictate different rates so the relationship between yield to maturity and coupon rate of bond may be stated as follows:

* When the market price of the bond is less than the par value, i.e., the bond sells at a discount and the YTM > coupon yield.
* When the market price of the bond is more than its par value, i.e., the bond sells at a premium and the coupon yield > YTM.
* When the market price of the bond is equal to its par value, i.e., the bond sells at par and the YTM = coupon yield.

All of these theoretical aspects are now covered with the underlying math below.

# **Basic Mathematical Concepts regarding Bond Prices and Yields**

**Coupon Yield**

Coupon yield = Coupon Payment / Face Value

As an example,

Coupon: 6.84

Face Value: ₹100

Market Value: ₹103.00

Coupon yield = 6.84/100 = 6.84%

<br>

**Current Yield**

It is Current yield = (Annual coupon rate / Purchase price) X 100

CY = C / P * 100,

or

CY = (B * CR / 100) / P

Where:

CY is the current yield,

C is the periodic coupon interest payment,

P is the price of a bond,

B is the par value or face value of a bond,

CR is the coupon rate.

As an example,
the current yield for a 10 year 6.84% coupon bond selling for ₹103.00 per ₹100 par value is calculated below:

Annual coupon interest = 6.84% x ₹100 = ₹6.84

Current yield = (6.84/103) X 100 = 6.64%

<br>

**Yield to Maturity**

The following formula is used to calculate the bond price as it uses the time value of money with the premise that a rupee today is more valuable than a rupee at a later time as one can use today's money to earn interest and higher purchasing power now than a year later:

P = C*(1 + r)^-1^ + C*(1 + r)^-2^ + . . . + C*(1 + r)^-Y^ + B*(1 + r)^-Y^

Where:

P is the price of a bond,

C is the periodic coupon payment,

r is the yield to maturity (YTM) of a bond,

B is the par value or face value of a bond,

Y is the number of years to maturity.

Hence, reversing this formula (assuming constant coupon payment) gives the Yield to Maturity = (Annual Interest + ((Par Value - Market Price)/No. of years to Maturity))/(Par Value + Market Price)/2)


As an example, if the par value (face value) is Rs 1,000, current market price is Rs 900, coupon rate is 10%, the annual coupon payment is Rs 100 and time to maturity is 10 years.

the YTM would be: [100 + (1,000-900)/10] / (1,000+900)/2

the YTM would be 11.58%.

<br>

**Macaulay Duration**

See the below examples provided by the Reserve Bank of India on [its website](https://www.rbi.org.in/scripts/PublicationsView.aspx?Id=16413#27).
Here, a bond having 2 years maturity, and 10% coupon, and current price of ₹101.79 will have the following cash flows

| Time Period (half year) | 1 | 2 | 3 | 4 | Total |
| ----------------------- | ---- | ---- | --- | --- | --- |
| Inflows (₹) | 5 | 5 | 5 | 105 | |
| PV at a yield of 9% | 4.78 | 4.58 | 4.38 | 88.05 |101.79
| PV * time | 4.78| 9.16 | 13.14 | 352.20 |379.28

Duration in number of periods = 379.28/101.79 = 3.73

Macaulay Duration in years = 3.73/2 = 1.86 years

<br>

**Modified Duration**

Using the same example as above, MD = 1.86/(1+0.09/2) = 1.78 years

<br>

# **Important Excel functions for bond related calculations**

The below functions have been provided by the RBI for bond related calculations to be done by an individual using MS Excel at this [link](https://rbidocs.rbi.org.in/rdocs/content/pdfs/ANNEX20082019_6.pdf).

| Function | Syntax |
| -------- | -------- |
| Present Value | PV(rate,nper,pmt,fv,type) |

This function is used to find the present value of a series of future payments given the discount rate. This forms the basis for pricing a bond
* Rate is the interest rate per period.
* Nper is the total number of payment periods in an annuity.
* Pmt is the payment made each period and cannot change over the life of the annuity.
* Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
* Type is the number 0 or 1 and indicates when payments are due.

<br>

| Function | Syntax |
| -------- | -------- |
| Future Value | FV(rate,nper,pmt,pv,type) |

This function is used to calculate the future value of a series of investments made, given the interest rate.
* Rate is the interest rate per period
* Nper is the total number of payment periods in an annuity
* Pmt is the payment made each period; it cannot change over the life of the annuity.Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
* Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
* Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

<br>

| Function | Syntax |
| -------- | -------- |
| Coupon days | COUPDAYBS(settlement,maturity,frequency,basis) |

This function is used to workout the number of days from the beginning to the end of the coupon period that contains the settlement date.

* Settlement is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
* Maturity is the security's maturity date. The maturity date is the date when the security expires.
* Frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
* Basis is the type of day count basis to use. (0=US system 30/360, 2=Actual/actual, 3=Actual/365, 4=European style 30/360 (thus 0 or 4 throws same value)).

<br>

| Function | Syntax |
| -------- | -------- |
| Yearfrac | YEARFRAC(start_date,end_date,basis) (to find residual maturity) |

This function is used to find the residual maturity of a security in years.
* Start_date is a date that represents the start date.
* End_date is a date that represents the end date (maturity date).
* Basis is the type of day count basis to use. (0=US system 30/360, 2=Actual/actual, 3=Actual/365, 4=European style 30/360 (thus 0 or 4 throws same value)).

<br>

| Function | Syntax |
| -------- | -------- |
| YIELD | YIELD(settlement,maturity,rate,pr,redemption,frequency,basis) |

This function is used to find the Yield to Maturity of a security given the price of the security.
* Settlement is the security's settlement date. The security settlement date is the date on which the security and funds are exchanged. Maturity is the security's maturity date. The maturity date is the date when the security expires.
* Rate is the security's annual coupon rate.
* Pr is the security's price per ₹100 face value.
* Redemption is the security's redemption value per ₹100 face value.
* Frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
* Basis is the type of day count basis to use.

<br>

| Function | Syntax |
| -------- | -------- |
| DURATION | DURATION(settlement,maturity,coupon,yld,frequency,basis) |

This function is used to find the Duration of a security in number of years.
* Settlement is the security's settlement date. The security settlement date is the date on which the security and funds are exchanged.
* Maturity is the security's maturity date. The maturity date is the date when the security expires.
* Coupon is the security's annual coupon rate.
* Yld is the security's annual yield.
* Frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4. Basis is the type of day count basis to use.

<br>

| Function | Syntax |
| -------- | -------- |
| Modified Duration | MDURATION(settlement,maturity,coupon,yld,frequency,basis) |

This function is used to calculate the Modified Duration of a security.
* Settlement is the security's settlement date. The security settlement date is the date on which the security and funds are exchanged.
* Maturity is the security's maturity date. The maturity date is the date when the security expires.
* Coupon is the security's annual coupon rate. Yld is the security's annual yield.
* Frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
* Basis is the type of day count basis to use.