Need Help With Bond Yields |
Need Help With Bond Yields - Excel |
|
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!!!
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!!!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
Entering time values in custom format [h]:mm:ss
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
From limited experience I know that excel calculates dates via serial numbers.
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
On my calculator, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get 462,534.50. Which is what I need it to be because I need to calculate the difference of a figure not dividing equally.
On excel, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get the SAME result of 462,534.05.
I have to account for the difference of not dividing equally so there is some format or accounting function that's preset that I don't want but I have no idea how to fix it or format my excel sheet to calculate as my calculator would.
Please can someone help? Thanks!
On excel, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get the SAME result of 462,534.05.
I have to account for the difference of not dividing equally so there is some format or accounting function that's preset that I don't want but I have no idea how to fix it or format my excel sheet to calculate as my calculator would.
Please can someone help? Thanks!
I have imported a DBF file into Excel and have a column of dates that are missing the leading zero on single didgit months. When I try to us the custom format of mm/dd/yyyy it doesn't work (interestingly, after I select that format if I click on an individual cell it changes to the right format).
Does anyone know a better way to do this?
Thanks in advance!
Does anyone know a better way to do this?
Thanks in advance!
Hi All,
I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
Please can someone give me a bit of guidence or link me to a good rescource if missed it in the search.
Thanks in advance.
G
I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
Please can someone give me a bit of guidence or link me to a good rescource if missed it in the search.
Thanks in advance.
G
Since there isn't a ISDATE function in excel.
The following can be used to simulate the same, and avoid having to achieve the same with VBA's IsDate function:
STEPS:
1- Format the column (ex A) as text
2- Formula to check for valid dates:
=ISERROR(DATEVALUE(A1))
The following can be used to simulate the same, and avoid having to achieve the same with VBA's IsDate function:
STEPS:
1- Format the column (ex A) as text
2- Formula to check for valid dates:
=ISERROR(DATEVALUE(A1))
I have a list of 5 buildings each with different unit counts, and an average price sold for each building. How can I get the average price of all units sold?
A B
312 $1000
54 $1010
35 $1835
32 $1028
53 $1937
A B
312 $1000
54 $1010
35 $1835
32 $1028
53 $1937
I have entered a vlookup formula in a cell and it returns the correct value from the other worksheet. However when I copy the formula down it keeps returning the exact same value as the first cell. If I enter a formula in each individual cell it returns the correct value. Its driving me crazy ! I woyld be glad of any advice
Many Thanks
Karamazov
Many Thanks
Karamazov
i have a cell i have to check if it contains six characters. I have a list of data that i need to narrow down to six characters. I have successfully done that, but some of the cell has 5, 6, or 7 characters. The list contains about 600 cells, but i don't have time to format them individually. I want to create a formula that returns true or false if the cell contains 6 characters and false if it is above or below 6.
Can somebody help me, i'm new to excel!
Can somebody help me, i'm new to excel!
I need a formula to automatically calculate if the Job was completed in the
date range that was provided, and to return Early, Late or Ok accordingly.
date range that was provided, and to return Early, Late or Ok accordingly.
How I Can Maintain The Stock Register On Excel Sheet By Date Wise ?
But I do not want to insert any sheet for different dates & also the format will not be copied & paste in the same sheet. Just at the place of Date the date will be changed & the datas will be entered manually for each day.
In future, when I shall put the date, the datas will show that particular date's datas only.
Can anyone help me ?
But I do not want to insert any sheet for different dates & also the format will not be copied & paste in the same sheet. Just at the place of Date the date will be changed & the datas will be entered manually for each day.
In future, when I shall put the date, the datas will show that particular date's datas only.
Can anyone help me ?
I am trying to work out how to calculate the geometric mean of a series of values, some of which are negative, ie. investment returns over a series of years with some negatives.
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
Hi Guys
I have the following in G2:
=VLOOKUP(A2,Sheet1!A:D,4,FALSE)
The formula returns the correct result, which in this case is a number - 2
When I fill down my range, the formula copies correctly, but every result is the same. 2.
However, if I go to the next cell down (G3) and activate it by pressing F2, and then hit Enter, the result changes and is now correct.
Any ideas whats going wrong?
I have the following in G2:
=VLOOKUP(A2,Sheet1!A:D,4,FALSE)
The formula returns the correct result, which in this case is a number - 2
When I fill down my range, the formula copies correctly, but every result is the same. 2.
However, if I go to the next cell down (G3) and activate it by pressing F2, and then hit Enter, the result changes and is now correct.
Any ideas whats going wrong?
Hi
I'm not sure how to do this, but I want the Total (column D) to be the sum of the Unit Price (column C) times the Quantity (column A). Is there a formula you can apply to a column or do I have to do it in individaul cells?
I'm not sure how to do this, but I want the Total (column D) to be the sum of the Unit Price (column C) times the Quantity (column A). Is there a formula you can apply to a column or do I have to do it in individaul cells?
Can anyone help me with creating a macro that will save the worksheet as a pdf in a specific folder with the value of a cell and the date. This is what I have so far.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("H10").Value & Format(Date, "mmdd")
This saves it as the correct cell value + the date, but it saves it to my documents. I need it to save to a shared folder on a network drive.
Any help would be greatly appreciated.
THANKS
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("H10").Value & Format(Date, "mmdd")
This saves it as the correct cell value + the date, but it saves it to my documents. I need it to save to a shared folder on a network drive.
Any help would be greatly appreciated.
THANKS
I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.
I am trying to figure out how to write a formula to figure out production cycle time.
We will be building 8 "widgets" a day.
We will work 10 hours a day.
There is a 20 minute break at 9:20 am.
Production shuts down for lunch 30 minutes for lunch at 12:30.
Production starts at 6:00 am.
Here is what I can do.
Production cycle time = (10*60)-30/8 or 71.25 minutes
If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)
My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.
This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.
=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))
I have included an attachment to help (a picture is worth a thousand words).
Any help will be greatly appreciated.
I have bought a number of books in an attempt to figure this out, and I am still stumped
We will be building 8 "widgets" a day.
We will work 10 hours a day.
There is a 20 minute break at 9:20 am.
Production shuts down for lunch 30 minutes for lunch at 12:30.
Production starts at 6:00 am.
Here is what I can do.
Production cycle time = (10*60)-30/8 or 71.25 minutes
If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)
My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.
This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.
=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))
I have included an attachment to help (a picture is worth a thousand words).
Any help will be greatly appreciated.
I have bought a number of books in an attempt to figure this out, and I am still stumped
Is there an Excel guru that can help with this - its related to "drop down menus"
I have 2 colombs of data.
AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.
At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1
The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.
How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...
ANy suggestions??
I have 2 colombs of data.
AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.
At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1
The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.
How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...
ANy suggestions??
I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).
For instance,
A B
1 $32.17 8/17/08
2 $43.20 9/12/09
If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.
Thanks
For instance,
A B
1 $32.17 8/17/08
2 $43.20 9/12/09
If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.
Thanks