| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse PRICE function
Wondering if there is a way to calculate coupon rate of a bond based on YTM,
maturity, settlement date, and bond's price essentially I am trying to back solve for the coupon rate based on all inputs of the PRICE formula (i.e. all are known except RATE in the PRICE formula) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reverse PRICE function
Use Tools|Goal Seek
For details on HOW TO visit http://www.homeandlearn.co.uk/ME/mes8p4.html "ingmar" wrote: Wondering if there is a way to calculate coupon rate of a bond based on YTM, maturity, settlement date, and bond's price essentially I am trying to back solve for the coupon rate based on all inputs of the PRICE formula (i.e. all are known except RATE in the PRICE formula) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reverse PRICE function
OK - but is there a way to use a function to avoid the goal seek?
"Sheeloo" wrote: Use Tools|Goal Seek For details on HOW TO visit http://www.homeandlearn.co.uk/ME/mes8p4.html "ingmar" wrote: Wondering if there is a way to calculate coupon rate of a bond based on YTM, maturity, settlement date, and bond's price essentially I am trying to back solve for the coupon rate based on all inputs of the PRICE formula (i.e. all are known except RATE in the PRICE formula) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reverse PRICE function
Post your PRICE formula and we will help with the algebra to invert it.
-- Gary''s Student - gsnu200810 "ingmar" wrote: OK - but is there a way to use a function to avoid the goal seek? "Sheeloo" wrote: Use Tools|Goal Seek For details on HOW TO visit http://www.homeandlearn.co.uk/ME/mes8p4.html "ingmar" wrote: Wondering if there is a way to calculate coupon rate of a bond based on YTM, maturity, settlement date, and bond's price essentially I am trying to back solve for the coupon rate based on all inputs of the PRICE formula (i.e. all are known except RATE in the PRICE formula) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reverse PRICE function
=PRICE(Settlement Date,Maturity Date,Coupon,YTM,100,4,4)
This is standard excel price function I know price and all inputs BUT coupon need to find a way to calculate the coupon based on all other inputs assume there must be a way to do it - excel does not seem to have a function for that "Gary''s Student" wrote: Post your PRICE formula and we will help with the algebra to invert it. -- Gary''s Student - gsnu200810 "ingmar" wrote: OK - but is there a way to use a function to avoid the goal seek? "Sheeloo" wrote: Use Tools|Goal Seek For details on HOW TO visit http://www.homeandlearn.co.uk/ME/mes8p4.html "ingmar" wrote: Wondering if there is a way to calculate coupon rate of a bond based on YTM, maturity, settlement date, and bond's price essentially I am trying to back solve for the coupon rate based on all inputs of the PRICE formula (i.e. all are known except RATE in the PRICE formula) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reverse PRICE function
When you have the FORMULA and its result but don't know one of the variables
then GOAL SEEK will provide you the answer... If you know the calculation behind PRICE formula of Excel (how you will do it on paper) then we can give you the formula to way to calculate Coupon given the result. "ingmar" wrote: =PRICE(Settlement Date,Maturity Date,Coupon,YTM,100,4,4) This is standard excel price function I know price and all inputs BUT coupon need to find a way to calculate the coupon based on all other inputs assume there must be a way to do it - excel does not seem to have a function for that "Gary''s Student" wrote: Post your PRICE formula and we will help with the algebra to invert it. -- Gary''s Student - gsnu200810 "ingmar" wrote: OK - but is there a way to use a function to avoid the goal seek? "Sheeloo" wrote: Use Tools|Goal Seek For details on HOW TO visit http://www.homeandlearn.co.uk/ME/mes8p4.html "ingmar" wrote: Wondering if there is a way to calculate coupon rate of a bond based on YTM, maturity, settlement date, and bond's price essentially I am trying to back solve for the coupon rate based on all inputs of the PRICE formula (i.e. all are known except RATE in the PRICE formula) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reverse PRICE function
With SIGNIFICANT HELP from Gary's Student, here is your formula. Format the
cell for percentage. This will give you the interest rate if you (not yield), if you have all the other pertinent data. A1=Settlement Date A2=Maturity Date A4=Percent Yield A5=Redemption Value A6=Frequency A7=Basis Your formula for is as follows: =(A$8-PRICE(A$1,A$2,0%,A$4,A$5,A$6,A$7))/(PRICE(A$1,A$2,2%,A$4,A$5,A$6,A$7)-PRICE(A$1,A$2,1%,A$4,A$5,A$6,A$7))/100 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "ingmar" wrote: Wondering if there is a way to calculate coupon rate of a bond based on YTM, maturity, settlement date, and bond's price essentially I am trying to back solve for the coupon rate based on all inputs of the PRICE formula (i.e. all are known except RATE in the PRICE formula) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| NAME function working in reverse?? | Excel Discussion (Misc queries) | |||
| Price Function | Excel Worksheet Functions | |||
| Reverse NETWORKDAYS function? | Excel Worksheet Functions | |||
| Price Function Error? | Excel Worksheet Functions | |||
| Reverse MATCH Function | Excel Discussion (Misc queries) | |||
Hybrid Mode
