Interest Calculations

[Hardeep_kanwar]

Dear Experts
My data in 150 Sheets its a Account Data for 150 customers.

In column C i have amount after Due Data which is in Column B.And In Column
F i have a Amount Which is Paid by Customers.

First i want to Calculate the Extra Interest In Column G @5% Depending on
the numbers of Due Days (Column D) after the Due Dates i.e. is in (Column B)

Secondly If the Customer is Paid the Less Amount (Column F)of his Due Date
Amount (Column C), Then Calculate the Extra Interest in (Column J) @5% On Per
Month.

But If Customer is not Clearing his Dr. ( Column H) in Second or Third Month
then Calculate the Extra Interest in (Column I)

For Example: Say Customer will Not Clearing his Dr.( Column H) for 3 Months
As Example in H2,H2,H4

Then Calculate the Extra Interest for First month in J2 @ 5% for 3Months. In
J3 @5% for 2Months.In J4 @5% for 1Months
And so no.



Sheet2

A B C D E F G H I J
1 S.No Due Date Amount Due Days Date Received Paid Amount Interest @5% DR.
Total of DR. Charges @5% ON Dr
2 1 28-Feb-06 6292 2 2-Mar-06 6166 126 126
3 2 28-Mar-06 6292 5 6-Apr-06 6000 292 418
4 3 28-Apr-06 6292 6 4-May-06 6000 292 584
5 4 28-May-06 6292 4 30-Jun-06 7000 -708 -416
6 5 28-Jun-06 6292 15 1-Jul-06 6166 126 -582

Spreadsheet Formulas
Cell Formula
H2 =+C2-F2
I2 =+H2
H3 =+C3-F3
I3 =+H2+H3
H4 =+C4-F4
I4 =+H3+H4
H5 =+C5-F5
I5 =+H4+H5
H6 =+C6-F6
I6 =+H5+H6

Actually In column H is a DR. on Customers and Customer Will Pay this Late
Fees Charges With Interest Rate @5%.

For ex: In H2=126,H3=292,H4=292

See (Column I) Then Charge the Late Fee Charges@5% in Column J

Means if Customer is not Clearing his DR. then he have to paid Interest on
his Amount.

Any Help Will be most Appreciate

 

[Bob Bridges]

Whew! This question needs work, Hardeep_kanwar. I can't tell what a "Dr."
is - not a doctor, I take it. I don't know what you mean by "extra" interest
in columns G, I and J; why not just interest? And I don't know what a "less
amount" is (re col F). Can you find a way to say less about this problem and
explain more? Maybe just tackle one small piece of the problem at a time?

 

[David Biddulph]

First bit of advice is to replace all your =+ occurrences by =. You don't
need a + at the start of a formula in Excel. That is a relic from a Lotus
spreadsheet.

I'll let someone else answer the complicated part.

 

[Hardeep_kanwar]

Ok Just take the Simple Example

My first Question
A1=6292
A2=2 Days
A3=5%

Now i want to Calculate the interest rate of 6292 of 2 Days @5% per Month.


Second question i will ask in my next Post for Better Clarification.

This part is for your Clarification.
Dr. is not Doctor it is DEBIT

Sorry ,it is only INTEREST not Extra Interest.

Acutally it is a Amount which is paid by the Customer And he have to paid
the Amount in column C(C1=6292) but he paid only Column F(F1=6166).


Thanks in Advance

Hardeep Kanwar

 

[JoeU2004]

A1=6292
A2=2 Days
A3=5%
Now i want to Calculate the interest rate of 6292 of 2 Days @5% per Month.

First, anyone who charges 5% per __month__ should be shot. That's 60% per
year! I wonder if you mean 5% per year ;-).

Second, for a daily interest rate, you normally need to speicfy the month so
that you can determine the days per month. We could use 30 or 365/12. But
that is not really right.

Finally, if you want the interet __rate__, the amount is irrelevant. The
first formula below computes the interest __rate__. The second formula
computes the amount of interest. Both formulas assume a 30-day month. I
also assume that A2=2, not literally "2 Days".

=A2*A3/30

=A1*A2*A3/30

PS: If you truly meant that 5% is an annual rate, replace 30 with 365.

 

