Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    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 02:39.

  2. #2
    Join Date
    Mar 2013
    Posts
    2
    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 12:27.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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 time
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •