Hayden
03-03-2019, 08:00 AM
Hello all, I am trying to show every biweekly payment and every monthly interest paid, therefore, every other biweekly interest paid must be 0. When I input my code to do so within my loop, everything goes negative and I am trying to get down to 0. I am not sure what I am doing wrong.
' Calculate biweekly payment
biweeklyPmnt = Pmt(intRate / 13, loanLife * 26, -initLoanAmnt)
' Initialize beginning balance for period 1
yrBegBal = initLoanAmnt
' Loop to calculate and output year-by-year amort. table
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
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
' Calculate biweekly payment
biweeklyPmnt = Pmt(intRate / 13, loanLife * 26, -initLoanAmnt)
' Initialize beginning balance for period 1
yrBegBal = initLoanAmnt
' Loop to calculate and output year-by-year amort. table
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
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