generate loan repayment schedule

[depash]

Hi all,

I am designing a program to calculate accrued interest on a loan. the way to go about this is first, u generate a full repayment schedule of the loan using the principle and the duration. then u calculate the interest per month using interest reducing balance method (e.g. 1% of remaining loan balance per month.) then u calculate the actual interest paid every month by the client upto date. then calculate the total interest on reducing balance and subtract from the actual interest paid to date. That should be my accrued interest. that aside, my problem is that i cant get vb6 to generate a repayment schedule given the loan start date showing the total months and their respective payments. e.g.
no month year installment balance
1 january 2010 1,000 9,000
2 february 2010 1,000 8,000
3 march 2010 1,000 7,000
4

[AndreRet]

This is a function I am using. You will need to play around a bit to get all the results you wanted above, but by using APR, PVal, FvAl etc, you can basically calculate any repayment option. Let me know if this helped. -

Add the function to a module -

Option Explicit

Public Sub TestPMT(txtPval As TextBox, txtAPR As TextBox, txtTotPmts As TextBox, txtFval As TextBox, txtResult As TextBox)

Dim PVal As Double, APR As Double, Payment As Double, TotPmts As Double, Fval As Double
Dim PayType
Dim Response As String, Fmt As String, strMessage As String

Fmt = Format(Fmt, "###,###,##0.00")
Fval = 0
PVal = CDbl(txtPval.Text)
APR = CDbl(txtAPR.Text)

If APR > 1 Then
    APR = APR / 100
End If

TotPmts = CDbl(txtTotPmts.Text)
Fval = (PVal * txtFval.Text) / 100

Payment = Pmt(APR / 12, TotPmts, -PVal, Fval, PayType)

txtResult.Text = Format(Payment, Fmt) & " per month."
End Sub

Add the numbers to the respective textboxes, and you should get all your answers. What makes this nice is that I had to search for Future Value payments, couldn't get any code and done the rest myself by trial and error.

[depash]

Thankyou very much AndreRet for the smart and quick reply. Please forgive my asking, but its only that am not very good in vb, but i wanted help on generating a schedule showing several months, my problem was how to autofill months given the starting month and year. or maybe your code does that?

Much regards

[AndreRet]

It was a pleasure Depash. I am not sure what you mean on creating a schedule? Give me a broader outline and I will see what I can do.

[depash]

Thanks brother,
maybe am presenting scanty information to you, let me try and give all the details. On my system, am supposed to display a loan repayment document that will show a customer the day, month and year he/she has been given a loan. this document should display successive months for monthly installment to be paid per month. I wanted a method in vb6 that can automatically generate successive months on loan repayment. either on a ms flexgrid or a list view. such that if i borrowed a loan today and i will repay within 4 months, then the system can generate a report that shows AUGUST, SEPTEMBER, OCTOBER AND NOVEMBER on the list view. infact if i can be helped on how to autofill a list view with several months(with year) from current date, that could help me alot. sorry for the long explanation. to illustrate this, i have attached a sample schedule as jpeg, LOOK AT THE PERIOD COLUMN.

regards

[AndreRet]

Now I understand. Yes you can use the values from what I gave you by using a for, next loop. a Quick sample which you must modify to suit your needs will be -

Dim xMonths As Integer

For xMonths = 1 To TotPmts
   Listview1.ListItem.Add ....

search for listview code samples on the net. You just need to play around with the values given in the module to determine interest (APR), months to pay, tot to pay (TotPmnts) etc. I will try and post a solution when time allows. Quite busy at moment. If you have it figured, please post here for all.

Thanks.

[depash]

Thank you very much AndreRet, you have been of great help to me so far, i sincerely appreciate that, i will try the code and definitely post it here if i get the way round it. Be blessed AndreRet

[AndreRet]

Again, only a pleasure. Happy coding.

Please mark as solved, thanks.

[depash]

Hi all,

Am grateful i was able to find my way round the problem i had posted. I used the dateadd function and the datediff function. the next thing was to implement a for loop to populate the list view. here is the code:

Private Sub cmdCalc_Click()
txtLastDay.Text = DateDiff("m", dtp_LoanDate.Value, dtp_LastDay.Value)

lv_list.ListItems.Clear
Dim g, c As Integer
Dim Taccrued, Treducing, Tflat As Double

c = lv_list.ListItems.Count
'calculate reducing flat rate interest
Dim intrate, duration, ans As Double
intrate = Val(txtIntRate.Text) / 100
duration = Val(txtPeriod.Text) + 1
ans = ((Val(txtPrinciple.Text) * duration) / 200) / Val(txtPeriod.Text)
ans = FormatNumber(ans)
'end
f = 0
For g = 1 To Val(txtLastDay.Text)
c = c + 1
Set lv = lv_list.ListItems.Add(, , c & ".")
lv.SubItems(1) = DateAdd("m", g, dtp_LoanDate.Value)
lv.SubItems(1) = Format$(lv.SubItems(1), "mmmm-yyyy")
lv.SubItems(2) = Val(txtPrinciple.Text) / Val(txtPeriod.Text)
lv.SubItems(2) = FormatNumber(lv.SubItems(2))
If g = 1 Then
lv.SubItems(3) = Val(txtPrinciple.Text) - Format(lv.SubItems(2))
lv.SubItems(3) = FormatNumber(lv.SubItems(3))
Else
'loop to calculate loan balance
f = f + 1
lv.SubItems(3) = Format(lv_list.ListItems(g - 1).SubItems(3)) - Format(lv.SubItems(2))

lv.SubItems(3) = FormatNumber(lv.SubItems(3))
End If
lv.SubItems(4) = Format(lv.SubItems(3)) * intrate
Treducing = Val(Treducing) + Format(lv.SubItems(4))

lv.SubItems(5) = ans
Tflat = Val(Tflat) + Val(Format(lv.SubItems(5)))

lv.SubItems(4) = FormatNumber(lv.SubItems(4))
lv.SubItems(5) = FormatNumber(lv.SubItems(5))
Next g
txtAccruedInt.Text = Val(Treducing) - Val(Tflat)
txtAccruedInt.Text = FormatNumber(txtAccruedInt.Text)
txtLoanBal.Text = lv_list.ListItems(c).SubItems(3)
lblLevy.Caption = Format(txtLoanBal.Text) * (Val(txtLevyRate.Text) / 100)
Set lv = Nothing

End Sub

thankyou again for listening to my problem.
regards