FINA 4400: Financial Markets and Institutions
Chapter 3
End of Chapter Question #4
What is the value of a $1,000 bond with a 12-year maturity and an 8 percent coupon rate (paid semi-annually) if th
* 5%
* 6%
* 8%
* 10%
Face Value
(FV)
$1.000
$1.000
$1.000
$1.000
Number of Payments
(N)
Periodic Coupon Payment
(PMT)
Required Return
(YTM)
5,00%
6,00%
8,00%
10,00%
End of Chapter Question #6
What is the yield to maturity on the following bonds; all have a maturity of 10 years, a face value of $1,000 and a c
The bonds' current market values are $945.50, $987.50, $1,090.00, and $1,225.875, respectively.
Market Value
(PV)
Bond 1
Bond 2
Bond 3
Bond 4
Number of Payments
(N)
Periodic Coupon Payment
(PMT)
Face Value
(FV)
on rate (paid semi-annually) if the required rate of return is:
The Bond Value will be:
(PV)
s, a face value of $1,000 and a coupon rate of 9 percent (paid semiannually).
5, respectively.
The yield to maurity will be:
(YTM)
Chapter 3: Duration Application
Duration is a measure of how sensitive a bond price is to changes in interest rates. It is often used to predict the change in bo
of a bond if interest rates go up by 1%. Instead of calculating the bond's price using TVM, we can estimate the change in bond
As we saw in class, there are limitations to using durations in predicting bond price changes. Duration is based on a linear mo
exercise is to demonstrate those limitations by highlighting the prediction errors, which can be see graphically in Figure 3-7 in
For the first part of this exercise, we will calculate the "true" bond price given several scenarios of interest rates changes. The
For the second part of this exercise, we will calculate bond duration, both Macaulay and modified duration, and then use eac
first part. These predicted prices represent the straight line in Figure 3-7, which is called the Duration Model.
In part three, you'll describe the relationship between prediction error and (1) the direction of the interest rate change and (2
Part 1: True Bond Prices after Interest Rate Changes
Inputs
Settlement date
1/6/2021
Maturity
2/1/2028
Par $
1000
Coupon
7,125%
Frequency
2
Yield
[YIELD FORMULA]
$ Value of investment $
Bond Price
1.328,61
132,861%
New Yield
Change in Yield
New Bond Price
Bond Price Change ($)
Scenario 1
1/6/2021
2/1/2028
1000
7,125%
2
[INPUT]
[GIVEN IN SCENARIO]
[PRICE FORMULA]
[CALCULATE]
Part 2: Calculating Duration measures and using those measures to predict or estimate bond price changes
Macualay Duration
[DURATION FORMULA]
Modified Duration
[MDURATION FORMULA]
Scenario 1
Interest rate change
0,50%
Price change
Prediction Error
In this cell, you'll enter the formula that
you get when you rearrange the equation
∆𝑃
= −𝑀𝐷 × ∆𝑟𝑏 to solve for ΔP, which is
𝑃
the notation for the change in price. If
more information is need, this equation is
on page 84 in the textbook.
In this cell, you'll take the
between the true bond p
part 1 for each scenario a
from the predict bond pri
part 2 for each scenario.
Part 3:
#1. Describe the relationship between the prediction error and the direction of the interest rate change. Is this consistent w
Increase in interest rates:
Decrease in interest rates:
#2. Describe the relationship between the prediction error and the magnitude of the interest rate change. Is this consisten
Change of 50 bps:
Change of 100 bps:
predict the change in bond prices based on a specific change in interest rates. For example, let's say we want to know the price
mate the change in bond price using duration.
is based on a linear model, but the true relationship between bond prices and interest rates is not linear. The purpose of this
aphically in Figure 3-7 in the textbook.
erest rates changes. These prices represent the points along the bold black curve in Figure 3-7.
ration, and then use each to estimate or predict what the bond price will be given the same interest rate scenarios from the
Model.
erest rate change and (2) the size of the interest rate change.
Scenario 2
1/6/2021
2/1/2028
1000
7,125%
2
Scenario 3
Scenario 4
1/6/2021
1/6/2021
2/1/2028
2/1/2028
1000
1000
7,125%
7,125%
2
2
#1
#2
#3
#4
Scenarios
50 bps increase
50 bps decrease
100 bps increase
100 bps decrease
These are the true
bond prices after the
interest rate changes.
Scenario 2
Scenario 3
n this cell, you'll take the difference
etween the true bond price calculated in
art 1 for each scenario and subtract it
rom the predict bond price estimated in
art 2 for each scenario.
ange. Is this consistent with Figure 3-7?
Scenario 4
hange. Is this consistent with Figure 3-7?
ant to know the price
The purpose of this
cenarios from the
FINA 4400: Financial Markets and Institutions
Help Topics
THE PV FUNCTION
The present value formula, PV, "returns the present value of an investment," or "the total
amount a series of future payments is worth now." Examples include the present value
of a loan to the lender or the present value of $100 received from an investment a number
of years from now.
The syntax for this formula is:
PV(rate,nper,pmt,fv,type)
The first three variables in this function are required. Rate is the interest rate per period.
Remember that rate must be for the actual period. For example, a 10 percent annual
interest rate is equivalent to 10%/12, or 0.0083 per month.
Nper is the total number of payment periods. For example, a four year monthly loan
would have 48 periods. Pmt is the constant amount received or paid each period.
In many cases, this function can also be completed by typing in the formula for the
present value of a cash flow. See the example below.
Interest Rate
Periods
Cash Flow
Present Value
Present Value
7%
3
100
=C27/(1+$C$25)^c26
81,63
THE FV FUNCTION
The future value function, FV, "returns the future value of an investment," or the total
amount a single investment or series payments will be worth in the future. Examples include the
of an investment in a CD at the bank.
The syntax for this formula is:
FV(rate,nper,pmt,pv,type)
The first three variables in this function are required. Rate is the interest rate per period.
Remember that rate must be for the actual period. For example, a 10 percent annual
interest rate is equivalent to 10%/12, or 0.0083 per month.
Nper is the total number of payment periods. For example, a four year monthly loan
would have 48 periods. Pmt is the constant amount received or paid each period; enter 0 here
if you are calculating the future value of a lump sum and place that amount under pv.
In many cases, this function can also be completed by typing in the formula for the
future value of a cash flow. See the example below.
Interest Rate
Periods
Cash Flow
Present Value
Present Value
7%
3
100
=C53*(1+$C$51)^c52
122,50
THE RATE FUNCTION
Use Excel's RATE function to find the interest rate for a given payment and period.
The syntax for this formula is:
RATE(nper,pmt,pv,fv,type,guess)
The first three variables are required:
Nper is the total number of payment periods. For example, a four year monthly loan
would have 48 periods. Pmt is the constant amount received or paid each period.
pv is the the current value of the annuity (this value is entered as a negative, or outflow).
For example, suppose someone is willing to sell you a ten year annuity paying $15 each year fo
What is the rate of return on this annuity?
Periods
Cash Flow
Present Value
10
-15
80
Interest Rate
13%
THE (Macauley) DURATION FUNCTION
DURATION(settlement,maturity,coupon, yld, frequency, [basis])
The first five variables are requried:
Settlement is the day that the new bondholder assumes ownership of the bond.
Coupon is the securities annual coupon rate. Enter this as a percent.
Yld is the securities annual yield.
Frequency is the number of coupon payments per year. Annual frequency =1; semiannual frequ
Basis is optional, but refers to the type of day count for accrued interest.
THE (Modified Macauley) DURATION FUNCTION
MDURATION(settlement,maturity,coupon, yld, frequency, [basis])
The first five variables are requried:
Settlement is the day that the new bondholder assumes ownership of the bond.
Coupon is the securities annual coupon rate. Enter this as a percent.
Yld is the securities annual yield.
Frequency is the number of coupon payments per year. Annual frequency =1; semiannual frequ
Basis is optional, but refers to the type of day count for accrued interest.
Note: For both duration formula the security has an assumed par value of $100
ENTERING FORMULAS IN EXCEL
Select the cell in which you want to enter the formula and type an equal sign.
Enter the formula using standard formula operoters such as plus (+) and minus (-). For multiplic
Use a forward slash (/) for division; and the caret (^) for exponents.
You control the order of calculation by using parentheses to group operations that should be per
One of the best uses of formulas is a reference to another cell. The cell that contains the formul
as a dependent cell when its value depends on the values in other cells.
For example, the formula in the cell below calculates a value depending on what is entered in th
25
50
ent," or "the total
e present value
estment a number
t rate per period.
ercent annual
monthly loan
ch period.
mula for the
," or the total
e. Examples include the future value
t rate per period.
ercent annual
monthly loan
Bond Valuation
Duration
Help Topics
FINA 4400: Financial Markets and Institutions
Chapter 3
End of Chapter Question #4
What is the value of a $1,000 bond with a 12-year maturity and an 8 percent coupon rate (paid semi-annually) if the required rate of return is:
* 5%
* 6%
* 8%
* 10%
Number of Payments
(N)
Periodic Coupon Payment
(PMT)
The Bond Value will be:
(PV)
Face Value
(FV)
$1,000
$1,000
$1.000
$1,000
Required Return
(YTM)
5.00%
6.00%
8.00%
10.00%
End of Chapter Question #6
What is the yield to maturity on the following bonds; all have a maturity of 10 years, a face value of $1,000 and a coupon rate of 9 percent (paid semiannually).
The bonds' current market values are $945.50, $987.50, $1,090.00, and $1,225.875, respectively.
Market Value
(PV)
Number of Payments
(N)
Periodic Coupon Payment
(PMT)
Face Value
(FV)
The yield to maurity will be:
(YTM)
Bond 1
Bond 2
Bond 3
Bond 4
FINA 4400: Financial Markets and institutions
Help Topics
THE PV FUNCTION
The present value formula, PV, "returns the present value of an investment," or "the total
amount a series of future payments is worth now." Examples include the present value
of a loan to the lender of the present value of $100 received from an investment a number
of years from now.
The syntax for this formula is:
PV(rate,nper, pmt,fv, type)
The first three variables in this function are required. Rate is the interest rate per period.
Remember that rate must be for the actual period. For example, a 10 percent annual
interest rate is equivalent to 10%/12, or 0.0083 per month.
Nper is the total number of payment periods. For example, a four year monthly loan
would have 48 periods. Pmt is the constant amount received or paid each period.
In many cases, this function can also be completed by typing in the formula for the
present value of a cash flow. See the example below.
Interest Rate
Periods
Cash Flow
Present Value
Present Value
7%
3
100
1=C27/(1+$C$25)^c26
81.63
THE FV FUNCTION
The future value function, FV, "returns the future value of an investment," or the total
amount a single investment or series payments will be worth in the future. Examples include the future value
of an investment in a c at the bank.
The syntax for this formula is:
FV(rate,nper,pmt,pv.type)
The first three variables in this function are required. Rate is the interest rate per period.
Remember that rate must be for the actual period. For example, a 10 percent annual
interest rate is equivalent to 10%/12, or 0.0083 per month.
Nper is the total number of payment periods. For example, a four year monthly loan
would have 48 periods. Pmt is the constant amount received or paid each period; enter O here
if you are calculating the future value of a lump sum and place that amount under pv.
In many cases, this function can also be completed by typing in the formula for the
future value of a cash flow. See the example below.
Interest Rate
Periods
Cash Flow
Present Value
Present Value
7%
3
100
1=C53"(1+$C$51)^c52
122.50
THE RATE FUNCTION
Use Excel's RATE function to find the interest rate for a given payment and period.
The syntax for this formula is:
RATE(nper,pmt,pv,fv, type,guess)
The first three variables are required:
Nper is the total number of payment periods. For example, a four year monthly loan
would have 48 periods. Pmt is the constant amount received or paid each period.
pv is the the current value of the annuity (this value is entered as a negative, or outflow).
For example, suppose spmeone is willing to sell you a ten year annuity paying $15 each year for $8
What is the rate of return on this annuity?
Periods
Cash Flow
Present Value
10
-15
80
Interest Rate
13%
THE (Macauley) DURATION FUNCTION
DURATION(settlement, inaturity.coupon, yld, frequency, (basis])
The first five variables are requried:
Settlement is the day that the new bondholder assumes ownership of the bond.
Coupon is the securities annual coupon rate. Enter this as a percent.
Yld is the securities annual yield.
Frequency is the number of coupon payments per year. Annual frequency =1; semiannual frequendy =2; quarterly frequency=4
Basis is optional, but refers to the type of day count for accrued interest.
THE (Modified Macauley) DURATION FUNCTION
MDURATION(settlement maturity.coupon, yld, frequency, [basis])
The first five variables are requried:
Settlement is the day that the new bondholder assumes ownership of the bond.
Coupon is the securities annual coupon rate. Enter this as a percent.
Yld is the securities annual yield.
Frequency is the number of coupon payments per year. Annual frequency =1; semiannual frequency=2; quarterly frequency=4
Basis is optional, but refers to the type of day count for accrued interest.
Note: For both duration formula the security has an assumed par value of Shoo
ENTERING FORMULAS IN EXCEL
Select the cell in which you want to enter the formula and type an equal sign
Enter the formula using standard formula operoters such as plus (+) and minus (-). For multiplication use (*)
Use a forward slash (1) for division; and the caret (M) for exponents.
You control the order of calculation by using parentheses to group operations that should be perforned first.
One of the best uses of formulas is a reference to another cell. The cell that contains the formula is nown
as a dependent cell when its value depends on the values in other cells.
For example, the formula in the cell below calculates a value depending on what is entered in the cel to its right
25
50
Purchase answer to see full
attachment