Accounting/Math Question?

Discussion in 'General Chat' started by Brianpore, Mar 5, 2012.

  1. Brianpore

    Brianpore B&S (BS) ambassador-in-chief

    Messages:
    7,325
    Likes Received:
    2,678
    Joined:
    Jul 31, 2010
    Location:
    NJ
    Trying to figure out a table/calculations for this problem (not the exact numbers but just need to know how to do it. Initial loan of $10,000. Interest rate of 7%. Loan term is 6 months. Payment made every month is $200.

    Any help with setting up a chart/ table in excel that will shoe the amount of interest paid each month, amount paid towards the principal balance and the balance left each month?

    Cant figure this out for the life of me and everything I look for online just shoes how much you have to pay to get to a $0 balance which is not what I am trying to get. I want to know how much the balance is at the end of each month and how much of the $200 went to the interest and how much to the principal.

    Thanks!!!
     


  2. Thomas

    Thomas Senior member

    Messages:
    29,119
    Likes Received:
    1,303
    Joined:
    Jul 25, 2006
    Location:
    Texas
    

    Take your principal in column A. Enter your monthly rate in Column B (.07/12). Calculate the interest incurred on the principle for month one in column C (=Col A * Col B). Enter your payment in Column D. Column E = Column D - Column C and is your payment applied to principal. Column F = Column A - Column E
    In column A, under your original principal, link to Column F, first row.
    Copy the rest of the row after column A
    Copy each row down from there.
     
    Last edited: Mar 5, 2012


  3. Brianpore

    Brianpore B&S (BS) ambassador-in-chief

    Messages:
    7,325
    Likes Received:
    2,678
    Joined:
    Jul 31, 2010
    Location:
    NJ
    Thanks!!! Hopefully this is correct?

    Principal
    $10,000.00​
    Interest
    7.00%​
    Monthly Interest
    0.005833333​
    Term (Months)
    6​
    Payment Per Month
    $200.00​

    Months Starting Balance Total Amount Paid Paid Interest Paid Down Ending Balance
    1
    $10,000.00​
    $200.00​
    $58.33​
    $141.67​
    $9,858.33​
    2
    $9,858.33​
    $200.00​
    $57.51​
    $142.49​
    $9,715.84​
    3
    $9,715.84​
    $200.00​
    $56.68​
    $143.32​
    $9,572.52​
    4
    $9,572.52​
    $200.00​
    $55.84​
    $144.16​
    $9,428.36​
    5
    $9,428.36​
    $200.00​
    $55.00​
    $145.00​
    $9,283.35​
    6
    $9,283.35​
    $200.00​
    $54.15​
    $145.85​
    $9,137.51​
     


  4. Thomas

    Thomas Senior member

    Messages:
    29,119
    Likes Received:
    1,303
    Joined:
    Jul 25, 2006
    Location:
    Texas
    Looks right to me.
     




  5. syed moin uddin

    syed moin uddin New Member

    Messages:
    1
    Likes Received:
    0
    Joined:
    Dec 6, 2013
    how did you find the percentage like ..paid down..141.67...monthly interest 0.0583..please tell me ...
     


  6. Thomas

    Thomas Senior member

    Messages:
    29,119
    Likes Received:
    1,303
    Joined:
    Jul 25, 2006
    Location:
    Texas
    monthly interest is the annual interest rate/12. calculate the interest on the outstanding balance, subtract the interest from the payment and you have amt paid to principal.
     


Share This Page

Styleforum is proudly sponsored by