Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interest Calculations
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interest Calculations
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? --- "Hardeep_kanwar" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interest Calculations
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. -- David Biddulph "Hardeep_kanwar" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interest Calculation
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interest Calculation
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interest Calculation
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. --- "Hardeep_kanwar" wrote: Ok Just take the Simple Example 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). |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interest Calculation
"Bob Bridges" wrote:
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 ----- "Bob Bridges" wrote in message ... 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. --- "Hardeep_kanwar" wrote: Ok Just take the Simple Example 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interest calculations. | New Users to Excel | |||
interest calculations | Excel Worksheet Functions | |||
to compute interest rate from principal and interest amount | Excel Discussion (Misc queries) | |||
Interest and capital calculations | Excel Discussion (Misc queries) | |||
Why are cumulative interest payment calculations different? | Excel Worksheet Functions |