[Bob Bridges]

I had the same reaction as JoeU2004, by the way; 5% compounded monthly is
actually 80% per year, not 60% (because of compounding), which is pretty dang
high. However, onward: Prepare for a lecture on interest.

When a customer owes you $100 and has agreed to pay the piratical rate of 5%
per month, that means that after a month he owes you the original $100 plus
5% of that $100, or $5, which is $105 total. The next month, assuming he
hasn't paid anything, he owes $105 plus an additional 5%, or $5.25, for a
total of $110.25. The next month another 5% adds $5.51, then $5.79 and so
on. At the end of 12 months his bill is up to $179.59.

Now the way to calculate this without going through a complicated loop is to
view this in a slightly different way. Instead of multiplying the principal
by 0.05 and adding the result back in, think of it as multiplying the
principal by 1.05. At the end of the first month you multiply it by 1.05,
which turns the $100 debt into $105. The second month you multiply it by
1.05 and you have $110.25; times 1.05 again is $115.76, and so on.

To sum up, when you're charging interest at i% per period, then after n
periods the new value he owes you is the original principal times (1+i) n
times, or the principal * (1+i) ^ n. If you're charging 5% per month, then
after 12 months he owes you the principal * 1.05 ^ 12; if you're charging 8%
per year, then after 30 years he owes the principal * 1.08 ^ 30. And so on.

But there's a difference: You want to charge 5% per month but calculate the
interest after n days. No problem: You can assume an approximate 30 days in
a month and say that 5% per month is 5/30 or 1/6% per day. Or, if you want
to be more precise - no point in that, I think, but you can if you like -
there are actually more like 30.4375 days in a month, so 5% per month is
0.164271047% per day. Doesn't matter to me which you use; the point is that
again, after n periods (days in this case) the new debt is the original
principal times (1+i) ^ n - that is, if he owes you $6292 then after two days
he owes you $6292 * 1.016667 ^ 2, or $6312.99. That's how interest is
calculated.

You mentioned his having paid part of the debt. Well, say he owes $1000 at
5% per month, and after 15 days he pays $500. But during that 15 days
interest has been accumulating at 0.16667% per day, so that $500 no longer
represents half of the debt:

Mar 31: He owes you $1000

Apr 15: The debt is now $1000 * 1.16667 ^ 15, or $1 025.29. Then he paid
$500, so he now owes $525.29.

Apr 30 (15 days later): The $525.29 has grown to $538.58; he pays another
$500, and owes 38.58.

May 15 (15 days later): The $38.58 has grown to $39.56, which he can pay you
to clear the debt.

 

[JoeU2004]

I had the same reaction as JoeU2004, by the way; 5% compounded
monthly is actually 80% per year, not 60% (because of compounding)

I disagree. But this might vary depending on the country.

In the US, if the annual interest rate is 60%, the __nominal__ monthly rate is 5% (60%/12). Ergo, if the monthly rate is 5%, the __nominal__ annual rate is 60% (12*5%).

This is consistent with the language of the US "Truth In Lending" act (Reg Z), specifically Appendix J(b)(1), to wit: "The annual percentage rate shall be the __nominal__ annual percentage rate determined by multiplying the unit-period rate by the number of unit-periods in a year."

In the US, the annual interest rate and APR are different only if other costs besides the loan amount are factored in. The APR differs, not because of compounding, but because of a change in the PV of the loan due to including the other costs.

The next month, assuming he hasn't paid anything,
he owes $105 plus an additional 5%

That's the flaw in your reasoning, at least as it relates to determining the annual interest rate for US loans. In the US, we assume that all payments are made on time. Consequently, the periodic rate is the rate that would reduce the loan (less qualified up-front costs) to zero over the specified term of the loan, given a specified periodic payment amount. The annual rate is the periodic rate times the number of periods per year.

(Of course, normally the lender computes things the other way around. The lender determines the required annual interest rate based on a required return over time, then computes the periodic payments using the annual rate divided by the number of periods per year.)

I should note that this is a for "closed-end" loan with a single advance (loan amount) -- the type of loan that the OP is talking about, I presume.


----- original message -----