Value Of A Record Based On A Previous Record

I hope you can help me. I posted this in the microsoft sql server newsgroup
a few days ago and got no response so I thought I'd try here. If I can
provide any clarification I'll be glad to do so.

I'm trying to calculate a column based on the value of the previous record.
I'm not very experienced with SQL-Server.

I'm using the following table:
CREATE TABLE tblPayment
(
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[LoanID] [int] NULL ,
[PaymentPeriod] [int] NULL ,
[PaymentRecDate] [datetime] NULL ,
[PaymentAMT] [money] NULL
)

I have a view based on this table. That view has the following calculated
columns

BeginningBalance: For the first record, this is equal to the loan amount
from the loan table. For each additional record this is equal to the ending
balance from the previous payment record.

Interest: BeginningBalance * the monthly interest rate from the loan
table
Principal: PaymentAMT - Interest
EndingBalance: BeginningBalance - Principal

It might seem I could use a subquery to calculate the Beginning Balance as
in:

SELECT LoanID, PaymentPeriod, PaymentAMT,
(SELECT SUM(PaymentAMT) FROM tblPayment AS tbl1
WHERE tbl1.LoanID = tblPayment.LoanID AND tbl1.PaymentPeriod <
tblPayment.PaymentPeriod) AS BeginBalance
FROM tblPayment
WHERE (LoanID = @LoanID)

But this will not work, because the interest is calculated on the previous
month's balance. I need to find a way to loop through the recordset. Is
this possible?

Thank you,

--
Derek Cooper
Database9
www.database9.com