# Accounting/Math Question?

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

Messages:
7,370
2,711
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. ### ThomasStylish Dinosaur

Messages:
29,128
1,310
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

Messages:
7,370
2,711
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. ### ThomasStylish Dinosaur

Messages:
29,128
1,310
Joined:
Jul 25, 2006
Location:
Texas
Looks right to me.

Messages:
7,370
2,711
Joined:
Jul 31, 2010
Location:
NJ

6. ### syed moin uddinNew Member

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

Messages:
29,128