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!!