Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    8

    Unanswered: Help with formula for Amortization

    I inherited a database for the company I work for and since I am an amateur access programmer I am the goto guy. I am looking to create a formula to amortize a loan. I found some code that does this but It is way above my knowledge to change it for my needs. Any help would be greatly appreciated. This has to be done in Access not in Excel!
    Here is what I'm trying to do:
    End Result:
    I want to enter a number of months to calculate a payoff on a loan and if possible a payment amount to calculate the number of months to payoff a loan.
    Conditions:
    Payment amount must be > than Minimum Payment of loan
    A loan term is 30 days, not a month, because a month can have differing days in each month.
    Important Equations that factor in to figuring this formula out:
    ((Amount*rate)+Amount)=Total Loan Amount for a 30 day period
    Month(Time Increment)=30 days
    Minimum Payment=Amount * Rate

    As I mentioned any help or pointers would be greatly appreciated. I found an access amortization calculator for a mortgage online but I was unable to configure it for my needs for a different type of loan amortization.
    Thanks in advance for any help or pointers!
    Last edited by Bmw; 01-18-11 at 23:38. Reason: diction

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    Help with formula for Amortization

    Amount = 100
    Period = 12 months ( 30 days x 12 months = 360 days in a year)
    Rate = 0.1 (or 10%)

    Installment Amount = (Amount/period)*(1+(Rate/360*30))^period
    = 9.205942
    Total payment at the end of the period = Amount*(1+(Rate/360*30))^period
    = 110.4713
    Last edited by apr pillai; 01-21-11 at 14:30.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Sep 2010
    Posts
    8
    First of all Thank your for your reply.
    I looked at your formula I believe I need to provide some additional details.
    A Loan term is 30 days
    My loan is based on a 30 day Period
    A customer borrows money 1/21/11 the due date for repayment would be 02/20/11.
    Amount:$100
    Rate:10%
    Therefore Interest :$10
    Total Due Back on 02/20/11 :$110

    The terms of the loan is 30 days. So 2 things that can happen is:
    1. On 02/20/11 or sooner they can pay the loan off for $110
    2. On 02/20/11 Pay the interest of $10 and "reborrow" the $100 for another 30 days under the same terms.

    I want to provide an amortization schedule in which I can say enter a term of let say 12 months, maybe more or maybe less and it will provide me an amount they need to pay to achieve this goal.

    I tested Your formula. It is Spot on correct on a yearly basis but unfortunately I do not know how to modify it to suit my needs as described above.
    Any further help would be greatly appreciated.

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
  •