12-23-11, 01:31 #1Registered User
- Join Date
- Dec 2011
Unanswered: MS Access - Generate custom loan amortization schedule
I am attempting to create a loan amortization table using MS Access. I know this is usually more easily accomplished using Excel, but in this case I would prefer that Access perform all the calculations.
What I am first trying to accomplish is simply creating a compounding interest schedule that appends to a table.
I feel like it's pretty simple stuff - I'm just trying to produce:
For Period 1, Beginning Loan Balance = Loan_Amount
Interest Accrual = Loan Interest Rate * Beginning Loan Balance
Debt Payment = Debt Payment (based on common date query with my Debt_Payment_Schedule - already got that working)
Beginning Loan Balance + Interest Accrual - Debt Payment = Ending Loan Balance
For Period >1, Beginning Loan Balance = Ending Balance For Previous Period_Number **Not sure if this should be a Select(Last) or a Dlookup function**
Interest Accrual = Loan Interest Rate * Beginning Loan BalanceDebt Payment = Debt Payment (based on common date query with my Debt_Payment_Schedule - already got that working)Beginning Loan Balance + Interest Accrual - Debt Payment = Ending Loan Balance
Repeat as long as Period < Total Periods Until Maturity
The reason I want this I so that I can set up advanced logic in the debt payment field, yet accrue interest on a daily basis. I think I need to be using a Dlookup function, or possibly a set of queries, or possibly a macro? Any help would be much appreciated!!
The table I want is arranged like this, & I have been trying via a Query (that works correctly for the first record):
Forgive my garbled attempt here - I'm still a VBA newbie :
Field1 | Field2 | etc.
LoanControlBox is the table that holds the input variable - principal amount, interest rate, loan term etc.
[Period_Number] | Beginning Loan Balance: [LoanControlBox]![LoanAmount] | [Interest Rate] | Interest Accrual:[Beginning Loan Balance*][Interest Rate]] | Debt Payment: [Logic goes here for monthly, annual, payments etc.] | Ending Loan Balance: [Beginning Loan Balance] + [Interest Accrual] - [Debt Payment]
Then for Record 2, I want to set the Beginning Loan Balance = to the value of Ending Loan Balance for Record 1.
For Record 3, Beginning Loan Balance should = Ending Loan Balance for Record 2, etc.
Last edited by wrcromartie; 12-23-11 at 01:39.
03-26-13, 09:41 #2Registered User
- Join Date
- Mar 2013
very good forumla to create a loan amortization tablethank you, a week ago I have done it by Exel... you are right exel is more simple
I am going to create debt consolidation calculator In Exel and I will show my result
Last edited by debter; 03-28-13 at 11:27.
03-26-13, 13:13 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
yes Excel is a lot more straight forward, if you approach the problem in a spreadsheet way
a database is a lot more straight forward (and safer) if you approach the problem form a database perspectrive
spreadsheets a re a great analysis tool, they can be prone to all manner of problems especially if you relay on in cell formulas. users can deliberately or inadvertently screw up in cell formulae. because the logic is bundled with the data and user modifiable its all to easy to think all is well and good with your spreadsheet.
the database approach is to separate data from logic AND properly designed the logic isn't modifiable by users no matter how hard they try.
given a choice I'd palce more trust in a DB to give consistent results that a spreadsheet, every timeI'd rather be riding on the Tiger 800 or the Norton