Amortization Schedule

acarterczyz

Registered User.
Local time
Today, 10:16
Joined
Apr 11, 2013
Messages
68
Hey all!

I'm trying to create an amortization schedule query that will create the schedule in a table. The amortization schedule will need to factor in over-payments and allow me to stub in principal balance adjustments (the over payments). Does anyone know if there is working code out there? I've ran into 1 example built on Access 2003 and can't get it to work in my Access 2010.

Thanks in advance! :cool:
 
if it works in 2003, then it will work in 2010. Suspect you have not copied it correctly.

you just need to find an amortisation schedule that suits your needs, then ensure you have the appropriate data in your tables, then build your query

b*c/d is the same in any language
 
Thank you. I think I've gotten it figured out.
The only problem I'm running into now is getting over-payments added to the amortization schedule.

Amortization Schedule SQL:
Code:
SELECT IndexTbl.AccountNumber, ExNums.InstNo, IndexTbl.PaymentAmount AS Installment, DateAdd("m",([InstNo]-1)*1,DateAdd("m",1,[CommencementDate])) AS [Due Date], Round(IPmt(([LendingRate]/12)/100,[InstNo],[TermMonths],-[TotalLending],0,0),2) AS Interest, [Installment]-[Interest] AS Principal, Round([TotalLending]-((Pmt(([LendingRate]/12)/100,[TermMonths],-[TotalLending],0,0)*[InstNo])-TotInt(([LendingRate]/12)/100,[TermMonths],[TotalLending],1,[InstNo],0)),2) AS OutStanding
FROM IndexTbl, ExNums
WHERE (((ExNums.InstNo) Between 1 And [TermMonths]))
ORDER BY ExNums.InstNo;

Nums SQL:
Code:
SELECT Nums.Num+(Nums_1.Num*10)+(Nums_2.Num*100) AS InstNo
FROM Nums, Nums AS Nums_1, Nums AS Nums_2
ORDER BY Nums.Num+(Nums_1.Num*10)+(Nums_2.Num*100);

Nums gets the number of payments when building the amort schedule.
Anyone know how I'd go about adding extra payments into the schedule to reduce the balance? (Ex: I have a $40,000 car loan and payment is $700 a month, but I pay an extra $150 every so often to pay the loan down faster).
 

Users who are viewing this thread

Back
Top Bottom