Calculating Bond Yield to Maturity with Excel’s RATE Function

Summary

The document explains how to solve for a bond’s yield to maturity in Excel instead of repeatedly changing the discount rate by hand. It recommends the RATE function, using the number of coupon periods, coupon payment, and bond price and face value as inputs. For the stated example, where a bond sells at face value and pays a 3% coupon, yield to maturity equals the coupon rate.

The answer also notes that guessing and checking can estimate a yield, while an exact hand calculation generally requires a financial calculator or a numerical solver. The example assumes regular coupon periods and contains an inconsistency: its displayed pricing formula discounts the face value to period 11 despite stating a five-period maturity. The RATE example instead uses five periods, so users should check payment frequency, maturity, and cash-flow signs before applying it to other bonds.

Key ideas

• Excel’s RATE function can solve for a bond’s periodic yield from its cash flows and price.
• A bond priced at face value has a yield to maturity equal to its coupon rate under the example’s assumptions.
• The period count and coupon payment must match the bond’s payment schedule.
• Check the cash-flow timing carefully because the displayed formula and example use different maturity periods.

Tags

# Calculate yield of maturity for a certain price in excel


# Calculate yield of maturity for a certain price in excel












I have a bond with a time to maturity of 5, a nominal value of $100, coupons of \$3,- and an yield price that I need to calculate so that the bond price equals \$100,-. This yield value is symbolized by y in the following formula:

$P_{markt}=\sum\limits_{t=1}^{5}\frac{3}{(1+y)^t}+\frac{100}{(1+y)^{11}}$

Is there a way to easilly calculate this value of $y$ in excel? At the moment, I sum all the bond payments and the present value, and change the interest rate until I have the right value. Can this be done more efficient?

## Answer by Brumder (score 0, accepted)

https://quant.stackexchange.com/a/21078

This formula in excel should do it:

```
=RATE((5*1),((3/100)*100),-100,100)
```

You'll find it's 3% since YTM = coupon when the nominal value = market price.

Thus, for the bond's market price to = 100 (face value), the YTM will be the same as the coupon which in this case = (payment/face value) = (3/100) or 3%. You can change the market price of the bond (-100 in the formula) and y will adjust to the appropriate figure.

By hand, guessing & checking is the only way to get an exact figure (unless you have a financial calculator). However, for an estimate you can use the following:

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.