Formula for bond price

**Dracan** · 08-30-2007, 12:26 PM

I must write a function for bond price in some other language (doesn't matter) so I looked for a formula for bond (PRICE) price in help (also on http://office.microsoft.com/en-us/ex...092191033.aspx).
There is a good explanation of this calculation and first I wanted to check this formula in Excel .

I wrote a simple example for one bond with one year coupon and compared results of Excel function and my formula and noticed, that there is a small difference. Results are the same only when settlement date is on coupon date (ie. 17.04.2007). When it is between coupons (ie. 04.06.2007), the results of my formula is different. I've checked formula for accrued interests (third part of formula) with function ACCRINT and this part is ok, so difference must be in first two parts of formula.

What did I do wrong?

Regards, Dracan

My example was (copy text in blank woorkbook in A1):
Settlement:
04.06.2007
Maturity:
17.04.2008
Rate:
3,25%
Yld:
4,23%
Redemption:
100
Frequency:
1
Basis:
1
DSC:
=A4-A2
E :
366
N:
1
A:
=A2-(A4-A18)
Excell price:
=PRICE(A2;A4;A6;A8;A10;A12;A14)/100
Formula:
=(A28+A30-A32)/100
1. part
=(A10/((1+A8/A12)^(0+A16/A18)))
2. part
=(100*A6/A12)/((1+A8/A12)^(0+A16/A18))
3. part
=(100*A6/A12)*A22/A18
Difference
=A24-A26

**FrankBoston** · 08-30-2007, 05:40 PM

1) I suggest that you upload a zipped workbook that contains your formulas, with comments in nearby cells describing what you are doing.

2) How big is the difference as a fraction of the answer? Different implementations of a formula always show small differences due to round-off error in the floating point arithmetic.

**Dracan** · 08-31-2007, 02:35 AM

1. Here it is (workbook with example). I think it's self explanatory (it's a relatively simple formula).

2. The difference is small, but it has to be exact because of reverse calculations (yield). If I use function YIELD on result of my formula, it gives me wrong result, so it has to be accurate.

What did I miss?

**FrankBoston** · 08-31-2007, 11:20 AM

The excel formulas for Price and Yield have arguments

PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)

Your formulas add arguments for Discount, E, N, and A.

Your formula is more comprehensive, so of course it will differ from the Excel version. You should compare Excel to your formula for values of Discount, E, N, and A that have no effect.

**Dracan** · 08-31-2007, 04:29 PM

These extra parameters are part of formula I got from Excel help and are all computed from other parameters (except N, which is in my example 1).

DSC = number of days from settlement to next coupon date.
E = number of days in coupon period in which the settlement date falls.
N = number of coupons payable between settlement date and redemption date.
A = number of days from beginning of coupon period to settlement date.

It's strange that if you use that formula (as is stated in Excel help) you don't get the same result. I've used the same method with formula for YIELD and that formula checks ok.

I must have used some parameter(s) wrong, but I can't see which.

Or is it something else?

**FrankBoston** · 09-01-2007, 01:04 AM

Thank you for pointing out my incorrect statement in my prior post about the extra arguments.

I have been studying the situation. According to my analysis, in my version of Excel 2003 The Excel function for bond price
PRICE() is INCORRECT according to Microsoft Published documentation and according to the best mathematical analysis.

I have attached your workbook with my additional analysis. I was able to match the calculations in Excel PRICE() by calculating the present value of the bond payment and coupon at maturity using a linear interpolation for the last period, instead of the geometric interpolation which is more exact and which is the formula used in Microsoft Documentation that you linked to in your original post.

Say the Discount Rate or desired Yield is 4%. In other words, when we invest $100 for 1 year (365 days) we want $104 in return. The Discount Multiplier is 1.04 for future value, and the Discount Divisor is 1/1.04 = 0.961538462 for Present Value.

To determine a price for a bond purchased 182 days before maturity, we need to discount the value of the bond repayment and the final coupon for the 182 days.

Microsoft documentation says that this formula applies:
Price for bond principal = 98.06333608 = 100 / ((1 + .04) ^ (182/365))
    The " ^ " carat symbol is floating point exponentiation.

But the incorrect formula actually used is:
Price for bond principal = 98.04448265 = 100 / (1 + (.04 * 182/365))

The bad price in this case is about .02% less ($.20 per $1000) than the correct price, which is why the error has gone unnoticed until you tried to duplicate the formula.

**Dracan** · 09-01-2007, 02:08 PM

Thanks for your analysis, I blindly believed in Excel formula and thought I was doing something wrong. That means that also the YIELD function has a similar error (as it uses the same interpolation).

Because our users use Excel for all their calculations, I'll probably use the same (linear) formula as Excel does, but now I can at least explain this issue to them and give them a choice what to do about it.

You have been most helpful. Thanks again.

**sb84** · 07-13-2010, 01:08 PM

Hi FrankBoston,

You mentioned in your thread dated Jul5, 2007 that " The Excel function for bond price
PRICE() is INCORRECT according to Microsoft Published documentation and according to the best mathematical analysis."
Can you pass on the details where I can find this documentation.

I am trying to calculate the accrued interest for a bond using the PRICE formula in Excel 2007. It is a 3 year bond with semi-annual frequency. I am getting the same accrued interest for all periods except for the last coupon period (Attaching the Excel work-sheet for your reference). When I calculate the price manually using the formula in excel help, I am getting the same price for all periods. I fail to understand what is Microsoft doing in that last period to give me a different price.
Can you help?

Thanks

**sb84** · 07-13-2010, 01:16 PM

I meant thread dated - Sept 1, 2007.
Sorry about that!

**FrankBoston** · 07-13-2010, 02:01 PM

To SB84,

The Excel specifications for computing bond prices are in Excel Help "Bond Price".

The full analysis of the slight error in Excel that I worked on is in the above discussion and the attachment Test1.zip above. That file also has an explanation of what each part of the Excel published analytical formula is doing.

At the moment, I can't read .xlsx files in my creaky Excel 2003.

My guess, without being able to look, is that you have an error somewhere in your formula or interpretation. Probably not the computational error of this thread.

I suggest starting a new thread for help, from someone who has Excel 2007.

**rzesquieres** · 05-31-2011, 08:29 PM

Hi,

i was able to make a VBA function for Access 2003. Maybe this would help.

Function PRICE(settlement As Date, maturity As Date, rate As Double, yld As Double, redemption, frequency)

'Initialize Variables

Dim COUPNCD As Date
Dim COUPPCD As Date

coup = 0
cppayleft_exact = WorksheetFunction.Days360(settlement, maturity) / (360 / frequency)
CPPAYLEFT = WorksheetFunction.RoundUp(cppayleft_exact, 0)

mosToMat = (CPPAYLEFT - 1) * (12 / frequency)
mosToMatprev = (CPPAYLEFT) * (12 / frequency)
COUPNCD = DateAdd("m", -1 * mosToMat, maturity)
COUPPCD = DateAdd("m", -1 * mosToMatprev, maturity)

N = CPPAYLEFT
A = WorksheetFunction.Days360(COUPPCD, settlement)
k = 1
e = 360 / frequency
DCS = e - A

yfactor = 1 + (yld / frequency)

prin = redemption / (yfactor ^ (N - 1 + (DCS / e)))

Do Until k = N + 1

num = 100 * (rate / frequency)
den = yfactor ^ (k - 1 + (DCS / e))
coup = coup + (num / den)

k = k + 1

Loop

acr = 100 * (rate / frequency) * (A / e)

PRICE = prin + coup - acr

End Function