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@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@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....@cbs.dk Priv: pda...@gmail.com
______________________________________________
R-help@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.
