[R-SIG-Finance] Excel Price function in R for Bonds

[Amelia Marsh]

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