How to use previous row's Beginning Balance in calculation

[Hayden]

Weekly Payment Period	Opening Balance	Payment (Bi-Weekly)	Principle Paid	Interest Paid	Lease Buyout Price
1	$30,000.00	$215.52	$215.52	$0.00	$29,784.48
2	$29,784.48	$215.52	$32.23	$183.29	$29,752.25
3	$29,752.25	$215.52	$215.52	$0.00	$29,536.73
4	$29,536.73	$215.52	$33.76	$181.76	$29,502.97
5	$29,502.97	$215.52	$215.52	$0.00	$29,287.45
6	$29,287.45	$215.52	$35.29	$180.23	$29,252.16
7	$29,252.16	$215.52	$215.52	$0.00	$29,036.63
8	$29,036.63	$215.52	$36.83	$178.69	$28,999.80

I need to use the $30,000 when calculating intComp in period 2. I'm assuming I must change something in the intComp formula.

For rowNum = 1 To loanLife * 26
If (outRow + rowNum) Mod 2 = 0 Then
    intComp = 0
Else
    intComp = yrBegBal * intRate / 13
End If


    princRepay = biweeklyPmnt - intComp
    yrEndBal = yrBegBal - princRepay
    shtLoanAmort.Cells(outRow + rowNum, 1).Value = rowNum  'Year number
    shtLoanAmort.Cells(outRow + rowNum, 2).Value = yrBegBal

[Paul_Hossler]

Maybe

For rowNum = 1 To loanLife * 26

If (outRow + rowNum) Mod 2 = 0 Then
    intComp = 0

Else
    If rowNum = 1 Then
        intComp = yrBegBal * intRate / 13
    Else
        intComp = shtLoanAmort.Cells(outRow + rowNum - 1, 2).Value * intRate / 13
    End If

End If

    princRepay = biweeklyPmnt - intComp
    yrEndBal = yrBegBal - princRepay
    shtLoanAmort.Cells(outRow + rowNum, 1).Value = rowNum  'Year number
    shtLoanAmort.Cells(outRow + rowNum, 2).Value = yrBegBal
    shtLoanAmort.Cells(outRow + rowNum, 3).Value = biweeklyPmnt
    shtLoanAmort.Cells(outRow + rowNum, 4).Value = princRepay
    shtLoanAmort.Cells(outRow + rowNum, 5).Value = intComp
    shtLoanAmort.Cells(outRow + rowNum, 6).Value = yrEndBal 'Lease buyout price
    yrBegBal = yrEndBal
Next rowNum

As a side note, without an explicit "As Double" things like intRate as Dim-ed as Variant

Dim intRate, initLoanAmnt, loanLife As Double
Dim yrBegBal, yrEndBal
Dim biweeklyPmnt, intComp, princRepay
Dim outRow, rowNum, outSheet

Suggest

Dim intRateAs Double, initLoanAmnt As Double, loanLife As Double
Dim yrBegBal As Double, yrEndBal As Double
Dim biweeklyPmnt As Double, intComp As Double, princRepay As Double
Dim outRow As Long, rowNum As Long
DIm outSheet As String

[Hayden]

Maybe

For rowNum = 1 To loanLife * 26

If (outRow + rowNum) Mod 2 = 0 Then
    intComp = 0

Else
    If rowNum = 1 Then
        intComp = yrBegBal * intRate / 13
    Else
        intComp = shtLoanAmort.Cells(outRow + rowNum - 1, 2).Value * intRate / 13
    End If

End If

    princRepay = biweeklyPmnt - intComp
    yrEndBal = yrBegBal - princRepay
    shtLoanAmort.Cells(outRow + rowNum, 1).Value = rowNum  'Year number
    shtLoanAmort.Cells(outRow + rowNum, 2).Value = yrBegBal
    shtLoanAmort.Cells(outRow + rowNum, 3).Value = biweeklyPmnt
    shtLoanAmort.Cells(outRow + rowNum, 4).Value = princRepay
    shtLoanAmort.Cells(outRow + rowNum, 5).Value = intComp
    shtLoanAmort.Cells(outRow + rowNum, 6).Value = yrEndBal 'Lease buyout price
    yrBegBal = yrEndBal
Next rowNum

As a side note, without an explicit "As Double" things like intRate as Dim-ed as Variant

Dim intRate, initLoanAmnt, loanLife As Double
Dim yrBegBal, yrEndBal
Dim biweeklyPmnt, intComp, princRepay
Dim outRow, rowNum, outSheet

Suggest

Dim intRateAs Double, initLoanAmnt As Double, loanLife As Double
Dim yrBegBal As Double, yrEndBal As Double
Dim biweeklyPmnt As Double, intComp As Double, princRepay As Double
Dim outRow As Long, rowNum As Long
DIm outSheet As String

My apologies, I was actually able to figure it out with one line of code added:

For rowNum = 1 To loanLife * 26
If (outRow + rowNum) Mod 2 = 0 Then
    intComp = 0
Else
    intComp = shtLoanAmort.Cells(outRow + rowNum, 2).Offset(-1).Value * intRate / 13 <<<<<<<<<<<<<
    
End If

Just changed the intComp formula. Guess it is pretty close to yours!
Thanks so much for the help though!!