FI4000 Fundamentals of Valuation
1. Bond Pricing
Using Excel’s bond pricing function [=price(qualifiers)], determine the price of a bond with a settlement date of 1 July 2016, and a maturity date of 1 July
2036. The coupon rate of the bond is 6.5% and the bond pays coupons semi-annually with a yield to maturity (YTM) of 4.0%.
Prove Excel’s bond pricing is correct by determining the price by summing the discounted cash flows. Use cell addresses whenever possible.
Using Excel’s duration function [=duration(qualifiers)], find duration of a bond that has a settlement date of 1 July 2016, and a maturity date of 1 July 2036. The
coupon rate of the bond is 6.5% and the bond pays coupons semi-annually. The bond is selling at a yield to maturity of 4.00%.
A. Find the duration of the bond.
B. What if the coupon on the bond were 1.50% (but all else was unchanged) , what is the duration?
C. Explain, in a few sentences, the differences in the duration from Part A and Part B.
D. Find the bond’s modified duration using Excel’s modified duration formula (=mduration, etc.)
E. Make a small table (data table, if you like) that shows the Duration predicted price of the bond using modified duration for ytm’s ranging from 2.5% through
8.5% in increments of 50 basis points.
3. Term Structure of Interest Rates
Using Bloomberg, Yahoo.com/finance, MSN Money, The Wall Street Journal, Data Stream, Google.com/finance or any other reliable financial data service, find the yields
and maturities of at least ten US Treasury Stripped Principal securities. These are US government guaranteed zero coupon securities. (Data must be gathered all at
one time, not over days or even hours.) Maturities should range from 3 months to 30 years.
Plot the data in Excel on an XY scatter graph, assuring the maturity is on the X axis and yield on the Y axis.
What is the shape of the yield curve? What does that tell you about future interest rates?
General instructions: Use Excel to answer all questions, including non-quantitative questions. ALL ANSWERS MUST BE CLEARLY LABELED, clearly printed, ORGANIZED AND
EASY TO READ. Hand in one print version, as well as one electronic version (firstname.lastname@example.org) on or before the due date. The output font should be 10 or greater. You
will lose more points than you believe are justified if your OUTPUT font is less than 10 and/or you turn in difficult to read or disorganized work. On time
assignment: 15 minutes after class begins (or before). Not on time? 10 points off per day. Follow directions. Answer all questions asked. You are graded on
accuracy, completeness and organization, as well as your ability to follow directions. Make your work easy to read and follow. Put all questions on one spreadsheet.
You may use different worksheets in that file, but use one file. Remember, I am grading this. The harder it is to find your work and grade, the lower your grade will
All questions about the assignment should be directed to me only. Work on this assignment should be yours and yours alone. Don’t let anyone look at your Excel file
or copy any portions, ask or answer questions about your assignment to any other students. Any collaboration with others is a violation of the University’s policy on
Academic Honesty and can result in expulsion from GSU.