Need help with bond yields

[Jerel]

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

 

[Dave F]

Post the formula your traders are using, the formula you use, and explain how
the Bloomberg terminal calculates its result. Without knowing those three
pieces of information I'm not sure how we can help.

Dave

 

[georgeswebmail]

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

Try adding the Analysis Toolpak in Addins..also, try checking what
your sheet is set for as far as how your sheet is set in Tools,
Options, Transition...perhaps an converted Lotus function..

 

[Jerel]

The formula my traders use is:

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

I want to use their formula but Excel will not let me edit it or write the
same formula as my own. I would use the default Excel yield formula:

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

but that returns different numbers than the first formula.

I do not know what the methodology is that Bloomberg or financial
calculators use to calculate a yield.

 

[Jerel]

I have the analysis toolpak and bond addins turned on. So do my traders.

Under transition, everything is set to Excel.