Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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


This question may require knowledge both in Excel and in finance. I have a list of the daily LIBOR quotes and i need to convert these into monthly returns. Sounds simple. Its not. I have the returns already but I cannot use the ones I have because I do not own the rights to display. Therefore, I have to figure out how to calculate them manually and check the calculation against the returns I have. in order to do this I need to figure out the price of the bond every day and use the difference in the daily price plus the coupon value to calculate the return. So essentially, I am creating a portfolio where I buy a bond and sell it the next day. It is a bond that matures in 1 month, at which time it pays coupon and face value which I have set equal to $100. I have tried using the Excel price formula to calculate the price but it can only calculate for quarterly, semi-annual, and annual bonds. The frequency will only allow the values 1, 2, and 4. I need to use 12. Does anyone know a way around this? I have already tried taking the percent change in the quote plus the value of the coupon plus one and using the Product function for all values in the month. This typically leaves me 5-10 basis points in either direction off the correct value. Any suggestions are greatly appreciated.


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!


I am working on a spreadsheet dealing with Bond Valuation and I am having trouble figuring out how to form an "IF" statement that will calculate the % change in price can anyone help me? Below is how I have my spreadsheet set-up.

Column A Column B
Row 7 Par Value $10,000
Row 8 Year to Maturity 100
Row 9 Compounding/Yr 1
Row 10 Coupon Rate as Nominal 10%
Row 11 Market Rate as Nominal 11%
Row 12 Interest Rates Fall 50 bps
Row 13 Bond Price Changes $432.90
Row 14 % Change in Price ?

Column E Column F

Row 4 PV(SUM)
Row 5 PV(FUN) $9,090.94


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.


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.


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

Thank you in advance,
Jack


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?