Question 1

dt <- read_excel("Hw3data_2021.xlsx")
dt <- dt[complete.cases(dt),]
colnames(dt) <- c("T","Dt")
par.rate <- c()
denom <- c()
n <- length(dt$T)
denom[1] <- dt$Dt[1]
for (i in 2:n) {
  denom[i] <- denom[i-1]+dt$Dt[i]
}

dt$par.rate <- round(2*((100*(1-dt$Dt))/denom),4)
q1.table <- dt %>% select(T,par.rate) %>% filter(T%% 1 == 0)
knitr::kable(q1.table)
T par.rate
1 3.0339
2 3.3504
3 3.5747
4 3.7323
5 3.8441
6 3.9265
7 3.9920
8 4.0499
9 4.1063
10 4.1644
11 4.2257
12 4.2897
13 4.3545
14 4.4175
15 4.4754
16 4.5250
17 4.5636
18 4.5891
19 4.6007
20 4.5993
21 4.5878
22 4.5719
23 4.5600
24 4.5640
25 4.5994

Question 2

#yield shift
# Suppose yield shifts down 1 basis point

yield.shift <- -0.01
dt$par.shift <- dt$par.rate + yield.shift
get.bond.price <- function (par, c, n, yield, m) 
{
    pmt <- c * par/m
    p <- pmt * ((1 - ((1 + yield/m)^(-m * n)))/(yield/2)) + (par/((1 + 
        yield/m)^(m * n)))
    return(p)
}
dt$dv01 <- get.bond.price(100,dt$par.rate/100,dt$T,yield=dt$par.shift/100,2) - get.bond.price(100,dt$par.rate/100,dt$T,yield=dt$par.rate/100,2)

q2.table <- dt %>% select(T,dv01) %>% filter(T%% 1 == 0)
knitr::kable(q2.table)
T dv01
1 0.0097777
2 0.0191920
3 0.0282140
4 0.0368476
5 0.0451085
6 0.0530125
7 0.0605696
8 0.0677814
9 0.0746438
10 0.0811513
11 0.0872989
12 0.0930884
13 0.0985314
14 0.1036503
15 0.1084806
16 0.1130671
17 0.1174602
18 0.1217130
19 0.1258721
20 0.1299682
21 0.1340060
22 0.1379454
23 0.1416908
24 0.1450687
25 0.1478145

Question 3

part.3 <- (100*(dt$T*2-1)-1/(dt$par.rate/100/2)*(100+dt$par.rate/2*dt$T*2))*dt$Dt/100

dt$mac.D <- round((1+1/(dt$par.rate/100/2)+part.3)/2,4)
dt$mod.D <- round(dt$mac.D/(1+dt$par.rate/100/2),4)
q3.table <- dt %>% select(T,mac.D,mod.D) %>% filter(T%% 1 == 0)

knitr::kable(q3.table)
T mac.D mod.D
1 0.9930 0.9782
2 1.9549 1.9227
3 2.8813 2.8307
4 3.7709 3.7018
5 4.6235 4.5363
6 5.4403 5.3355
7 6.2223 6.1005
8 6.9705 6.8322
9 7.6854 7.5308
10 8.3683 8.1976
11 9.0189 8.8323
12 9.6377 9.4353
13 10.2252 10.0073
14 10.7817 10.5487
15 11.3084 11.0609
16 11.8066 11.5454
17 12.2777 12.0038
18 12.7239 12.4385
19 13.1477 12.8521
20 13.5517 13.2471
21 13.9386 13.6260
22 14.3098 13.9900
23 14.6666 14.3397
24 15.0087 14.6738
25 15.3346 14.9899

Question 4

invest.amount <-5000000 *dt$Dt[6]
invest.amount
## [1] 4494051

Question 5

dt5 <- dt %>% filter(T%% 1 == 0 & T <=5)
#convexity of 1yr bond
num1 <- 1*2*dt$par.rate[2]/2*dt$Dt[1]+2*3*(100+dt$par.rate[2]/2)*dt$Dt[2]
den1 <- (1+dt$par.rate[2]/200)^2*400
conv1 <- num1/den1

#convexity of 2yr bond
num2 <- 1*2*dt$par.rate[4]/2*dt$Dt[1]+2*3*dt$par.rate[4]/2*dt$Dt[2]+3*4*dt$par.rate[4]/2*dt$Dt[3]+4*5*(100+dt$par.rate[4]/2)*dt$Dt[4]
den2 <- (1+dt$par.rate[4]/200)^2*400
conv2 <-num2/den2

