# [R] Excel Price function in R

Amelia Marsh amelia_marsh08 at yahoo.com
Sun Sep 27 20:19:01 CEST 2015

```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

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) {
cashflows   <- cashflows + 1
}

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.