VBA Biweekly payments and monthly interest payments

[Hayden]

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

[Paul_Hossler]

Welcome to the forums

Please take a minute and read the links in my signature, esp the parts about multi-posing and CODE tags

I think your math is wrong (first <<<<<<) and the final iteration has to be handled specially (second <<<<<)

There might be interest owed on the final partial payment -- not sure

'************************************************************
' Compute and output results
'************************************************************
' Calculate biweekly payment
biweeklyPmnt = Pmt(intRate / 26, loanLife * 26, -initLoanAmnt)  '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' Initialize beginning balance for year 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
    
    If yrBegBal < biweeklyPmnt Then        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        shtLoanAmort.Cells(outRow + rowNum, 3).Value = yrBegBal
        shtLoanAmort.Cells(outRow + rowNum, 4).Value = yrBegBal
        shtLoanAmort.Cells(outRow + rowNum, 5).Value = 0
        shtLoanAmort.Cells(outRow + rowNum, 6).Value = 0
    Else
        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
    End If
Next rowNum

[Hayden]

Why is the Pmt function using intRate / 26 when it is monthly compounding? The whole thing works thank you, but I need to understand. Also, is there a way to make every payment equal? Currently, the last one is the same as the yrBegBal.

[Hayden]

When I do it in excel I get a payment of a few more cents but no uneven payment at the end. How would I do this in vba?

[Hayden]

I FOUND THE PROBLEM: The payment should be as shown in the excel file above. Why is it just a few cents off?