How do I anchor sums from columns?

[larryg003]

Hi Guys,

I have the following three columns and I am trying to anchor sums from the maturity, price and coupon values to calculate zero rates for bonds. It sounds complex, but it really isn't. I just need help on the formulas.
Coupon Maturity Prices Zero Rate
2.5 a1 0.5 b1 101 c1 .99753 (d1)
3.5 a2 1.0 b2 101.01 c2 d2
1.4 a3 1.5 b3 100 c3 d3
3.5 a4 2.0 b4 98 c4 d4
2.3 a5 2.5 b5 98.2 c5 d5
1.9 a6 3.0 b6 97 c6 d6
1.0 a7 3.5 b7 96 c7 d7
2.3 a8 4.0 b8 96.4 c8 d8

Basically, in d2 I want the following pattern:
=(c2-(a2*d1))/(1+a2)

In d3 I want:
=(c3-(a3*d2)-(a3*d1))/(1+a3)

In d4:
=(c4-(a4*d3)-(a4*d2)-(a4*d1))/(1+a4)

in d5
=(c5-(a5*d4)-(a5*d3)-(a5*d2)-(a5*d1))/(1+a5)

Very simple, I just don't know how to get excel to continually anchor the sums w/o having to continually rewrite the formula. I really appreciate all of your help guys!

-Larry G.

[dilipandey]

Hi Larry,

Little confusing.. I have tried transforming your example in the attached spreadsheet but it is not clear to me.. please look and correct. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[MarvinP]

Hi Larry,

I think I've got it with a helper column E. See the attached.