how to calculate interest on payments

  • Thread starter Thread starter Bob L
  • Start date Start date
B

Bob L

I need a formula/spreadsheet that will calculate 18% interest on delinquent
quarterly payments.

Example: Tenant owes $100 every quarter but has not made a payment since
2006.

I need to be able to plug in the "past due" payment, accumlate the balance
and compute the interest due..
 
Bob

Assuming you mean 18% p.a. for whole years only from the date the rent was
due this may do it for you. If it doesn't come out clearly post your email
address and I'll forward the s/s
31/03/2007 100 2.682191781 2 04/12/2009 36
30/06/2007 100 2.432876712 2 04/12/2009 36
30/09/2007 100 2.180821918 2 04/12/2009 36
31/12/2007 100 1.928767123 1 04/12/2009 18
31/03/2008 100 1.679452055 1 04/12/2009 18
30/06/2008 100 1.430136986 1 04/12/2009 18
30/09/2008 100 1.178082192 1 04/12/2009 18
31/12/2008 100 0.926027397 0 04/12/2009 0
31/03/2009 100 0.679452055 0 04/12/2009 0
30/06/2009 100 0.430136986 0 04/12/2009 0
30/09/2009 100 0.178082192 0 04/12/2009 0

"=SUM(G11-A11)/365" "=ROUNDDOWN(D11,0)" "=TODAY()" "=SUM(B11*E11*18%)"



Total rent 1100 Accumulated Interest 180 Grand total 1280
 
Bob L said:
I need a formula/spreadsheet that will calculate 18% interest on
delinquent
quarterly payments.
Example: Tenant owes $100 every quarter but has not made a payment since
2006.
I need to be able to plug in the "past due" payment, accumlate the balance
and compute the interest due.
Click to expand...

The answer depends on a couple points that need clarification.

Is 18% an annual or a quarterly rate?

Is 18% truly "interest" (which often compounds), or is it a "penalty" (which
often does not compound)?

If A1 is the number of consecutive missed payments (12), A2 is the quarterly
rate (18%/4 or 18%), and A3 is the payment due (100):

1. If interest is compounded, then:

total interest due: =FV(A2, A1, -A3*A2)
total delinquent payments: =A3*A1

2. If the penalty is not compounded, then:

total penalty due: =A3*A2*A1
total delinquent payments: =A3*A1
 
Back
Top