Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



How To Calculate Bond Price?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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!

View Answers     

Similar Excel Tutorials

Goal Seek in Excel
Find a desired result by having Excel automatically change input values in Excel. Using Goal Seek, you setup a for ...
Calculate Someone's Age in Excel
How to automatically calculate someone's age using Excel.  This method is simple and will update every year so tha ...
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 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 formu ...

Helpful Excel Macros

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.

Thanks in adv.


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.

Please help me out

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


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
answers better!!

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.


Ladies and gents,

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.
Thanks in advance for any help you could lend.


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

Thank you in advance,
Jack




This function has always given me problems. I always get #NUM and when it provides a answer and yield which is different from Bloomberg Platform.. Which is the standard of the financial world.. I am trying to create a large spread sheet and need help.. Many of the bond issues pay interest monthly can Excel be used to calculate a 12 interest payment bond?

Thanks,
Asles

I am trying to calculate the present value of a bond where the first interest payment was only a partial period. I used the pv value function and plugged in 9.3 for the period (vs 10 if bond had been held for full time). This is producing a different result then using the NPV on a monthly basis where I put 0s in for nonpayment months and adjusted the interest rate monthly. Can I not use the PV function with partial time periods?


I am using the price function to amortize the book value of a bond that receives monthly interest payments. The problem is that the formula does not calculate monthly periods, only annual, semi-annual, and quarterly. Does anyone have a suggestion?


Greetings,

Now, the calculation for a bond is quite obscure. For example, a move from 119.00 to 120.00 is a total of 32 points. so, once the price is at 119.32, the next one point move will take price to 120.00, and so on 120.01, 120.02, 120.03 to 120.32 then 121.00 etc.

Here is my original formula and how it was incorrect with a given example;

A B
1 Sell 125.08
2 Buy 124.32
3 Profit 24.27

The formula I used was: (B1-B2-0.0015)*(0.32*100), the answer excel gave in B3 was 24.27.

The correct answer should be 8.9985, the 0.0015 is simply the commission fee. The reason the answer should be 8.9985 is because once price gets from 125.08 to 124.32, that is 9.00 point profit, minus 0.0015 commission would equal 8.9985.

Example: 125.08 to125.00 = 8.00 , 125.00 to 124.32 = 1.00 = Total Profit 9.00 less 0.0015 commission = 8.9985

This formula I used seemed to work well with certain scenarios, but once price gets to around that 32 tip off point, the formula I use is not correct.

If anyone can assist me with a formula I can use in one cell only, I would greatly appreciate it. Look forward to hearing from you.

Regards,

Vladmir.


Please help. I am trying to update a field in a Table via a Module looking at a field and its contents. I want to show an answer based on the following via a Function

Table name = tblActivity
Field = Capability (Has values either 1, 2, or 3)
Field to update = Bond
Field to calculate Bond values = Price * Exchange

If you can help that will be an amazing thing