[R] Excel Price function in R

[peter dalgaard]

Given that this requires knowledge of both bond theory and Excel plus a fair amount of effort to understand your code, you are likely to be _so_ on your own....

However, I'll venture a guess that it has something to do with whether coupons should be discounted until payout or until maturity.  

There are some fairly straightforward numerical experiments that you could perform to get a handle on what is different in Excel: Graph the price as a function of maturity; do you see an abrupt change or does your curve and Excel's diverge in a smoothish fashion? If the latter, what is the order of magnitude of the divergence? Can you relate it to some of the parameters of your model? What happens if you go beyond 2 years to maturity? 3? 4? Etc.

-pd

> On 27 Sep 2015, at 20:19 , Amelia Marsh via R-help <r-help at r-project.org> wrote:
> 
> 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
> 
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

-- 
Peter Dalgaard, Professor,
Center for Statistics, Copenhagen Business School
Solbjerg Plads 3, 2000 Frederiksberg, Denmark
Phone: (+45)38153501
Email: pd.mes at cbs.dk  Priv: PDalgd at gmail.com