Can anyone help me out with my Loan Management Database project? (1 Viewer)

[shebishelby]

I am working on this loan Management form that records information of clients that borrows money and records their details in a database. it then has a Repayment Schedule query that list out the number of installments, the due date the outstanding balance, and the principal after the installments are paid.
The formula i used is incorrect, its used to calculate fortnightly installments only. The Access file is attached below.

InstNo: [Nums].[Num] + ([Nums_1].[Num] * 10) + ([Nums_2].[Num] * 100)

Installment: Round(Pmt(([IntRate]/26)/100, [TotalPeriod], -[LoanAmt], 0, [PaymentMode]), 2)

Interest: Round(IPmt(([IntRate]/26)/100, [InstNo], [TotalPeriod], -[LoanAmt], 0, [PaymentMode]), 2)

Due Date: DateAdd("d", ([InstNo] - 1) * [PaymentType] * 14, [StartDate])

Outstanding: Round([LoanAmt] - ((Pmt(([IntRate]/26)/100, [TotalPeriod], -[LoanAmt], 0, [PaymentMode]) * [InstNo]) - TotInt([IntRate]/26)/100, [TotalPeriod], [LoanAmt], 1, [TotalPeriod], [PaymentMode])), 2)

Principal: [Installment] - [Interest]

TotRepayment: Round((Pmt(([IntRate]/26)/100, [TotalPeriod], -[LoanAmt], 0, [PaymentMode])) * [TotalPeriod] * 26, 2)

Interest: [TotRepayment] - [LoanAmt]


Function TotInt(IntRt As Double, TotPay As Double, TotAmt As Double, StartPeriod As Long, EndPeriod As Long, PaymentMode As Long) As Double
Dim i As Long
Dim j As Double
j = 0

For i = StartPeriod To EndPeriod
j = j + IPmt(IntRt / 26, i, EndPeriod - StartPeriod + 1, -TotAmt, 0, PaymentMode) * TotPay
Next i

TotInt = -Round(j, 2)
End Function

 

[The_Doc_Man]

OK, immediately you have asked a question that requires us to clarify the nature of this forum.

We can certainly tell you how "standard" amortization generally works (well,... some of us can; I'm a chemist by original training.) But you are the subject matter expert on amortization. If you have any local rules and regulations, it is unlikely that we will know them. You are from Australia. I'm from Louisiana. Therefore I won't know anything about your national or state or local regulations on loan management.

My first response, therefore, is this: If you know that the formula you used is incorrect, (a) HOW do you know that and (b) do you know the correct formula? I'm not trying to dishearten you. I'm just trying to make sure that you understand that unless someone here is from your area, we might not know the correct formula either. We can tell you how Access works. But we will only know general facts regarding amortization. If you can fit your questions into that framework, we'll be glad to help.

 

[The_Doc_Man]

Having said that, there is another ongoing thread in this forum at the moment that discusses amortization. See if it helps.

Creating an Amortisation Schedule in Access

Hi all, I am new to the forum and using ms access. I would really like some help creating the attached excel loan schedule in access. The fields shaded yellow would be populated using a form and those shaded green would be calculated in the db. Please kindly assist with the request :banghead:

www.access-programmers.co.uk

 

[LarryE]

I am working on this loan Management form that records information of clients that borrows money and records their details in a database. it then has a Repayment Schedule query that list out the number of installments, the due date the outstanding balance, and the principal after the installments are paid.
The formula i used is incorrect, its used to calculate fortnightly installments only. The Access file is attached below.

I looked up what a fortnight is, and it's 14 days. Would it be correct is you removed the 14 from:
Due Date: DateAdd("d", ([InstNo] - 1) * [PaymentType] * 14, [StartDate]), so
Due Date: DateAdd("d", ([InstNo] - 1) * [PaymentType], [StartDate])

 

[Pat Hartman]

Or, you can look up the pmt() function to find out the arguments.

Pmt function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Payment = Pmt(APR / PmtsPerYear, TotPmts, -PVal, FVal, PayType)

If you compare this to what you have, "PmtsPerYear" is hard-coded at 26. We don't know what you want. Typically mortgages are paid monthly so the value would be 12. Substitute whatever is "right". Making this a variable in the application rather than hard-coded is much more flexible. Also, since you are using the function 4 times in your code, when you do change the value, you have to remember to change ALL instances. Best to just make it a variable NOW since you have to change it anyway.

PS TotPmts = mortgage term * payments per year so you must have used the PmtsPerYear value in other places in the application so you need to change them also. That is why we don't hard code values.