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