Formula to "Pull In" a value from Preceeding Rows based on Multiple Criteria?

[FIONAMONA]

I am fairly new to advanced formula use in Excel, and I am stumped. I use index match all the time and the usual sum ifs, ifs, and etc...but can't figure this out. My boss is insistent that I keep my columns as they are....here is my issue. I have 5 columns in which I am calculating liquidation risks of loans. A borrower may have multiple loans all with different loan numbers, yet when liquidating we take all collateral and come to one risk value on the borrower...see below

I need the preliminary risk/surplus column E to add to column C and Subtract column D if the loans are related. If not related, the tallying stops. So, risk/suplus + collater - loan amount = risk/surplus and repeat...The loans must remain in this order....any ideas of a formula to put in column E would make u my hero.
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]loan #[/TD]
[TD]collateral $[/TD]
[TD]loan amt[/TD]
[TD]risk or surplus[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]1234[/TD]
[TD]50,000[/TD]
[TD]25,000[/TD]
[TD]25,000[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]4567[/TD]
[TD]100,000[/TD]
[TD]50,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]7890[/TD]
[TD]6000[/TD]
[TD]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]5678[/TD]
[TD]0.00[/TD]
[TD]25,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sam[/TD]
[TD]6709[/TD]
[TD]50,000[/TD]
[TD]25,000[/TD]
[TD]25,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]