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