or Connect
Styleforum › Forums › General › General Chat › Accounting/Math Question?
New Posts  All Forums:Forum Nav:

Accounting/Math Question?

post #1 of 7
Thread Starter 

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!!!

post #2 of 7
Quote:
Originally Posted by Brianpore View Post

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!!!

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.
post #3 of 7
Thread Starter 

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

 

post #4 of 7
Looks right to me.
post #5 of 7
Thread Starter 

Thanks for the help!!

post #6 of 7

how did you find the percentage like ..paid down..141.67...monthly interest 0.0583..please tell me ...

post #7 of 7
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.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › Accounting/Math Question?