|
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) |
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) |
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) |
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) |
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) |
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) |
Reverse PRICE function
The formula for PRICE is actually in the Excel Help. However, I'd like to see
you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
John,
Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
awwww, c'mon!
That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Actually John, the summation is not a killer. The price formula only has
three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Thanks Gary...
I also thought so but was afraid to commit before spending some time on it. "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Where's the formula?
-- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Hi Folks,
Although I don't have time now to look at the formula, if it has integration, Excel can do that in the spreadsheet. You can also do differentiatin. (sic) -- Thanks, Shane Devenshire "John C" wrote: Where's the formula? -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Huh, still no formula? I guess the summation is a killer. I can come up with
the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
I HATE to admit it, but you are right and I was wrong.
The summation IS a pain in the a** I will look to VBA to see if something manageable can be developed -- Gary''s Student - gsnu200811 "John C" wrote: Huh, still no formula? I guess the summation is a killer. I can come up with the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Don't sweat it. I'm wrong all the time, and freely admit to being wrong. None
of us are perfect. It would be interesting to see the excel formula for it, but I'm not demanding anything :) I don't understand the OP's aversion to goal seek, though. That would be a better question to have answered. -- ** John C ** "Gary''s Student" wrote: I HATE to admit it, but you are right and I was wrong. The summation IS a pain in the a** I will look to VBA to see if something manageable can be developed -- Gary''s Student - gsnu200811 "John C" wrote: Huh, still no formula? I guess the summation is a killer. I can come up with the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
I figured out how to invert the PRICE function, numerically, not with algebra.
-- Gary''s Student - gsnu200811 "John C" wrote: Don't sweat it. I'm wrong all the time, and freely admit to being wrong. None of us are perfect. It would be interesting to see the excel formula for it, but I'm not demanding anything :) I don't understand the OP's aversion to goal seek, though. That would be a better question to have answered. -- ** John C ** "Gary''s Student" wrote: I HATE to admit it, but you are right and I was wrong. The summation IS a pain in the a** I will look to VBA to see if something manageable can be developed -- Gary''s Student - gsnu200811 "John C" wrote: Huh, still no formula? I guess the summation is a killer. I can come up with the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
I didn't get a chance to look at it this weekend, and probably not gonna get
a chance to look at it for a bit, it'd still be interesting to see the "solve for rate" equivalent of the price formula :) in excel of course :) -- ** John C ** "Gary''s Student" wrote: I figured out how to invert the PRICE function, numerically, not with algebra. -- Gary''s Student - gsnu200811 "John C" wrote: Don't sweat it. I'm wrong all the time, and freely admit to being wrong. None of us are perfect. It would be interesting to see the excel formula for it, but I'm not demanding anything :) I don't understand the OP's aversion to goal seek, though. That would be a better question to have answered. -- ** John C ** "Gary''s Student" wrote: I HATE to admit it, but you are right and I was wrong. The summation IS a pain in the a** I will look to VBA to see if something manageable can be developed -- Gary''s Student - gsnu200811 "John C" wrote: Huh, still no formula? I guess the summation is a killer. I can come up with the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Heck. I tried solving it, but I came up very wrong, perhaps you can share a
little info? I am probably missing something stupid In the price formula, I have the following Redemption=100 YLD=6.5% Frequency=2 DSC=90 (calculated using COUPDAYSNC) E=180 (calculated using COUPDAYS) Rate=5.75% N=20 (calculated = COUPNUM) k=1 to N (I am sure this is my error, but I cannot figure it out) A=90 (calculated using COUPDAYBS) -- ** John C ** "Gary''s Student" wrote: I figured out how to invert the PRICE function, numerically, not with algebra. -- Gary''s Student - gsnu200811 "John C" wrote: Don't sweat it. I'm wrong all the time, and freely admit to being wrong. None of us are perfect. It would be interesting to see the excel formula for it, but I'm not demanding anything :) I don't understand the OP's aversion to goal seek, though. That would be a better question to have answered. -- ** John C ** "Gary''s Student" wrote: I HATE to admit it, but you are right and I was wrong. The summation IS a pain in the a** I will look to VBA to see if something manageable can be developed -- Gary''s Student - gsnu200811 "John C" wrote: Huh, still no formula? I guess the summation is a killer. I can come up with the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Hi John:
Here is my progress to date. As you pointed out, the equation for PRICE is hideous. I wanted to see how Price varied if I just changed the Rate. I followed the instructions in Help and in A1 thru A7: 15-Feb-08 15-Nov-17 5.75% 6.50% $100 2 0 and then in A8: =PRICE(A$1,A$2,A$3,A$4,A$5,A$6,A$7) which displays 94.63436162 just as it should. Next I built a table of RATE v.s. PRICE In B1 and C1: =(ROW()-1)/100 and =PRICE(A$1,A$2,B1,A$4,A$5,A$6,A$7) I then copied these down: 0.00% 53.59741246 1.00% 60.73427318 2.00% 67.87113391 3.00% 75.00799463 4.00% 82.14485535 5.00% 89.28171608 6.00% 96.4185768 7.00% 103.5554375 8.00% 110.6922983 9.00% 117.829159 10.00% 124.9660197 11.00% 132.1028804 12.00% 139.2397411 13.00% 146.3766019 14.00% 153.5134626 15.00% 160.6503233 16.00% 167.787184 17.00% 174.9240448 18.00% 182.0609055 19.00% 189.1977662 20.00% 196.3346269 21.00% 203.4714877 22.00% 210.6083484 23.00% 217.7452091 24.00% 224.8820698 25.00% 232.0189306 26.00% 239.1557913 27.00% 246.292652 28.00% 253.4295127 29.00% 260.5663735 30.00% 267.7032342 31.00% 274.8400949 32.00% 281.9769556 33.00% 289.1138164 34.00% 296.2506771 35.00% 303.3875378 36.00% 310.5243985 37.00% 317.6612593 38.00% 324.79812 39.00% 331.9349807 40.00% 339.0718414 41.00% 346.2087022 42.00% 353.3455629 43.00% 360.4824236 44.00% 367.6192843 45.00% 374.756145 46.00% 381.8930058 47.00% 389.0298665 48.00% 396.1667272 49.00% 403.3035879 50.00% 410.4404487 51.00% 417.5773094 52.00% 424.7141701 53.00% 431.8510308 54.00% 438.9878916 55.00% 446.1247523 56.00% 453.261613 57.00% 460.3984737 58.00% 467.5353345 59.00% 474.6721952 60.00% 481.8090559 61.00% 488.9459166 62.00% 496.0827774 63.00% 503.2196381 64.00% 510.3564988 65.00% 517.4933595 66.00% 524.6302203 67.00% 531.767081 68.00% 538.9039417 69.00% 546.0408024 70.00% 553.1776632 71.00% 560.3145239 72.00% 567.4513846 73.00% 574.5882453 74.00% 581.7251061 75.00% 588.8619668 76.00% 595.9988275 77.00% 603.1356882 78.00% 610.2725489 79.00% 617.4094097 80.00% 624.5462704 81.00% 631.6831311 82.00% 638.8199918 83.00% 645.9568526 84.00% 653.0937133 85.00% 660.230574 86.00% 667.3674347 87.00% 674.5042955 88.00% 681.6411562 89.00% 688.7780169 90.00% 695.9148776 91.00% 703.0517384 92.00% 710.1885991 93.00% 717.3254598 94.00% 724.4623205 95.00% 731.5991813 96.00% 738.736042 97.00% 745.8729027 98.00% 753.0097634 99.00% 760.1466242 When plotted it is an absolute straight line! The function really has the form: Y = A*X + B or X = (Y - B) / A In F19 & F20: =C1 The B Factor (53.59741246) =(C100-C1)/B100 The A Factor (713.6860724) Finally the way to get the rate that gives you a price of, say, 200: =(200-F19)/F20 which displays 20.51% The key issue is that we really don't have to invert all the algebra. -- Gary''s Student - gsnu200811 "John C" wrote: Heck. I tried solving it, but I came up very wrong, perhaps you can share a little info? I am probably missing something stupid In the price formula, I have the following Redemption=100 YLD=6.5% Frequency=2 DSC=90 (calculated using COUPDAYSNC) E=180 (calculated using COUPDAYS) Rate=5.75% N=20 (calculated = COUPNUM) k=1 to N (I am sure this is my error, but I cannot figure it out) A=90 (calculated using COUPDAYBS) -- ** John C ** "Gary''s Student" wrote: I figured out how to invert the PRICE function, numerically, not with algebra. -- Gary''s Student - gsnu200811 "John C" wrote: Don't sweat it. I'm wrong all the time, and freely admit to being wrong. None of us are perfect. It would be interesting to see the excel formula for it, but I'm not demanding anything :) I don't understand the OP's aversion to goal seek, though. That would be a better question to have answered. -- ** John C ** "Gary''s Student" wrote: I HATE to admit it, but you are right and I was wrong. The summation IS a pain in the a** I will look to VBA to see if something manageable can be developed -- Gary''s Student - gsnu200811 "John C" wrote: Huh, still no formula? I guess the summation is a killer. I can come up with the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
Um, not to be a stickler, but that is coming up with the security's annual
yield, not the security's annual rate. -- ** John C ** "Gary''s Student" wrote: When plotted it is an absolute straight line! The function really has the form: Y = A*X + B or X = (Y - B) / A In F19 & F20: =C1 The B Factor (53.59741246) =(C100-C1)/B100 The A Factor (713.6860724) Finally the way to get the rate that gives you a price of, say, 200: =(200-F19)/F20 which displays 20.51% |
Reverse PRICE function
John C <johnc@stateofdenial wrote...
The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it . . . It's easy. It's essentially Price = x + Rate * y - Rate * z so Rate = (Price - x) / (y - z) where Price is a given, x = redemption / (1 + yld / frequency) ^ (N - 1 + DSC / E) y = Sum[k=1..N, 100 / frequency / (1 + yld / frequency) ^ (k - 1 + DSC / E)] = 100 / frequency / (1 + yld / frequency) ^ (DSC / E - 1) * Sum[1..N, (1 + yld / frequency) ^ -k] z = 100 * A / frequency / E x and z are straightforward. The latter Sum[] for y can be derived simply as PV(yld / frequency, N, -1). |
Reverse PRICE function
I see no excel portion of it. I was referring to the algebra in excel format,
but I don't see that. -- ** John C ** "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it . . . It's easy. It's essentially Price = x + Rate * y - Rate * z so Rate = (Price - x) / (y - z) where Price is a given, x = redemption / (1 + yld / frequency) ^ (N - 1 + DSC / E) y = Sum[k=1..N, 100 / frequency / (1 + yld / frequency) ^ (k - 1 + DSC / E)] = 100 / frequency / (1 + yld / frequency) ^ (DSC / E - 1) * Sum[1..N, (1 + yld / frequency) ^ -k] z = 100 * A / frequency / E x and z are straightforward. The latter Sum[] for y can be derived simply as PV(yld / frequency, N, -1). |
Reverse PRICE function
Hey, got it figured out, though, I think it fails if the rate is above 99% or
below 1% (untested, and don't care to test, if the OP is still around, he can take it for what it's worth). In one more column, type the following in a row 2 cell: =IF(AND(A$8=C2,A$8<C3),(A$8-C1)/(C2-C1)/100+B1,"") copy down as needed. This will find which RATE the result is in between, and then calculate the percent in between (i.e.: between 5 & 6 % doesn't tell me it is actually 5.75%). -- ** John C ** "Gary''s Student" wrote: Hi John: Here is my progress to date. As you pointed out, the equation for PRICE is hideous. I wanted to see how Price varied if I just changed the Rate. I followed the instructions in Help and in A1 thru A7: 15-Feb-08 15-Nov-17 5.75% 6.50% $100 2 0 and then in A8: =PRICE(A$1,A$2,A$3,A$4,A$5,A$6,A$7) which displays 94.63436162 just as it should. Next I built a table of RATE v.s. PRICE In B1 and C1: =(ROW()-1)/100 and =PRICE(A$1,A$2,B1,A$4,A$5,A$6,A$7) I then copied these down: 0.00% 53.59741246 1.00% 60.73427318 2.00% 67.87113391 3.00% 75.00799463 4.00% 82.14485535 5.00% 89.28171608 6.00% 96.4185768 7.00% 103.5554375 8.00% 110.6922983 9.00% 117.829159 10.00% 124.9660197 11.00% 132.1028804 12.00% 139.2397411 13.00% 146.3766019 14.00% 153.5134626 15.00% 160.6503233 16.00% 167.787184 17.00% 174.9240448 18.00% 182.0609055 19.00% 189.1977662 20.00% 196.3346269 21.00% 203.4714877 22.00% 210.6083484 23.00% 217.7452091 24.00% 224.8820698 25.00% 232.0189306 26.00% 239.1557913 27.00% 246.292652 28.00% 253.4295127 29.00% 260.5663735 30.00% 267.7032342 31.00% 274.8400949 32.00% 281.9769556 33.00% 289.1138164 34.00% 296.2506771 35.00% 303.3875378 36.00% 310.5243985 37.00% 317.6612593 38.00% 324.79812 39.00% 331.9349807 40.00% 339.0718414 41.00% 346.2087022 42.00% 353.3455629 43.00% 360.4824236 44.00% 367.6192843 45.00% 374.756145 46.00% 381.8930058 47.00% 389.0298665 48.00% 396.1667272 49.00% 403.3035879 50.00% 410.4404487 51.00% 417.5773094 52.00% 424.7141701 53.00% 431.8510308 54.00% 438.9878916 55.00% 446.1247523 56.00% 453.261613 57.00% 460.3984737 58.00% 467.5353345 59.00% 474.6721952 60.00% 481.8090559 61.00% 488.9459166 62.00% 496.0827774 63.00% 503.2196381 64.00% 510.3564988 65.00% 517.4933595 66.00% 524.6302203 67.00% 531.767081 68.00% 538.9039417 69.00% 546.0408024 70.00% 553.1776632 71.00% 560.3145239 72.00% 567.4513846 73.00% 574.5882453 74.00% 581.7251061 75.00% 588.8619668 76.00% 595.9988275 77.00% 603.1356882 78.00% 610.2725489 79.00% 617.4094097 80.00% 624.5462704 81.00% 631.6831311 82.00% 638.8199918 83.00% 645.9568526 84.00% 653.0937133 85.00% 660.230574 86.00% 667.3674347 87.00% 674.5042955 88.00% 681.6411562 89.00% 688.7780169 90.00% 695.9148776 91.00% 703.0517384 92.00% 710.1885991 93.00% 717.3254598 94.00% 724.4623205 95.00% 731.5991813 96.00% 738.736042 97.00% 745.8729027 98.00% 753.0097634 99.00% 760.1466242 When plotted it is an absolute straight line! The function really has the form: Y = A*X + B or X = (Y - B) / A In F19 & F20: =C1 The B Factor (53.59741246) =(C100-C1)/B100 The A Factor (713.6860724) Finally the way to get the rate that gives you a price of, say, 200: =(200-F19)/F20 which displays 20.51% The key issue is that we really don't have to invert all the algebra. -- Gary''s Student - gsnu200811 "John C" wrote: Heck. I tried solving it, but I came up very wrong, perhaps you can share a little info? I am probably missing something stupid In the price formula, I have the following Redemption=100 YLD=6.5% Frequency=2 DSC=90 (calculated using COUPDAYSNC) E=180 (calculated using COUPDAYS) Rate=5.75% N=20 (calculated = COUPNUM) k=1 to N (I am sure this is my error, but I cannot figure it out) A=90 (calculated using COUPDAYBS) -- ** John C ** "Gary''s Student" wrote: I figured out how to invert the PRICE function, numerically, not with algebra. -- Gary''s Student - gsnu200811 "John C" wrote: Don't sweat it. I'm wrong all the time, and freely admit to being wrong. None of us are perfect. It would be interesting to see the excel formula for it, but I'm not demanding anything :) I don't understand the OP's aversion to goal seek, though. That would be a better question to have answered. -- ** John C ** "Gary''s Student" wrote: I HATE to admit it, but you are right and I was wrong. The summation IS a pain in the a** I will look to VBA to see if something manageable can be developed -- Gary''s Student - gsnu200811 "John C" wrote: Huh, still no formula? I guess the summation is a killer. I can come up with the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
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) |
Reverse PRICE function
Even better, here is a single formula. Many many thanks to you :)
=(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 ** "Gary''s Student" wrote: Hi John: Here is my progress to date. As you pointed out, the equation for PRICE is hideous. I wanted to see how Price varied if I just changed the Rate. I followed the instructions in Help and in A1 thru A7: 15-Feb-08 15-Nov-17 5.75% 6.50% $100 2 0 and then in A8: =PRICE(A$1,A$2,A$3,A$4,A$5,A$6,A$7) which displays 94.63436162 just as it should. Next I built a table of RATE v.s. PRICE In B1 and C1: =(ROW()-1)/100 and =PRICE(A$1,A$2,B1,A$4,A$5,A$6,A$7) I then copied these down: 0.00% 53.59741246 1.00% 60.73427318 2.00% 67.87113391 3.00% 75.00799463 4.00% 82.14485535 5.00% 89.28171608 6.00% 96.4185768 7.00% 103.5554375 8.00% 110.6922983 9.00% 117.829159 10.00% 124.9660197 11.00% 132.1028804 12.00% 139.2397411 13.00% 146.3766019 14.00% 153.5134626 15.00% 160.6503233 16.00% 167.787184 17.00% 174.9240448 18.00% 182.0609055 19.00% 189.1977662 20.00% 196.3346269 21.00% 203.4714877 22.00% 210.6083484 23.00% 217.7452091 24.00% 224.8820698 25.00% 232.0189306 26.00% 239.1557913 27.00% 246.292652 28.00% 253.4295127 29.00% 260.5663735 30.00% 267.7032342 31.00% 274.8400949 32.00% 281.9769556 33.00% 289.1138164 34.00% 296.2506771 35.00% 303.3875378 36.00% 310.5243985 37.00% 317.6612593 38.00% 324.79812 39.00% 331.9349807 40.00% 339.0718414 41.00% 346.2087022 42.00% 353.3455629 43.00% 360.4824236 44.00% 367.6192843 45.00% 374.756145 46.00% 381.8930058 47.00% 389.0298665 48.00% 396.1667272 49.00% 403.3035879 50.00% 410.4404487 51.00% 417.5773094 52.00% 424.7141701 53.00% 431.8510308 54.00% 438.9878916 55.00% 446.1247523 56.00% 453.261613 57.00% 460.3984737 58.00% 467.5353345 59.00% 474.6721952 60.00% 481.8090559 61.00% 488.9459166 62.00% 496.0827774 63.00% 503.2196381 64.00% 510.3564988 65.00% 517.4933595 66.00% 524.6302203 67.00% 531.767081 68.00% 538.9039417 69.00% 546.0408024 70.00% 553.1776632 71.00% 560.3145239 72.00% 567.4513846 73.00% 574.5882453 74.00% 581.7251061 75.00% 588.8619668 76.00% 595.9988275 77.00% 603.1356882 78.00% 610.2725489 79.00% 617.4094097 80.00% 624.5462704 81.00% 631.6831311 82.00% 638.8199918 83.00% 645.9568526 84.00% 653.0937133 85.00% 660.230574 86.00% 667.3674347 87.00% 674.5042955 88.00% 681.6411562 89.00% 688.7780169 90.00% 695.9148776 91.00% 703.0517384 92.00% 710.1885991 93.00% 717.3254598 94.00% 724.4623205 95.00% 731.5991813 96.00% 738.736042 97.00% 745.8729027 98.00% 753.0097634 99.00% 760.1466242 When plotted it is an absolute straight line! The function really has the form: Y = A*X + B or X = (Y - B) / A In F19 & F20: =C1 The B Factor (53.59741246) =(C100-C1)/B100 The A Factor (713.6860724) Finally the way to get the rate that gives you a price of, say, 200: =(200-F19)/F20 which displays 20.51% The key issue is that we really don't have to invert all the algebra. -- Gary''s Student - gsnu200811 "John C" wrote: Heck. I tried solving it, but I came up very wrong, perhaps you can share a little info? I am probably missing something stupid In the price formula, I have the following Redemption=100 YLD=6.5% Frequency=2 DSC=90 (calculated using COUPDAYSNC) E=180 (calculated using COUPDAYS) Rate=5.75% N=20 (calculated = COUPNUM) k=1 to N (I am sure this is my error, but I cannot figure it out) A=90 (calculated using COUPDAYBS) -- ** John C ** "Gary''s Student" wrote: I figured out how to invert the PRICE function, numerically, not with algebra. -- Gary''s Student - gsnu200811 "John C" wrote: Don't sweat it. I'm wrong all the time, and freely admit to being wrong. None of us are perfect. It would be interesting to see the excel formula for it, but I'm not demanding anything :) I don't understand the OP's aversion to goal seek, though. That would be a better question to have answered. -- ** John C ** "Gary''s Student" wrote: I HATE to admit it, but you are right and I was wrong. The summation IS a pain in the a** I will look to VBA to see if something manageable can be developed -- Gary''s Student - gsnu200811 "John C" wrote: Huh, still no formula? I guess the summation is a killer. I can come up with the formula via algebra, that's not the issue. It's putting that formula into excel, which was the OPs request, that is, to quote Will Farrell, "Mind Bottling". -- ** John C ** "Gary''s Student" wrote: Actually John, the summation is not a killer. The price formula only has three terms on the right hand side: 1. the redemption term 2. the summation term 3. the A/E term the redemption term has no rate in it so it can be subtracted over to the left side. rate can then be factored out of the summation and A/E terms and the factored terms can be divided over to the left side, leaving only rate on the right -- Gary''s Student - gsnu200810 "John C" wrote: awwww, c'mon! That summation is a killer. -- ** John C ** "Sheeloo" wrote: John, Looked at the formula and would like to withdraw my offer of solving it for RATE... :-) Seriously, I will see whether this can be done, subject to availability of time... Regards, "John C" wrote: The formula for PRICE is actually in the Excel Help. However, I'd like to see you do the reverse algebra on it (not being sarcastic, I looked at it, and just too busy to wrap my mind around it, would love to see the solve for rate version). -- ** John C ** "Sheeloo" wrote: 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) |
Reverse PRICE function
John C <johnc@stateofdenial wrote...
I see no excel portion of it. I was referring to the algebra in excel format, but I don't see that. Need spoon feeding? OK. "Harlan Grove" wrote: .... Price = x + Rate * y - Rate * z so Rate = (Price - x) / (y - z) .... For my own convenience, I'll assume there are the following named cells with the following initial trial values. settlement____03/12/2008 maturity______11/15/2019 rate__________3.75% yield_________4.50% redemption____100 I'll also note that the OP mentioned frequency of 4 and basis 4 (European 30/360), so I'll hardcode around those. I'll also assume there are the following names defined as formulas. N =4*DATEDIF(settlement,maturity,"Y") +ROUNDUP(DATEDIF(settlement,maturity,"YM")/3,0) A =90-DAYS360(settlement,DATE(YEAR(settlement), MONTH(settlement)+MOD(DATEDIF(settlement,maturity, "YM"),3) +(DAY(settlement)DAY(maturity)),DAY(maturity)),0) Note that E = 90 for basis 4 and DSC = E - A, so DSC is redundant. Then x = redemption / (1 + yield / 4) ^ (N - A / 90) y = 100 / frequency * (1 + yield / 4) ^ (A / 90) * PV(yield / 4, N, -1) z = 100 / frequency * A / 90 Put it all together, and given Price, Rate is given by the formula =(Price-redemption/(1+yield/4)^(N-A/90))*0.04 /((1+yield/4)^(A/90)*PV(yield/4,N,-1)-A/90) The formula =PRICE(settlement,maturity,rate,yield,redemption,4 ,4) given the initial trial values above returns 93.21674378. Naming the cell containing this formula Price, the formula =(Price-redemption/(1+yield/4)^(N-A/90))*0.04 /((1+yield/4)^(A/90)*PV(yield/4,N,-1)-A/90) returns 0.0375, or 3.75%, the annual coupon rate. Replacing the defined names N and A with the expressions used to define them in the formula immeidately above I leave as an exercise for you if you're up to it. HAND |
Reverse PRICE function
Good for the example, but you start falling off if you have other data. And
this from the person who claims to want to account for all scenarios. What happens if I change my frequency? Your formula goes to heck. Yes, you said you hardcoded it, but why would you hardcode it? Why wouldn't you give it the flexibility? -- ** John C ** "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... I see no excel portion of it. I was referring to the algebra in excel format, but I don't see that. Need spoon feeding? OK. "Harlan Grove" wrote: .... Price = x + Rate * y - Rate * z so Rate = (Price - x) / (y - z) .... For my own convenience, I'll assume there are the following named cells with the following initial trial values. settlement____03/12/2008 maturity______11/15/2019 rate__________3.75% yield_________4.50% redemption____100 I'll also note that the OP mentioned frequency of 4 and basis 4 (European 30/360), so I'll hardcode around those. I'll also assume there are the following names defined as formulas. N =4*DATEDIF(settlement,maturity,"Y") +ROUNDUP(DATEDIF(settlement,maturity,"YM")/3,0) A =90-DAYS360(settlement,DATE(YEAR(settlement), MONTH(settlement)+MOD(DATEDIF(settlement,maturity, "YM"),3) +(DAY(settlement)DAY(maturity)),DAY(maturity)),0) Note that E = 90 for basis 4 and DSC = E - A, so DSC is redundant. Then x = redemption / (1 + yield / 4) ^ (N - A / 90) y = 100 / frequency * (1 + yield / 4) ^ (A / 90) * PV(yield / 4, N, -1) z = 100 / frequency * A / 90 Put it all together, and given Price, Rate is given by the formula =(Price-redemption/(1+yield/4)^(N-A/90))*0.04 /((1+yield/4)^(A/90)*PV(yield/4,N,-1)-A/90) The formula =PRICE(settlement,maturity,rate,yield,redemption,4 ,4) given the initial trial values above returns 93.21674378. Naming the cell containing this formula Price, the formula =(Price-redemption/(1+yield/4)^(N-A/90))*0.04 /((1+yield/4)^(A/90)*PV(yield/4,N,-1)-A/90) returns 0.0375, or 3.75%, the annual coupon rate. Replacing the defined names N and A with the expressions used to define them in the formula immeidately above I leave as an exercise for you if you're up to it. HAND |
Reverse PRICE function
Hi. If interested, here is the Price equation, as listed in Excel help,
reversed to give Rate.... Sub Demo() '// Variables Dim dsc, e, n, a Dim Price Dim dteSett Dim dteMat Dim Yld Dim Redem Dim f 'frequence Dim Basis Dim k 'a constant Dim Num Dim Den '// Fill in given values from example Price = 94.6343616213221 dteSett = DateSerial(2008, 2, 15) dteMat = DateSerial(2017, 11, 15) Yld = 0.065 Redem = 100 f = 2 Basis = 0 '// Calculate other variables. dsc = WorksheetFunction.CoupDaysNc(dteSett, dteMat, f, Basis) e = WorksheetFunction.CoupDays(dteSett, dteMat, f, Basis) n = WorksheetFunction.CoupNum(dteSett, dteMat, f, Basis) a = WorksheetFunction.CoupDayBs(dteSett, dteMat, f, Basis) '// Equation..broken up a little... k = ((f + Yld) / f) Num = (e * Yld * ((-Redem) * (f + Yld) + f * Price * k ^ (dsc / e + n))) Den = (100 * ((-a) * Yld * k ^ (dsc / e + n) + e * (f + Yld) * (-1 + k ^ n))) Debug.Print "Rate: "; Num / Den End Sub Returns: Rate: 0.0575 HTH :) Dana DeLouis Gary''s Student wrote: Hi John: Here is my progress to date. As you pointed out, the equation for PRICE is hideous. I wanted to see how Price varied if I just changed the Rate. I followed the instructions in Help and in A1 thru A7: 15-Feb-08 15-Nov-17 5.75% 6.50% $100 2 0 and then in A8: =PRICE(A$1,A$2,A$3,A$4,A$5,A$6,A$7) which displays 94.63436162 just as it should. Next I built a table of RATE v.s. PRICE In B1 and C1: =(ROW()-1)/100 and =PRICE(A$1,A$2,B1,A$4,A$5,A$6,A$7) I then copied these down: 0.00% 53.59741246 1.00% 60.73427318 2.00% 67.87113391 3.00% 75.00799463 4.00% 82.14485535 5.00% 89.28171608 6.00% 96.4185768 7.00% 103.5554375 8.00% 110.6922983 9.00% 117.829159 10.00% 124.9660197 11.00% 132.1028804 12.00% 139.2397411 13.00% 146.3766019 14.00% 153.5134626 15.00% 160.6503233 16.00% 167.787184 17.00% 174.9240448 18.00% 182.0609055 19.00% 189.1977662 20.00% 196.3346269 21.00% 203.4714877 22.00% 210.6083484 23.00% 217.7452091 24.00% 224.8820698 25.00% 232.0189306 26.00% 239.1557913 27.00% 246.292652 28.00% 253.4295127 29.00% 260.5663735 30.00% 267.7032342 31.00% 274.8400949 32.00% 281.9769556 33.00% 289.1138164 34.00% 296.2506771 35.00% 303.3875378 36.00% 310.5243985 37.00% 317.6612593 38.00% 324.79812 39.00% 331.9349807 40.00% 339.0718414 41.00% 346.2087022 42.00% 353.3455629 43.00% 360.4824236 44.00% 367.6192843 45.00% 374.756145 46.00% 381.8930058 47.00% 389.0298665 48.00% 396.1667272 49.00% 403.3035879 50.00% 410.4404487 51.00% 417.5773094 52.00% 424.7141701 53.00% 431.8510308 54.00% 438.9878916 55.00% 446.1247523 56.00% 453.261613 57.00% 460.3984737 58.00% 467.5353345 59.00% 474.6721952 60.00% 481.8090559 61.00% 488.9459166 62.00% 496.0827774 63.00% 503.2196381 64.00% 510.3564988 65.00% 517.4933595 66.00% 524.6302203 67.00% 531.767081 68.00% 538.9039417 69.00% 546.0408024 70.00% 553.1776632 71.00% 560.3145239 72.00% 567.4513846 73.00% 574.5882453 74.00% 581.7251061 75.00% 588.8619668 76.00% 595.9988275 77.00% 603.1356882 78.00% 610.2725489 79.00% 617.4094097 80.00% 624.5462704 81.00% 631.6831311 82.00% 638.8199918 83.00% 645.9568526 84.00% 653.0937133 85.00% 660.230574 86.00% 667.3674347 87.00% 674.5042955 88.00% 681.6411562 89.00% 688.7780169 90.00% 695.9148776 91.00% 703.0517384 92.00% 710.1885991 93.00% 717.3254598 94.00% 724.4623205 95.00% 731.5991813 96.00% 738.736042 97.00% 745.8729027 98.00% 753.0097634 99.00% 760.1466242 When plotted it is an absolute straight line! The function really has the form: Y = A*X + B or X = (Y - B) / A In F19 & F20: =C1 The B Factor (53.59741246) =(C100-C1)/B100 The A Factor (713.6860724) Finally the way to get the rate that gives you a price of, say, 200: =(200-F19)/F20 which displays 20.51% The key issue is that we really don't have to invert all the algebra. |
Reverse PRICE function
If I'm not mistaken, here it is as a function...
Function Price_Rate(Price, dteSett, dteMat, Yld, Redem, F, Basis) '// Variables Dim dsc, e, n, a Dim k 'a constant Dim Num Dim Den '// Calculate other variables. With WorksheetFunction dsc = .CoupDaysNc(dteSett, dteMat, F, Basis) e = .CoupDays(dteSett, dteMat, F, Basis) n = .CoupNum(dteSett, dteMat, F, Basis) a = .CoupDayBs(dteSett, dteMat, F, Basis) End With k = ((F + Yld) / F) Num = e * Yld * (F * k ^ (dsc / e + n) * Price - Redem * (F + Yld)) Den = 100 * (e * (k ^ n - 1) * (F + Yld) - a * k ^ (dsc / e + n) * Yld) Price_Rate = Num / Den End Function Sub TestIt() Dim Price, dteSett, dteMat, Yld, Redem, F, Basis Price = 94.6343616213221 dteSett = DateSerial(2008, 2, 15) dteMat = DateSerial(2017, 11, 15) Yld = 0.065 Redem = 100 F = 2 Basis = 0 Debug.Print Price_Rate(Price, dteSett, dteMat, Yld, Redem, F, Basis) End Sub Returns: 0.0575 <snip |
Reverse PRICE function
John C <johnc@stateofdenial wrote...
Good for the example, but you start falling off if you have other data. And this from the person who claims to want to account for all scenarios. What happens if I change my frequency? Your formula goes to heck. Yes, you said you hardcoded it, but why would you hardcode it? Why wouldn't you give it the flexibility? Number of coupons and basis aren't 'data', they're parameters. Easy to adapt to different number of uniformly spaced coupons. Add a defined name freq, then the defined names and formula become N =freq*DATEDIF(settlement,maturity,"Y") +ROUNDUP(DATEDIF(settlement,maturity,"YM")*freq/12,0) A =INT(360/freq)-DAYS360(settlement,DATE(YEAR(settlement), MONTH(settlement)+MOD(DATEDIF(settlement,maturity, "YM"),12/ freq) +(DAY(settlement)DAY(maturity)),DAY(maturity)),0) =(Price-redemption/(1+yield/freq)^(N-A*freq/360))*freq/100 /((1+yield/freq)^(A*freq/360)*PV(yield/freq,N,-1)-A*freq/360) As for different bases, can be done, but makes for much more complexity. |
Reverse PRICE function
Dana DeLouis wrote...
.... Function Price_Rate(Price, dteSett, dteMat, Yld, Redem, F, Basis) .... If you're going to go the VBA route, why not a general udf that could invert any monotonically increasing function? The following is a very simplistic binary search approach. Function invfcnbs( _ f As String, _ y As Double, _ Optional reltol As Double = 0.000001, _ Optional neg As Boolean = False _ ) As Variant '------------------------------- Const MAXITER As Long = 1000 Dim xlo As Double, xhi As Double, xx As Double Dim ylo As Double, yhi As Double, yy As Double Dim n As Long If InStr(1, f, "$$") = 0 Then invfcnbs = CVErr(xlErrNull) Exit Function End If 'step 1 - bracket x value xhi = 16 yhi = Evaluate(Replace(f, "$$", CStr(xhi))) xlo = IIf(neg, -xhi, 1 / xhi) ylo = Evaluate(Replace(f, "$$", CStr(xlo))) For n = 1 To MAXITER If Sgn(yhi - y) * Sgn(y - ylo) = 1 Then Exit For xhi = xhi * 2 yhi = Evaluate(Replace(f, "$$", CStr(xhi))) xlo = IIf(neg, -xhi, 1 / xhi) ylo = Evaluate(Replace(f, "$$", CStr(xlo))) Next n If n MAXITER Then invfcnbs = CVErr(xlErrNum) Exit Function End If 'step 2 - use binary search to find x = inverse_of_f(y) For n = 1 To MAXITER xx = (xhi + xlo) / 2 yy = Evaluate(Replace(f, "$$", CStr(xx))) If Sgn(yy - y) = Sgn(yhi - y) Then xhi = xx yhi = yy Else xlo = xx ylo = yy End If If Abs(yhi / ylo - 1) < reltol Then Exit For Next n invfcnbs = IIf(n <= MAXITER, (xhi + xlo) / 2, CVErr(xlErrNum)) End Function This could be used to find the coupon rate using =invfcnbs("=PRICE(settlement,maturity,$$,yield,red emption,1,4)", PRICE(settlement,maturity,rate,yield,redemption,1, 4)) It could also be used to find the yield using =invfcnbs("=PRICE(settlement,maturity,rate,$$,rede mption,1,4)", PRICE(settlement,maturity,rate,yield,redemption,1, 4)) or the redemption amount using =invfcnbs("=PRICE(settlement,maturity,rate,yield,$ $,1,4)", PRICE(settlement,maturity,rate,yield,redemption,1, 4)) A little algebra is generally preferable, but this udf provides a quick & dirty equivalent to Goal Seek. |
Reverse PRICE function
Yep, fortunately already solved the problem via excel though.
-- ** John C ** "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... Good for the example, but you start falling off if you have other data. And this from the person who claims to want to account for all scenarios. What happens if I change my frequency? Your formula goes to heck. Yes, you said you hardcoded it, but why would you hardcode it? Why wouldn't you give it the flexibility? Number of coupons and basis aren't 'data', they're parameters. Easy to adapt to different number of uniformly spaced coupons. Add a defined name freq, then the defined names and formula become N =freq*DATEDIF(settlement,maturity,"Y") +ROUNDUP(DATEDIF(settlement,maturity,"YM")*freq/12,0) A =INT(360/freq)-DAYS360(settlement,DATE(YEAR(settlement), MONTH(settlement)+MOD(DATEDIF(settlement,maturity, "YM"),12/ freq) +(DAY(settlement)DAY(maturity)),DAY(maturity)),0) =(Price-redemption/(1+yield/freq)^(N-A*freq/360))*freq/100 /((1+yield/freq)^(A*freq/360)*PV(yield/freq,N,-1)-A*freq/360) As for different bases, can be done, but makes for much more complexity. |
| All times are GMT +1. The time now is 01:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com