Email:      Pass:    Pass?

E-mail:

# How To Calculate Bond Price?

Could someone please give me detailed instructions on how to calculate the price of a bond (if given all the other elements, such as the coupon rate, etc) using excel? I know it has something to do with the payment function but I'm just really confused!
Thank you so much!

## Similar Excel Tutorials

Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
FV Function - Get the Future Value in Excel
The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...

Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Output the Worksheet Name in a Cell in Excel - UDF
- UDF to return the name of the worksheet in Excel on which this function is located. There are no arguments for this fun
Display the Print Window in Excel
- This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

## Similar Topics

I am trying to calculate the price of a bond half way through the total period.

For eg if the Bond FV is 100 & disc rate is 10% then the price at the start will be 100/1+10% = 90.91. (12 months)

Now in case I want to calculate the price of the bond after 6 months assuming the same discount rate.

Pls help me.

hello.

I am working to make a bond template which is lump sum principal payment at the end.

I set up cells for period as =IF(ROWS(A\$22:A22)-1>B\$9,"",ROWS(A\$22:A22)-1), so once I put the number of payment the excel will generate number of periods for me automatically.

Also I used IF function for the PMT and Balance.

The problem is that this bond needs to pay off principal at the end of the amortization schedule(at the maturity date).

Can you help me to set it up?

just in case I give you information for the bond.
Face amount 5,000,000 Stated rate 6% semi-annual payment
bond term is 10years.
there is no market rate so I calculate the IRR.

Thanks

Hi,

Could you please help me understand the difference betwen Yield formula and YieldMat? Both formulas ask for the coupon in % but it is not clear what value does it calculated percentage from (because it does not ask for the par value)? Also can't figure out how it treats accrued interest.

Also what is the best to use for my case? I am trying to calculate Yield for a convertible bond which will convert to a certain sum on a certain date. I need to monitor the yield that changes with price of underlying shares(in order to make a decision whether to convert or to hold the bond).

Here are parameters that I have:
Settlement 8/16/10
Maturity 9/30/10
Annual Coupon 5% (of \$1000 par)
Last coupon paid on 5/15/2010
Price 1,255
Redemption Value 1,210
Frequency of coupon 2
Basis 3

PS: Redemption value is fixed because the bond will convert at that date at a set price.
I would appreciate your help very much.
Elena.

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

Hello, Sry I am somewhat of a beginner - I am trying to build a bond model but cant seem to figure out how to calculate the number of days since the last coupon was paid.

Assumptions:
> semi-annual coupons - if bond issued jan1, then coupons are paid on jan1, and on july1.

If i choose a random date to buy a bond, I need to figure out how much interest is accrued/owed to the current bond holder...it is determined based on the number of days since the last coupon

Example:
Bond issued on Dec1/00
I want to buy on Mar15/04

I need to formula to know that the closest and most previous bond payment occured on dec1/03, and calculate number of days from then till mar15/04
= 105 days

Anybody have any idea?

CHeers!

Hi Guys,
Iam trying to write a code that calculates individual cash flow and total year cash flow for an portfolio. Ive approached this in a "step by step" excel formula based way. But i suspect its a smarter way to do this.

Please find a screen shot of my sheet here before "processing":

what I need help/suggestions about is how to calculate Cash flow for each bond and year.

A bond pays a coupon as a percentage of face value (its 100) and a principal (its 100) when it expires. (Given my maturity.)

So bond 1, pays coupon of 7 in year 1 and 2, while in year 3 its pays coupon 7 + principal 100.

So for year 1 cell 4G, i need the formula to add only coupon as no bond has expired in year 1. The coupon is 7 for bond 1 as face value is 100, (100x0.07).
While for year 3, in cell 4I i need coupon 7 + 100 principal etc.

It supposed to look like this when calculate: (i typed them in manually):

Here's code we use to calculate bond and principal cash flow
Code:

