ModeldrillPrice a bond from its cash flows
A financial modeling exercise from Modeldrill. Solve it with a live spreadsheet grid in your browser, with Fill right and Fill down, and it is checked the moment you submit.
The task
A bond pays a yearly coupon and returns its face value at maturity. Fill the annual coupon (B7), the price as the present value of all the payments at the yield in B4, using PV and shown as a positive number (B8), the price as a share of face value (B9) and a label (B10) that reads "Premium" when the price is above face value, "Discount" when it is below and "Par" when they are equal.
What this exercise teaches
A bond is an annuity of coupons plus one lump sum, and PV handles both in one call through its fourth argument. When the yield is above the coupon rate the price is below face value, and the other way round: price and yield always move in opposite directions.
A hint if you get stuck
PV(rate, periods, payment, future value): the yield is the rate, the coupon is the payment and the face value is the future value. PV returns a negative number for money you receive, so put a minus in front.
Try it now, free, no account needed
The first 15 tasks on Modeldrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every task, timed mode and the certificate.
Open this exerciseMore exercises in Valuation
- Unlevered free cash flow
- Discount factors and present values
- Mid-year convention switch
- NPV, IRR and profitability index
- Terminal value by perpetual growth
- Terminal value by exit multiple
- Enterprise value to equity value
- Dated cash flows with XNPV and XIRR
- Return on an investment with irregular dates
- Weighted average cost of capital
- Unlever and relever beta from peers
- Diluted shares with the treasury stock method
- Implied exit multiple and implied growth
- Unlevered to levered free cash flow
- Trading multiples from a peer group