[R] Excel Price function in R

[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