```Sub BondCashflow()

' Input parameters to determine bond cashflow

Principal = Range("B1").Value
Coupon = Range("B2").Value
Maturity = Range("B3").Value
MyYear = Range("B4").Value

' Initialise Cashflow

Cashflow = 0

' One way to compute cashflow
' If MyYear < Maturity Then Cashflow = Coupon
' If MyYear = Maturity Then Cashflow = Principal + Coupon
```

Greatly appreciate any info on what type of code and suggestions.

I've been using the price function to check some work I've been doing.
I'm able to replicate the Excel values for A/A, A/365 and A/360 day
count options to a large number of decimal places for all days during
the life of a bond EXCEPT between the penultimate and the last coupon
of a bond when the price function appears to drop almost twice as much
as I think it should. I can see no reason why it should and I like my

I can't find out if this is an error that has been discovered before or
if I've missed something.

Look forward to seeing if someone else has had this problem.

Thanks

Mike

I have a tricky calculation that I am trying to automate in excel. In construction we have to have surety bonds on our projects. These are often referred as Performance & Payment Bonds. They they are based on contract amount, but have have different rates.

1. First \$500,000.00 = 1.125%
2. Next \$2,000,000.00 above the first \$500,000.00 = 0.955%
3. Next \$5,000,000.00 above 1 & 2 above = 0.595%

Example 1: If my contract price is \$1,200,000.00, then the first \$500,000.00 is calculated at 1.125%. The balance would be calculated at 0.955%. The sum of these calculations will be my bond rate

