Installment payment

You may find this Visual Basic function useful. It is adapted from the Alex Evans' C# code posted  here:
 
http://www.codeproject.com/KB/recipes/loan.aspx

This explains the variables.
NPV        or Net Present Value, this is the amount of the loan
FV       or Future Value (or sometime known as Residual, or Pay-Out) is the amount of money that will still be outstanding as the final payment AFTER all the payments are made.
NumPay       Number of Payments / Instalments, for example if a monthly payment is made for a period of 5 years, this will equal 60.
IntRate       Interest Rate, expressed as say 10.00 (for 10%)
bStart       this should be a 0 or 1, if 0, each repayment is made at the end of the period (end of month) otherwise, payments are made ahead of each period.

Function MKCalcPayment(NumPay As Integer, IntRate As Double, NPV As Double, FV As Double, bStart As Boolean) As Double
Dim p As Double
 
    IntRate = IntRate / 1200
     p = (-NPV * ((1 + IntRate) ^ NumPay) + FV) / _
               ((1 + IntRate * bStart) * ((((1 + IntRate) ^ NumPay) - 1) / _
               IntRate))
    MKCalcPayment = -p  '// Just convert it into a positive value.
 
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

jasonkk,

Access has 3 built in Payment functions.

Just check the help files for all the info you need.

JeffCoachman

I just want to know how to copy the data from loan table to payment table based on payment schedule.
let's say, I input the data for loan_amt, period, start_date and payment
Then with query or vb code to add  payment and payment schedule date to the payment table based on periond(i.e. 10 payments) which is 10 records with scheduled date.

If this is too much to ask then I'm sorry.
 

jasonkk,

I don't mind helping but I don't fully understand.

In order to calculate any payment, you need an interest rate.
You never mentioned an Interest rate in any of your posts.

Also needed is when to compounding starts, at the start of the first period or the end.

Did you investigate *any* of this on you own?
It is easy enough to do in Excel.

JeffCoachman

Jeff,
You don't have to worry about interest or calculate payment.
I'm going to manually calculate payment and enter into the payment column

I need a query or vb code that  copy specific datas from one table(loan table) to another table(payment table).
But copy 10 times if the payment term is 10 into the another table(payment table) with payment scheduled date.

If you look at my sample db then it will be more clear.
Source is loadn table and result will be like payment table.

Thansk

Great.

Where can I find your sample?

JeffCoachman

I didn't know that can't attach mdb file.
I changed extention to .doc

Actually, it is not copying data between table.
I'm going to create a form and from the form data it will save into 2 tables but loan table will store all the datas from form and payment table will get only partial data times payment term.
So I need a query or vb code based on the form.

Thanks

Thank you