#convexity of 3yr bond
num3 <- 1*2*dt$par.rate[6]/2*dt$Dt[1]+2*3*dt$par.rate[6]/2*dt$Dt[2]+3*4*dt$par.rate[6]/2*dt$Dt[3]+4*5*dt$par.rate[6]/2*dt$Dt[4]+5*6*dt$par.rate[6]/2*dt$Dt[5]+6*7*(100+dt$par.rate[6]/2)*dt$Dt[6]
den3 <- (1+dt$par.rate[6]/200)^2*400
conv3 <-num3/den3

#convexity of 4yr bond
num4 <- 1*2*dt$par.rate[8]/2*dt$Dt[1]+2*3*dt$par.rate[8]/2*dt$Dt[2]+3*4*dt$par.rate[8]/2*dt$Dt[3]+4*5*dt$par.rate[8]/2*dt$Dt[4]+5*6*dt$par.rate[8]/2*dt$Dt[5]+6*7*dt$par.rate[8]/2*dt$Dt[6]+7*8*dt$par.rate[8]/2*dt$Dt[7]+8*9*(100+dt$par.rate[8]/2)*dt$Dt[8]
den4 <- (1+dt$par.rate[8]/200)^2*400
conv4 <-num4/den4

#convexity of 5yr bond
num5 <- 1*2*dt$par.rate[10]/2*dt$Dt[1]+2*3*dt$par.rate[10]/2*dt$Dt[2]+3*4*dt$par.rate[10]/2*dt$Dt[3]+4*5*dt$par.rate[10]/2*dt$Dt[4]+5*6*dt$par.rate[10]/2*dt$Dt[5]+6*7*dt$par.rate[10]/2*dt$Dt[6]+7*8*dt$par.rate[10]/2*dt$Dt[7]+8*9*dt$par.rate[10]/2*dt$Dt[8]+9*10*dt$par.rate[10]/2*dt$Dt[9]+10*11*(100+dt$par.rate[10]/2)*dt$Dt[10]
den5 <- (1+dt$par.rate[10]/200)^2*400
conv5 <-num5/den5

conv <- c(conv1,conv2,conv3,conv4,conv5)
year <- c(1,2,3,4,5)
conv.table <- cbind(year,conv)
colnames(conv.table) <- c("Years","Convexity")

knitr::kable(conv.table)
Years Convexity
1 1.440993
2 4.678763
3 9.554128
4 15.914576
5 23.620008

Question 6

#up 100 bps

get.spot.rate <- function(D,T,compound.times){
spot.rate <- compound.times*((1/D)^(1/(compound.times*T))-1)
return(spot.rate)
}

spot.rate <- get.spot.rate(dt5$Dt,dt5$T,2)
dt5$spot.rate.new.up <- spot.rate+.01
con2dt <- function(r,T){
  dt <- 1/(1+r/2)^(2*T)
  return(dt)
}
dt5$Dt.new <- con2dt(dt5$spot.rate.new.up,dt5$T)
new.price <- get.bond.price(100,dt5$par.rate/100,dt5$T,dt5$spot.rate.new.up,2)
original.price <- get.bond.price(100,dt5$par.rate/100,dt5$T,spot.rate,2)
dt5$estimate.change.up <- (-dt5$mod.D*.01*100)+1/2*(.01)^2*conv*100
dt5$actual.change.up <- (new.price-original.price)/original.price*100
uptable <- dt5 %>% select(spot.rate.new.up,estimate.change.up,actual.change.up)
knitr::kable(uptable)
spot.rate.new.up estimate.change.up actual.change.up
0.0403542 -0.970995 -0.9705321
0.0435713 -1.899306 -1.8957319
0.0458792 -2.782929 -2.7735523
0.0475196 -3.622227 -3.6051728
0.0486942 -4.418200 -4.3929122 
#down 100 bps


get.spot.rate <- function(D,T,compound.times){
spot.rate <- compound.times*((1/D)^(1/(compound.times*T))-1)
return(spot.rate)
}
spot.rate <- get.spot.rate(dt5$Dt,dt5$T,2)
dt5$spot.rate.new.down <- spot.rate-.01
con2dt <- function(r,T){
  dt <- 1/(1+r/2)^(2*T)
  return(dt)
}
dt5$Dt.new <- con2dt(dt5$spot.rate.new.down,dt5$T)
new.price <- get.bond.price(100,dt5$par.rate/100,dt5$T,dt5$spot.rate.new.down,2)

dt5$estimate.change.down <- -dt5$mod.D*-.01*100+1/2*(-.01)^2*conv*100

dt5$actual.change.down <- (new.price-original.price)/original.price*100
downtable <- dt5 %>% select(spot.rate.new.down,estimate.change.down,actual.change.down)
knitr::kable(downtable)
spot.rate.new.down estimate.change.down actual.change.down
0.0203542 0.985405 0.9849425
0.0235713 1.946094 1.9425245
0.0258792 2.878471 2.8691193
0.0275196 3.781373 3.7644028
0.0286942 4.654400 4.6293160