What Is a Bond? Borrow, Build & Repay | An AI Company Story | FIN301
Follow a fictional AI company as it raises capital with $1,000 bonds. Learn about coupons, maturity, early sales, changing market yields, and repayment.
Chapter 7 — Bond Pricing
FIN301 • Coupon bonds, zero-coupon bonds, YTM, current yield, price sensitivity, ratings, and homework.
Learning Goals
- Understand coupon, par value, maturity, price, and yield.
- See why bond prices and yields move in opposite directions.
- Compute bond price, current yield, and yield to maturity.
- Use Excel-style formulas and interactive tools to check answers.
Chapter 7 Links
FINRA fixed income data + bond center
Use FINRA to find a corporate bond and identify coupon rate, maturity date, market price, and yield/YTM.
Chapter 7 Slides (PPT)
This version uses your local PPT link. If you later upload the file to your website, you can replace this with an Office web viewer embed.
Bond Basics
A bond is a loan you make to a company or government. In return, you usually receive periodic coupon payments and the par value at maturity.
| Term | Meaning |
|---|---|
| Coupon | Periodic interest payment in dollars |
| Coupon rate | Annual coupon divided by par value |
| Par value | Face value repaid at maturity, often $1,000 |
| Market price | What the bond sells for today |
| Current yield | Annual coupon / bond price |
| Yield to maturity (YTM) | Discount rate that makes PV of cash flows equal the bond price |
| Maturity | Time until principal repayment |
Intro Video
Adjustable Bond Cash-Flow Timeline
Change the bond inputs and redraw the timeline. The tool computes price directly from the YTM you enter.
Purchase price (outflow)
Coupons and final par (inflows)
Cash-Flow Breakdown
Price–Yield Curve
Annual coupon bond first. Semi-annual coupon bond is below in a collapsed section.
Annual Coupon Bond
This graph assumes 1 payment per year.
Semi-Annual Coupon Bond (click to open)
Semi-Annual Coupon Bond
This graph assumes 2 payments per year.
Excel Recipes
Key formulas
- Annual coupon bond price:
=ABS(PV(yield, maturity, coupon, 1000)) - Semi-annual coupon bond price:
=ABS(PV(yield/2, maturity*2, coupon/2, 1000)) - Annual coupon YTM:
=RATE(maturity, coupon, -price, 1000) - Semi-annual coupon YTM:
=RATE(maturity*2, coupon/2, -price, 1000)*2 - Current yield:
=annual coupon / bond price
Examples
- Annual coupon: If a bond sells for $1,200, solve YTM with
RATE(n, coupon, -1200, 1000). - Semi-annual coupon: If a bond sells for $1,200, solve YTM with
RATE(n*2, coupon/2, -1200, 1000)*2. - Zero-coupon price:
=ABS(PV(y/2, years*2, 0, 1000))
Bond Calculators
Use these tools to compute bond price, YTM, and current yield. Each one also shows the Excel formula and the math setup.
Calculator A: Price from YTM
Price Result
Bond price
—
Annual coupon ($)
—
Current yield
—
Price is the present value of all future cash flows discounted at the YTM.
Excel Formula
—
Math Setup
—
—
Calculator B: YTM from Price
YTM Result
YTM (annual %)
—
Current yield
—
Excel Formula
—
Math Setup
—
—
Calculator C: Current Yield
Current Yield Result
Annual coupon ($)
—
Current yield
—
Excel Formula
—
Math Setup
—
—
Yield Curve (FYI)
Yield curves summarize market yields across maturities. Watch how the shape changes over time.
Chapter 7 Study Guide
1) Use FINRA to find corporate bond information
Search a company such as Walmart on FINRA fixed income data.
Example bond: Walmart (WMT5571329 / CUSIP 931142FE8)
Symbol: WMT5571329 | CUSIP: 931142FE8 | Type: CORP
Open Walmart bond on FINRA2) Key terms to know
- Coupon: periodic interest payment.
- Coupon rate: coupon / par value.
- Par value: amount repaid at maturity.
- Market price: current trading price.
- Yield: general return measure.
- YTM: discount rate that matches PV to market price.
- Current yield: annual coupon / price.
- Maturity: time until principal repayment.
3) Pricing a bond (PV)
Annual coupon bond: Bond price = ABS(PV(yield, maturity, coupon, 1000))
Semi-annual coupon bond: Bond price = ABS(PV(yield/2, maturity*2, coupon/2, 1000))
Key idea: bond price and yield move inversely.
4) Calculating bond return (YTM)
Annual coupon: YTM = RATE(maturity, coupon, -price, 1000)
Semi-annual coupon: YTM = RATE(maturity*2, coupon/2, -price, 1000) * 2
5) Current yield
Current yield = annual coupon / bond price
6) Zero-coupon bond
- 10-year zero sells for $400 → find YTM.
- 10-year zero has YTM = 10% → find price.
7) Bond ratings and Z-score discussion
- Who are Moody’s, S&P, and Fitch?
- What is IBM’s rating?
- Is IBM’s rating the highest?
- Who earns the highest rating?
8) Bond cash flows (investor view)
Example: 5-year annual coupon bond, coupon rate = 5%, par = $1,000.
| Year | Cash flow |
|---|---|
| 1 | $50 |
| 2 | $50 |
| 3 | $50 |
| 4 | $50 |
| 5 | $1,050 (coupon + par) |
Practice
Try each question first on your own. Then open the help box to see the Excel inputs and solution method.
Bond Practice in Excel | Inputs, Equations & the Price–Yield Curve | FIN301 Chapter 7
Follow all four practice questions with cash-flow timelines, detailed Excel inputs, equations, and the downward-sloping bond price–yield curve. Narrated by Emma, with captions and chapter markers.
1) Price a 10-year zero-coupon bond with YTM = 10%
Zero coupon • PV
Show Excel inputs and detailed solution
Given
Par =
1000, Years = 10, YTM = 10%Assumption
Use
semi-annual convention unless told otherwiserate
10%/2 = 5% per half-yearnper
10*2 = 20 periodspmt
0 because it is a zero-coupon bondfv
1000Excel
=ABS(PV(10%/2,10*2,0,1000))copyExcel steps
1. In Excel, type the rate per period:
2. Type the number of periods:
3. Payment is
4. Future value is
5. Wrap with
10%/22. Type the number of periods:
10*23. Payment is
04. Future value is
10005. Wrap with
ABS() so the price shows as positive
Math idea
Price = 1000 / (1 + 10%/2)^(10*2)2) Find current yield for a coupon bond selling below par
Current yield
Show Excel inputs and detailed solution
Example inputs
Coupon rate =
6%, Par = 1000, Price = 950Annual coupon
6%*1000 = 60Current yield
Annual coupon / PriceExcel
=(6%*1000)/950copyExcel steps
1. Compute annual coupon using
2. Divide that annual coupon by the market price
3. Format the result as a percentage
coupon rate × par2. Divide that annual coupon by the market price
3. Format the result as a percentage
Math idea
Current yield = 60 / 950Teaching note
Because the bond sells below par, current yield is usually higher than the coupon rate.
3) Solve YTM for a 5-year annual coupon bond selling at $950
Annual coupon • RATE
Show Excel inputs and detailed solution
Example inputs
Years =
5, Coupon rate = 2%, Par = 1000, Price = 950Function
RATEnper
5pmt
2%*1000 = 20pv
-950fv
1000type
0Excel
=RATE(5,2%*1000,-950,1000)copyExcel steps
1. Since it is annual coupon, use
2. Coupon payment each year is
3. Use
4. Use
nper = 52. Coupon payment each year is
2% × 1000 = 203. Use
-950 for price because Excel needs opposite cash-flow signs4. Use
1000 for face value at maturity
Math idea
950 = 20/(1+r)^1 + 20/(1+r)^2 + 20/(1+r)^3 + 20/(1+r)^4 + 1020/(1+r)^54) Draw a price–yield graph
Price–yield relationship
Show Excel inputs and detailed solution
Suggested bond
Par =
1000, Coupon rate = 5%, Years = 10, Semi-annualGoal
Create prices for many YTM values, then graph them
YTM column
Enter yields such as
1%, 2%, 3%, ... 12%Price formula
=ABS(PV(A2/2,10*2,5%*1000/2,1000))copyExcel steps
1. In column A, list YTM values from low to high
2. In column B, compute bond price using the PV formula
3. Fill the formula down for all YTM values
4. Highlight both columns
5. Insert a scatter chart or line chart
2. In column B, compute bond price using the PV formula
3. Fill the formula down for all YTM values
4. Highlight both columns
5. Insert a scatter chart or line chart
Chapter 7 Homework (due with the second mid-term)
Show steps. Open each problem to see the Excel function and the input setup. Answers are intentionally hidden so students solve them on their own.
Bond Homework Explained in Excel | 10 Questions: Price, YTM & Maturity | FIN301 Chapter 7
Try the homework first, then follow all 10 questions in order. The walkthrough explains RATE, NPER, PMT, PV, and FV inputs and why each input is used.
1) IBM 5-year, 2% annual coupon, price $950 → YTM?
IBM 5-year, 2% annual coupon bond is selling for $950. How much is this IBM bond’s YTM?
Annual coupon • RATE
Show Excel setup
Function
RATErate
(solve)nper
5pmt
2%*1000 = 20pv
-950fv
1000type
0Excel
=RATE(5,2%*1000,-950,1000)copy2) IBM 10-year, 4% semi-annual coupon, price $950 → YTM?
IBM 10-year, 4% semi-annual coupon bond is selling for $950. How much is this IBM bond’s YTM?
Semi-annual coupon • RATE
Show Excel setup
Function
RATErate
(solve per half-year)nper
10*2 = 20pmt
4%*1000/2 = 20pv
-950fv
1000type
0Excel
=RATE(10*2,4%*1000/2,-950,1000)*2copy3) IBM 10-year, 5% annual coupon, YTM 8% → price?
IBM 10-year, 5% annual coupon bond offers 8% return. How much is the price of this bond?
Annual coupon • PV
Show Excel setup
Function
PVrate
8%nper
10pmt
5%*1000 = 50pv
(solve)fv
1000type
0Excel
=ABS(PV(8%,10,5%*1000,1000))copy4) IBM 5-year, 5% semi-annual coupon, YTM 8% → price?
IBM 5-year, 5% semi-annual coupon bond offers 8% return. How much is the price of this bond?
Semi-annual coupon • PV
Show Excel setup
Function
PVrate
8%/2nper
5*2 = 10pmt
5%*1000/2 = 25pv
(solve)fv
1000type
0Excel
=ABS(PV(8%/2,5*2,5%*1000/2,1000))copy5) IBM 20-year zero coupon, YTM 8% → price?
IBM 20-year zero coupon bond offers 8% return. How much is the price of this bond?
Zero coupon • PV
Show Excel setup
Function
PVrate
8%/2nper
20*2 = 40pmt
0pv
(solve)fv
1000type
0Excel
=ABS(PV(8%/2,20*2,0,1000))copy6) Collingwood Homes: 8.5% coupon, 18.5 years, price 964.20, semiannual → YTM?
Collingwood Homes has a bond issue outstanding that pays an 8.5% coupon and matures in 18.5 years. The bonds have par value $1,000 and market price $964.20. Interest is paid semiannually. What is the YTM?
Semi-annual coupon • RATE
Show Excel setup
Function
RATErate
(solve per half-year)nper
18.5*2 = 37pmt
8.5%*1000/2 = 42.5pv
-964.20fv
1000type
0Excel
=RATE(18.5*2,8.5%*1000/2,-964.20,1000)*2copy7) Grand Adventure Properties: 9.5% annual coupon, YTM 11.2%, 11 years → price?
Grand Adventure Properties offers a 9.5% annual coupon bond. The YTM is 11.2% and the maturity date is 11 years from today. Face value is $1,000. What is the market price?
Annual coupon • PV
Show Excel setup
Function
PVrate
11.2%nper
11pmt
9.5%*1000 = 95pv
(solve)fv
1000type
0Excel
=ABS(PV(11.2%,11,9.5%*1000,1000))copy8) D&L Movers zero: price 319.24, FV 1,000, YTM 9.17% → years to maturity?
The zero coupon bonds of D&L Movers have market price $319.24, face value $1,000, and YTM 9.17%. How many years until these bonds mature?
Zero coupon • NPER
Show Excel setup
Function
NPERrate
9.17%/2nper
(solve half-year periods)pmt
0pv
-319.24fv
1000type
0Excel
=NPER(9.17%/2,0,-319.24,1000)/2copy9) Zero coupon: FV 1,000, price 212.56, maturity 25 years → YTM?
A zero coupon bond with face value $1,000 is issued with initial price $212.56. The bond matures in 25 years. What is the yield to maturity?
Zero coupon • RATE
Show Excel setup
Function
RATErate
(solve per half-year)nper
25*2 = 50pmt
0pv
-212.56fv
1000type
0Excel
=RATE(25*2,0,-212.56,1000)*2copy10) Stainless Tubs: 6% coupon, semiannual, 11 years, price 989 → YTM?
The bonds issued by Stainless Tubs bear a 6% coupon, payable semiannually. The bonds mature in 11 years and have face value $1,000. Currently, the bonds sell for $989. What is the yield to maturity?
Semi-annual coupon • RATE
Show Excel setup
Function
RATErate
(solve per half-year)nper
11*2 = 22pmt
6%*1000/2 = 30pv
-989fv
1000type
0Excel
=RATE(11*2,6%*1000/2,-989,1000)*2copy