I am trying to break the contract price down. First I have a cell I use this
=IF(M28

Hi all,
The yield function in excel does not give the right value if I play around with the redemption value(% of par). I have checked this with other available online bond yield calculators such as
http://www.moneychimp.com/calculator...e=calc_bondytm

If I change the redemption value to 50 and the bond price to \$50, ideally the coupon rate and the yield should be equal for any time period. While this happens in the calculator mentioned above, Excel gives me a different number. Is this actually a bug or am I missing the point?

Test this in Excel...

=YIELD("6/30/2009","6/30/2022",0.0958,50,50,2,0)

Also test different redemption and price values and compare them with the calculator above.

I need to write a formula that will calculate the cost of a construction performance and payment bond. The bond is calculated as a percentage of the contract amount. Let's just say the contract amount is in cell A1 on the spreadsheet. The cost of the bond will be 2.5% of the first \$100,000 of contract, 1.5% on the next \$500,000 of the contract, and then 1.0% on the next 2,000,000 and so on. I have put the table below that references every percentage we would use. Remember that the formula needs to work even if the contract amount is as small as \$1 or as large as millions of dollars. I am terrible at these types of formulas and could use some help. Thank you for your assistance!

BOND RATE BOND AMOUNT
2.50% Up to 100,000.00
1.50% On the Next 400,000.00
1.00% On the Next 2,000,000.00
0.75% On the Next 2,500,000.00
0.70% On the Next 2,500,000.00
0.65% Amount Over 7,500,000.00

via previous thread "Formula for bond price". Slight "bug" in PRICE function still exists in Excel 2007.

Good day!

I am trying to validate the PRICE function using the longhand formula provided in Excel Help.
I am able to get the exact price for my sample bonds, except for a particular bond with the following details:

Settlement Date 03/27/2013
Maturity 06/29/2013
Coupon Rate 9.625%
Yield 0.350%
Redemption 100
Frequency 2
Basis 30-day rule

Using the PRICE function, result is 102.366057042189

Using the long-hand formula, with below details: my result is 102.366097039549 (diff of -0.00004)

DSC 92
N 1
A 88
E 180

Is there any reported bug on the PRICE function? Or were the values I used for variables DSC, N, A and E incorrect?
I get the exact result (exact zero difference) for all other bonds except this one.

Appreciate immediate help. Thank you!

ok got a quick question i hope assuming say i have a bond with the following details

Bond price of \$93
Face value of \$100
semiannual coupons of 6.5%
bond matures in 5 years

now this is where i lose sight of how to use an excel formula to further calculate this problem. it states quote "if the required market returns are 1 percent higher in two years and one decides to sell at that time, what is the total return of the bond, and what is the investment yield?

any assistance on how to run a problem such as this through excel is greatly appreciated i understand the basic us of the =price formula as well as the =yield formula but do not know how to use them in regards to this scenario or if a another formula should be use.

Hi,

I'm new here, so first of all hi!

Came across this site while stuck on a problem.

In the attached sheet I am trying to calculate the Yield on a bond...which I have done. However, I need to do this multiple times for instance if the bond was at different prices...cells M7:M47.

I have been told that the spreadsheet is set up correctly so I could use a table array and XIRR (like used to calculate the Yield to call and YTM),,,,,but I do not know how to use the XIRR function with a table, plus the data is not continuous. So for example cells N6 AND O6 have the calculation for the yieldbased on the price of the bond being 104. I need to complete the table columns N7 and O7 downwards with the XIRR if the bond was at that price and output into the empty cells in the table.

Could someone be as kind to point out how to do this and perhaps show me? it looks like a really fast powerful tool, but I cannot get it to work.

Thanks in advance...any questions about the workbook, just get back to me and I will answer any questions as at first glance it is not the easiest to follow.

I work for a construction company where we have to calculate payment bonds for projects that we bid. I am trying to make a spreadsheet that satisfies the perameters, but cannot seem to get the formulas to work out.

Bonds on construction projects are "stepped", meaning that as the project cost escalates, the amount of bond you pay per thousand dollars goes down.

Bond rate calculation explanation:
The first \$500,000 (\$1 thru \$500K) of a project has a bond rate cost of \$9.00 per thousand dollars.
The next \$2,000,000 (\$500K thru \$2.5M) has a bond rate cost of \$7.50 per thousand dollars.
The next \$2,500,000 (\$2.5M thru \$5M) has a bond rate cost of \$6.00 per thousand dollars.
The next \$2,500,000 (\$5M thru \$7.5M) has a bond rate cost of \$5.00 per thousand dollars.
Any amount over \$7.5M has a bond rate cost of \$4.00 per thousand dollars.

To follow this formula for a project with a total cost of \$10,125,000, I would have:
(first \$500,000/\$1,000) 500 * \$9.00 = \$4,500
(\$500K thru \$2.5M/\$1,000) 2,000 * \$7.50 = \$15,000
(\$2.5M thru \$5M/\$1,000) 2,500 * \$6.00 = \$15,000
(\$5M thru \$7.5M/\$1,000) 2,500 * 5.00 = \$12,500
(\$7.5M thru \$10,125,000/\$1,000) 2,625 * 4.00 = \$10,500
Total Bond = \$57,500

Conversely, if I bid a small project, say \$2,615,000, it would look like:
500 * \$9.00 = \$4,500
2,000 * \$7.50 = \$15,000
115 * \$6.00 = \$690
Total Bond = \$20,190

Could someone please point me in the right direction as to what type of formula I would use to make such a spreadhseet? I tried to do an "If, Then" formula, but couldn't ever get it to work out. Where could I be missing it?

Any help would be greatly appreciated.

I need to know exactly how the Duration Function works in excel. For such a coupon bond (ex. settlement date 31/12/2009, maturity date 31/12/2011 with frequency 1) I managed to do it manually, but when I need to calculate the duration for a coupon bond such as this:

settlement date 31/12/2009
Maturity date 16/02/2012
frequency 2
Coupon rate 0.06
Yield 0.02

I'm not getting the exact answer when doing it manually.

Any help much appreciated

I work for an investment management firm and I am having trouble calculating municipal bond yields with Excel. My problem is two fold.

The first has to do with the how you write the formula or input the data. My traders use the following format:

yield(trade price, redemption price, rate, 2, settlement date, redemption date, "MUNI")

If you use insert function in Excel, the format is:

yield(settlement date, redemption date, rate, trade price, redemption price, 2)

If one of my traders sends me a spreadsheet using the first format/formula, it will properly calculate on my PC. But if I try to edit the formula in any way or replicate it on my own, Excel returns #NUM! We all appear to be using the same add-ins (I have the bond add-in installed).

The other problem is that if I use the second format, the one built into Excel, it returns the wrong yield. The first format of course returns the correct yield. How do I know it is wrong? If I check the yield using a financial calculator or a Bloomberg terminal it matches what the first formula returns.

Any help would be very much appreciated!!!

I need to write an Excel function that returns best price of a government bond.

The actual handwritten formula is quite complicated, impossible to display
here, and the input values for this formula are-----------------
Accrued days, Number of coupon period days, Days from settlement to next
coupon date, coupon periods per year, number coupon periods between
settlement & redemption, annual yield, discount rate.

The PRICE function in Excel has limitations on the YIELD argument (Must be positive) and the Frequency (number of coupon payments per year - wont accept 12 for monthly)

How can I price a bond that pays on a monthly basis or has a negative yield

I've been using a tutorial and some youtube to try and calculate the price of the bonds offered at my brokerage. Currently I show them being high. Why is that?

The attached Excel sheet shows my calculations.The image just below my calculations is of an actual bond from my broker. Prices do not match.

What am I doing wrong?

I am stymied by the fact that this function does not decrease the accrued interest when a coupon payment date is passed. For instance, a bond with a 5% coupon rate that pays interest semi-annually, issued on 11-15-2006 with a first coupon date of 5/15/2007 should have accrued of \$2.5 (per hundred face amount) on 5/15/2007 and accrued of \$.0138 on 5/16/2007. The accrint function instead calculates the accrued on 5/16/2007 as \$2.5138 in essence failing to account for the fact it just passed the 5/15/07 coupon payment date. What am I doing wrong?

I need to calculate the yield to maturity in excel of a 10 year bond with a 6% coupon and a face value of \$100 that is currently selling for \$103.50.

I am told to use the IRR function, but I have not been able to get it to work and do not understand Excel's instructions on how to use it.

Can someone please explain how I would set this up in Excel for it to work? The function seems so simple, yet I am stuck. Thanks!

I am not that fimilar with Exel. Can you calculate the "price of a
convertible bond" using Excel? If so , how?

Hi,

Firstly I'm unable to upload a shot of my screensheet, I'm hoping my explanation is clear enough.

I have a list of bonds who's prices are either above or below some defined limits. Those limits are 95 and 101. The list of bonds includes partial duplication, in that I can have more than 1 row of the same bond but each with a slightly differing price to each other (though all are outside of the limits)

Is there a formula I can use that will allow me to flag/identify a bond price (for an identical bond security) that is the furthest away from the limit?

E.g. (parts in red is what's required)

Column: A (Bond ISIN) - B (Bond Name) - C (Bond Price) - D (required flag)
ISIN1 - Bond1 - 101.25 - TRUE
ISIN1 - Bond1 - 101.15 - FALSE
ISIN1 - Bond1 - 101.05 - FALSE
ISIN2 - Bond2 - 94.90 - FALSE
ISIN2 - Bond2 - 94.75 - TRUE
ISIN2 - Bond2 - 94.80 - FALSE
ISIN3 - Bond3 - 102.10 - FALSE
ISIN3 - Bond3 - 102.25 - TRUE

The data will be/can be ordered like above. An additional is that this will then be done on a fund holding level, e.g. different funds can hold the same bond (ISIN) in which case the sort will be first by fund, then by bond but even if the same bond exists in different funds, I'd still like to identify that price furthest from the limit defined.

I can do this via a macro, but would prefer in this instance to use a formlua if possible.

Can this be done?