Excel For Finance Tips - What is Yield ?

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,

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.

Dear Sirs,

I am looking for a ms excel template.

Say I am investing $100 every month.
So Excel sheet should give me result of
compounded yield say after 5 years or 10 years and so on !!!

Thanks for kind help

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.

I'm trying to set up a formula that will determine the last amount of cash flow in a stream of payments that is required to reach a certain return. For example, if I have these cash flows YR 0 -50,000,000, YR 1 4,922,955, YR 2 7,086,093, YR 3 8,486,159, I want a formula that will determine how much cash flow I need in YR 4 to achieve a 10% IRR. Any suggestions?

hey guys, so i am trying to import

HTML Code:

http://finance.yahoo.com/q/hp?s=MSFT

that type of information into excel via macro. i can do it by importing it via webquery but the problem is that i need more than one page (go all the way down and there is a NEXT button to get information that is older)

so i am trying to import not just one page, but lets say import 5 or 6 (which will give me a years data on a stock vs. the 3 months)

and then down the road, id like to get rid of the line that shows the dividend payout, but that isn't too important right now.

is this possible what i am asking?

Does anyone have a Bond Yield Template, payment schedule and chart for excel?


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi,

I have a challenge building a financial model in excel. I'm modeling the profit margin of a potential contract with different time frames. Unfortunately, due to upfront revenue (with no underlying cost) incurred only in year 0, my profit margin % decreases as I extend the time frame. This causes problems with contract evaluation as it would conclude that shorter contracts are always better.

For example

Category YR 0 YR 1 Total
Rev 30,000 100,000 130,000
Costs 0 70,000 70,000
Net Profit 30,000 30,000 60,000

Margin 46%

I am setting up my portfolio in yahoo finance and wanted to know if there is a way to link it up with my microsoft 2003 excel spreadsheet and yahoo stock portfolio so that I can have it automatically update in real time.

Please help

Hi

I have made a excel sheet where i have put the names of several stocks in which i am interested. I have also used the facility of Import external data-New web query and given the Yahoo finance link
However, i am having trouble in importing the data as it says the file name should not contain ? wherein the yahoo web query contains the ? in the middle.
Is there any solution.
The surprising part is that i can edit the query and get the rates but it does not refresh, which means that i have not given the wrong path.
Also 1 out of 10 times it gets refreshed but 9 times it gives invalid web query
I shall be thankful if the members can help me out
I am attaching my Excel sheet for ready reference
Thanks