Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with bond yields
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!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with bond yields
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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Jerel" wrote: 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!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with bond yields
On Feb 7, 12:54 pm, Jerel wrote:
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.. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with bond yields
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. "Dave F" wrote: 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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Jerel" wrote: 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!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with bond yields
I have the analysis toolpak and bond addins turned on. So do my traders.
Under transition, everything is set to Excel. " wrote: On Feb 7, 12:54 pm, Jerel wrote: 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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Bond Price | Excel Worksheet Functions | |||
calcuate bond yields | Excel Worksheet Functions | |||
date and bond calculations | Excel Discussion (Misc queries) | |||
date and bond calculations | Excel Worksheet Functions | |||
Bond Yield Template with Payment schecule and chart (Excel) | Excel Discussion (Misc queries) |