PDA

View Full Version : [SOLVED] VBA Biweekly payments and monthly interest payments



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

Paul_Hossler
03-03-2019, 08:39 AM
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
03-03-2019, 08:53 AM
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
03-03-2019, 09:13 AM
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
03-03-2019, 09:22 AM
I FOUND THE PROBLEM: The payment should be as shown in the excel file above. Why is it just a few cents off?