Chapter 10 Day 2 Excel Graph 10%, 10 year, 1000 face value bond PV=(YTM/2,NPR,PMT,FV)à in excel Example for first row= (.01/2,20,50,1000) Example for second row=(.02/2,20,50,100)
1-In order to calculate price using PV in the Second column, first row, put in =PV(1%/2,20,50,1000). Once excel computes this, the formula can be used for the following yield. 2- To create a scatter chart go click the insert tab-then scatter plot.
3 years, 8% coupon, $1000 face value
Suppose price of bond is trading at $949.24. Goal-find interest rate Price of bond=present value of cash flows. I=semiannual interest rate I=YTM (Internal Rate of Return)
40/(1+i/2)^1 + 40/(1+i/2)^2+…..40+1000/(1+i/2)^6=$949.24 ( This shows how to set up equation on an exam)
On exam he will ask two things 1- What is the equation to find out YTM. 2- How to solve it. Uses excel to solve the problem. How to use excel to solve problem: PV =-949.24 NPER=6 ( three years *2) FV=+1,000 PMT= $40 YTM=? · Use the rate function in excel · =Rate(NPR,PMT,PV,FV) · =Rate(6,40,-949.24,1000 · Show at least two decimals as a percentage on exam* · .05*2=10%. (YTM=i*2)
7 year bond, 9% coupon, sells for $914, find YTM PV=-914 FV=1000 NPR=14 PMT=.09*1000=90/2=45 YTM=?
Use the rate function in excel =Rate(NRP,PMT,PV,FV) =Rate(14,45,-914,100) =.0539*2 =.1078 or 10.78%
15 year ,zero coupon bond, Face value $1000, Price $209, Find TYM
PV=-209 FV=1000 NPR=30 PMT=0 YTM=? = Use rate function in excel =Rate(NPR,PMT,PV,FV) =Rate(30,0,-209,1000) =5.36%*2 =10.71%
Interest Rate on Bonds
R=R*IP+DRP+MP+LRP+CP
· Real rate of interest is determined by supply and demand · Pay a higher interest rate for inflation
Type Rate 3-month T Bill .01% 10-year Treasury Bond 3.5%
· Difference between these two is maturity. Investors need an incentive to invest for three years. Inflation could be taken into account as well.
GE 10-Year Bond 5.48% · Difference between US Treasury bond and T-Bond is default risk. GE is not default free like the United States government. Investors want an extra 2% for investing- explains why how/why investors are risk averse. Ford 10-year Bond 11.42% · Difference between Ford and GE is default risk. New Car ( 5 year) 7.12% 15-30-year mortgage 4.59/5.15% · Difference between the two is credit risk. If someone defaults on a car- they will most likely get back less than in a home. ( Homes don’t depreciate as much as homes) Credit Card 13.46 · There is no collateral for the credit card companies.
Moody’s S&P A AAA Aa Aa A A Baa… Bbb… C C Default · The farther down the alphabet- the greater investment risk · S& P will repeat the same letter- example (CCC,CC,CC) · Moody’s will use lower cares letters( Aaa,Aa,A,Baa,Ba,Ba… and so on) · Anything after Baa(Moody’s) or BBB( S&P) are junk bonds. Most investors cannot/will not invest in these). |
Chapter 10 Day 2 |