Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exponent help
Hello, my question deals with solver and exponents in excel. This may be too
technical but I'll give it a try. I'm using solver for calculating zero rate or "spot rate" bond yields. Instead of using periodic interest rates you have to use continuous compounding. Here is the formula with the numbers inputed and I'll use "Y" as the variable to solve for: 4(EXP-(Y*.5)) +4(EXP-(Y*1))+4(EXP-(Y*1.5))+104(EXP-(Y*2.0)) all of this must equal 104.21 when the equation is solved out. My teacher showed us the answer in class but didn't go over the technique cause he assumed we could figure it out. I'm unsure of how to do it with solver. Any help would be greatly appreciated and I'll be glad to answer any questions. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exponent help
The first thing that comes to mind is: recapitulate this equation in Excel
and set Y to some value, say, 1, and then instruct Solver to solve the equation to equal 104.21 by chaning the value in the cell that you set as Y = 1 Dave -- Brevity is the soul of wit. "Josh P." wrote: Hello, my question deals with solver and exponents in excel. This may be too technical but I'll give it a try. I'm using solver for calculating zero rate or "spot rate" bond yields. Instead of using periodic interest rates you have to use continuous compounding. Here is the formula with the numbers inputed and I'll use "Y" as the variable to solve for: 4(EXP-(Y*.5)) +4(EXP-(Y*1))+4(EXP-(Y*1.5))+104(EXP-(Y*2.0)) all of this must equal 104.21 when the equation is solved out. My teacher showed us the answer in class but didn't go over the technique cause he assumed we could figure it out. I'm unsure of how to do it with solver. Any help would be greatly appreciated and I'll be glad to answer any questions. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exponent help
In B1 enter:
=4*(EXP(-(y*0.5))) +4*(EXP(-(y*1)))+4*(EXP(-(y*1.5)))+104*(EXP(-(y*2))) The define y to be cell A1 Then run solver which supplies: 0.0566090568345708 -- Gary's Student gsnu200701 "Josh P." wrote: Hello, my question deals with solver and exponents in excel. This may be too technical but I'll give it a try. I'm using solver for calculating zero rate or "spot rate" bond yields. Instead of using periodic interest rates you have to use continuous compounding. Here is the formula with the numbers inputed and I'll use "Y" as the variable to solve for: 4(EXP-(Y*.5)) +4(EXP-(Y*1))+4(EXP-(Y*1.5))+104(EXP-(Y*2.0)) all of this must equal 104.21 when the equation is solved out. My teacher showed us the answer in class but didn't go over the technique cause he assumed we could figure it out. I'm unsure of how to do it with solver. Any help would be greatly appreciated and I'll be glad to answer any questions. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exponent help
Put the formula =4*(EXP(-A1*0.5))
+4*(EXP(-A1*1))+4*(EXP(-A1*1.5))+104*(EXP(-A1*2)) into cell A2 In Solver ask it to set B1 to 104.21 by changing A1. You could also use Goal Seek. -- David Biddulph "Josh P." <Josh wrote in message ... Hello, my question deals with solver and exponents in excel. This may be too technical but I'll give it a try. I'm using solver for calculating zero rate or "spot rate" bond yields. Instead of using periodic interest rates you have to use continuous compounding. Here is the formula with the numbers inputed and I'll use "Y" as the variable to solve for: 4(EXP-(Y*.5)) +4(EXP-(Y*1))+4(EXP-(Y*1.5))+104(EXP-(Y*2.0)) all of this must equal 104.21 when the equation is solved out. My teacher showed us the answer in class but didn't go over the technique cause he assumed we could figure it out. I'm unsure of how to do it with solver. Any help would be greatly appreciated and I'll be glad to answer any questions. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exponent help
it looks like u have only one variable...
try goal seek... -- ***** birds of the same feather flock together.. "Josh P." wrote: Hello, my question deals with solver and exponents in excel. This may be too technical but I'll give it a try. I'm using solver for calculating zero rate or "spot rate" bond yields. Instead of using periodic interest rates you have to use continuous compounding. Here is the formula with the numbers inputed and I'll use "Y" as the variable to solve for: 4(EXP-(Y*.5)) +4(EXP-(Y*1))+4(EXP-(Y*1.5))+104(EXP-(Y*2.0)) all of this must equal 104.21 when the equation is solved out. My teacher showed us the answer in class but didn't go over the technique cause he assumed we could figure it out. I'm unsure of how to do it with solver. Any help would be greatly appreciated and I'll be glad to answer any questions. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exponent help
Another approach to the problem, which avoids Solver altogether, is to note
that this is a quartic equation in x=exp(-y/2). Quartic equations have known solutions http://en.wikipedia.org/wiki/Quartic_equation Ferrari's method has a solution with real x0 [so that y=-2*ln(x) is also real] by taking +/-[s] to be - and +/-[t] to be + to get x=0.97209229125665744 or y=-2*ln(x)=0.056609058353243358 Jerry "Josh P." wrote: Hello, my question deals with solver and exponents in excel. This may be too technical but I'll give it a try. I'm using solver for calculating zero rate or "spot rate" bond yields. Instead of using periodic interest rates you have to use continuous compounding. Here is the formula with the numbers inputed and I'll use "Y" as the variable to solve for: 4(EXP-(Y*.5)) +4(EXP-(Y*1))+4(EXP-(Y*1.5))+104(EXP-(Y*2.0)) all of this must equal 104.21 when the equation is solved out. My teacher showed us the answer in class but didn't go over the technique cause he assumed we could figure it out. I'm unsure of how to do it with solver. Any help would be greatly appreciated and I'll be glad to answer any questions. Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exponent help
Nice one Jerry. :)
-- HTH :) Dana DeLouis Windows XP & Office 2003 "Jerry W. Lewis" wrote in message ... Another approach to the problem, which avoids Solver altogether, is to note that this is a quartic equation in x=exp(-y/2). Quartic equations have known solutions http://en.wikipedia.org/wiki/Quartic_equation Ferrari's method has a solution with real x0 [so that y=-2*ln(x) is also real] by taking +/-[s] to be - and +/-[t] to be + to get x=0.97209229125665744 or y=-2*ln(x)=0.056609058353243358 Jerry <snip have to use continuous compounding. Here is the formula with the numbers inputed and I'll use "Y" as the variable to solve for: 4(EXP-(Y*.5)) +4(EXP-(Y*1))+4(EXP-(Y*1.5))+104(EXP-(Y*2.0)) all of this must equal 104.21 when the equation is solved out. <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping the exponent at a fix value in Excel | Excel Worksheet Functions | |||
Keeping the exponent at a fix value in Excel | Excel Discussion (Misc queries) | |||
Keeping the exponent at a fix value in Excel | Excel Discussion (Misc queries) | |||
Keeping the exponent at a fix value in Excel | Excel Worksheet Functions | |||
Can I fix the exponent in scientific/engineering format? | Excel Discussion (Misc queries) |