Reverse PRICE function

[ingmar]

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)

 

[Sheeloo]

Use Tools|Goal Seek

For details on HOW TO visit http://www.homeandlearn.co.uk/ME/mes8p4.html

 

[ingmar]

OK - but is there a way to use a function to avoid the goal seek?

 

[Gary''s Student]

Post your PRICE formula and we will help with the algebra to invert it.

 

[ingmar]

=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

 

[Sheeloo]

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.

 

[John C]

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).

 

[Sheeloo]

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]

awwww, c'mon!
That summation is a killer.

 

[Gary''s Student]

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

 

[Sheeloo]

Thanks Gary...

I also thought so but was afraid to commit before spending some time on it.

 

[John C]

Where's the formula?
--
** John C **

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

 

[ShaneDevenshire]

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)

 

[John C]

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".

 

[Gary''s Student]

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

 

[John C]

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.

 

[Gary''s Student]

I figured out how to invert the PRICE function, numerically, not with algebra.

 

[John C]

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]

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)

 

[Gary''s Student]

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.