Lesson 6 of 11 · All lessons in this module
- Present value and discount (zero-coupon) bondsVideo lesson
- Coupon bonds and perpetual bonds
- Level-payment instruments: mortgages and amortizing loansVideo lesson
- Valuing shares with dividend discount models
- Implied returns on bonds
- Floating-rate coupons and spreadsheet TVM functions
- Required return, implied growth and the P/E ratio
- Cash flow additivity, no arbitrage and forward rates
- No-arbitrage pricing of FX forwards and options
- Valuing a real option with cash flow additivity
- Integrated cases: bills, forward rates and currency forwards
This lesson is part of the 2027 curriculum. You are following the 2026 curriculum, where it is not taught in this form. Switch if you are sitting the exam under the 2027 curriculum.
Lesson 6 of 11 · 12 min
Floating-rate coupons and spreadsheet TVM functions
A floating-rate bond's coupon is reset from a reference rate plus a fixed margin, and every present value, payment or implied rate in this module can be solved with a handful of spreadsheet functions.
In short
- A floating-rate bond resets its coupon each period: coupon rate = reference rate + fixed margin, set shortly before the payment.
- The periodic coupon = (reference rate + margin) ÷ payments per year × principal. The margin compensates for the issuer's risk and stays fixed.
- Excel and Google Sheets share the same core functions: PV, PMT and RATE for level cash flows, IRR for uneven ones.
- rate and nper are per period; a cash outflow is entered as a negative number, so PV and PMT usually return negative values for what the investor pays.
- type = 0 for end-of-period payments (the default), 1 for payments at the start of each period.
Unlock this lesson free for 7 days
Create a free account to get 7 days free: every lesson, video and flashcard, and the question bank. No card needed.