Dear Forum,
I am using trying to find price of bond in R. I have written the code in line with Excel PRICE formula. However, whenever the residual maturity is less than a year, my R output tallies with the Excel Price formula. However, moment my residual maturity exceeds 1 year, the R output differs from Excel Price function. I have tried to find out the reason for am not able to figure out.
Please guide me. Here is my code alongwith illustrative examples -
(I am copying this code from notepad++. Please forgive forgive for any inconvenience caused)
# MY code
add.months = function(date, n) {
nC <- seq(date, by=paste (n, "months"), length = 2)[2]
fD <- as.Date(strftime(as.Date(date), format='%Y-%m-01'))
C <- (seq(fD, by=paste (n+1, "months"), length = 2)[2])-1
if(nC>C) return(C)
return(nC)
}
# ________________________________________________________________________
date.diff = function(end, start, basis=1) {
if (basis != 0 && basis != 4)
return(as.numeric(end - start))
e <- as.POSIXlt(end)
s <- as.POSIXlt(start)
d <- (360 * (e$year - s$year)) +
( 30 * (e$mon - s$mon )) +
(min(30, e$mday) - min(30, s$mday))
return (d)
}
# ________________________________________________________________________
excel.price = function(settlement, maturity, coupon, yield, redemption, frequency, basis=1)
{
cashflows <- 0
last.coupon <- maturity
while (last.coupon > settlement) {
last.coupon <- add.months(last.coupon, -12/frequency)
cashflows <- cashflows + 1
}
next.coupon <- add.months(last.coupon, 12/frequency)
valueA <- date.diff(settlement, last.coupon, basis)
valueE <- date.diff(next.coupon, last.coupon, basis)
valueDSC <- date.diff(next.coupon, settlement, basis)
if (cashflows == 0)
stop('number of coupons payable cannot be zero')else
if (cashflows == 1)
{
valueDSR = valueE - valueA
T1 = 100 * coupon / frequency + redemption
T2 = (yield/frequency * valueDSR/valueE) + 1
T3 = 100 * coupon / frequency * valueA / valueE
result = (T1 / T2) - T3
return(result = result)
}else
if (cashflows > 1)
{
expr1 <- 1 + (yield/frequency)
expr2 <- valueDSC / valueE
expr3 <- coupon / frequency
result <- redemption / (expr1 ^ (cashflows - 1 + expr2))
for (k in 1:cashflows) {
result <- result + ( 100 * expr3 / (expr1 ^ (k - 1 + expr2)) )
}
result <- result - ( 100*expr3 * valueA / valueE )
return(result = result)
}
}
# ________________________________________________________________________
(ep1 = excel.price(settlement = as.Date(c("09/15/24"), "%m/%y/%d"), maturity = as.Date(c("11/15/4"), "%m/%y/%d"), coupon = 0.065, yield = 0.05904166667, redemption = 100, frequency = 2, basis = 1))
(ep2 = excel.price(settlement = as.Date(c("09/15/24"), "%m/%y/%d"), maturity = as.Date(c("7/16/22"), "%m/%y/%d"), coupon = 0.0725, yield = 0.0969747125, redemption = 100, frequency = 2, basis = 1))
(ep3 = excel.price(settlement = as.Date(c("09/15/24"), "%m/%y/%d"), maturity = as.Date(c("11/16/30"), "%m/%y/%d"), coupon = 0.08, yield = 0.0969747125, redemption = 100, frequency = 2, basis = 1))
# .......................................................................................................................................
# OUTPUT
ep1 = 100.0494
Excel output = 100.0494
ep2 = 98.0815
Excel output = 98.08149
ep3 = 98.12432
Excel output = 98.122795
While ep1 and ep2 match exactly with Excel Price function values, ep3 which has maturity exceeding one year doesnt tally with Excel Price function.
Kindly advise
With regards
Amelia