This lesson explains how Excel’s financial functions can solve two common bond problems: finding a bond’s present value (price) and finding its yield to maturity (YTM). These calculations apply time-value-of-money principles to the bond’s coupon payments and repayment of face value.
A coupon bond’s price is the present value of its future coupon payments plus the present value of its face value at maturity. Excel’s PV function calculates this value when the periodic rate, number of periods, coupon payment, and future value are supplied.
All flashcards
What does Excel’s PV function calculate for a coupon bond?
It calculates the present value of the bond’s coupon payments and face value using the periodic discount rate and number of periods.
What does nper represent in a bond PV or RATE calculation?
It is the total number of payment periods, not necessarily the number of years. For a 15-year bond with semiannual coupons, nper is 30.
How do you convert a semiannual bond’s annual coupon rate into a coupon payment?
Multiply face value by the annual coupon rate, then divide by 2. For a $1,000 bond at 4%, the semiannual coupon is $20.
What does Excel’s RATE function solve for?
RATE solves for the interest rate per period that makes the present value of the bond’s future coupon payments and face value equal its price.
Why can PV show a negative bond value in Excel?
Excel uses a cash-flow sign convention. If coupons and face value are entered as positive future inflows, PV may appear negative to represent the corresponding investment outflow.
Quiz questions
Which Excel function calculates a bond’s present value from its periodic yield, payment, number of periods, and face value?Answer: PV
For a bond, what does Excel’s RATE function solve for?Answer: The interest rate per payment period
A $1,000 bond has a 4% annual coupon rate and pays semiannually. What is its coupon payment per period?Answer: $20
When using RATE for an investor’s bond yield, why is the purchase price commonly entered with the opposite sign from future coupons and face value?Answer: The purchase is an initial cash outflow, while the coupons and face value are future cash inflows.
A bond pays semiannually, and RATE returns 2.5% per period. What is the nominal annual yield?Answer: 5.0%
Based on Principles of Finance by Julie Dahlquist, Rainford Knight (OpenStax), licensed CC BY 4.0. Adapted into a summary, flashcards and quizzes.