Interest Calculation

[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 I) @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 I2 @ 5% for 1Months. In I3 @5% for 2Months.In I4 @5% for 1Months
And so no.

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:68px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:90px;" /><col style="width:82px;" /><col style="width:122px;" /><col style="width:132px;" /><col style="width:133px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-color:#000000; ">S.No</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Due Date</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Amount</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Due Days</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Date Received</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Paid Amount</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Interest @5%</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">DR.</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:2px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Charges @5% ON Dr</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Feb-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2-Mar-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6166</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">126</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Mar-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6-Apr-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6000</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">292</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Apr-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4-May-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6000</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">292</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-May-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">30-Jun-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">7000</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">-708</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:2px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">28-Jun-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">15</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">1-Jul-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">6166</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">126</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=+C2-F2</td></tr><tr><td >H3</td><td >=+C3-F3</td></tr><tr><td >H4</td><td >=+C4-F4</td></tr><tr><td >H5</td><td >=+C5-F5</td></tr><tr><td >H6</td><td >=+C6-F6</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4



Thanks In Advance

Hardeep kanwar

 

[venkat1926]

Then Calculate the Extra Interest for First month in I2 @ 5% for 1Months. In I3 @5% for 2Months.In I4 @5% for 1Months

what is the logic of this. why one month only, why two months etc.

 

[hardeep.kanwar]

what is the logic of this. why one month only, why two months etc.

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.

Or Could you Please tell me what Formula i Can Use to Calculate the Interest Rate for a month

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:68px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:90px;" /><col style="width:82px;" /><col style="width:122px;" /><col style="width:132px;" /><col style="width:132px;" /><col style="width:136px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; text-align:center; border-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-left-width:2px; border-color:#000000; ">S.No</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Due Date</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Amount</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Due Days</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Date Received</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Paid Amount</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Interest @5%</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">DR.</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Total of DR.</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:2px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Charges @5% ON Dr</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Feb-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2-Mar-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6166</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">126</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">126</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Mar-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6-Apr-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6000</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">418</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Apr-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4-May-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6000</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">584</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-May-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">30-Jun-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">7000</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">-708</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">-416</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:2px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">28-Jun-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">15</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">1-Jul-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">6166</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">*</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">126</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">-582</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=+C2-F2</td></tr><tr><td >I2</td><td >=+H2</td></tr><tr><td >H3</td><td >=+C3-F3</td></tr><tr><td >I3</td><td >=+H2+H3</td></tr><tr><td >H4</td><td >=+C4-F4</td></tr><tr><td >I4</td><td >=+H3+H4</td></tr><tr><td >H5</td><td >=+C5-F5</td></tr><tr><td >I5</td><td >=+H4+H5</td></tr><tr><td >H6</td><td >=+C6-F6</td></tr><tr><td >I6</td><td >=+H5+H6</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Thanks In Advance

 

[venkat1926]

if the interest is 5% per annum theintersts wil be
=126*5%/12=ra. 0.525
if the interest is 5% per month it is
=126*5%=Rs 6.3
this is simple interest.

if it is compound interest it will be different depending upon the compounding period.

see help under COMPINT.
<title>CUMIPMT</title><link href="office10.css" type="text/css" rel="stylesheet"><script language="JavaScript" src="ExpCollapse.js"></script><script language="JavaScript" src="tfplug.js"></script>If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

 

[hardeep.kanwar]

if the interest is 5% per annum theintersts wil be
=126*5%/12=ra. 0.525
if the interest is 5% per month it is
=126*5%=Rs 6.3
this is simple interest.

if it is compound interest it will be different depending upon the compounding period.

see help under COMPINT.
<title>CUMIPMT</title><link href="office10.css" type="text/css" rel="stylesheet"><script language="JavaScript" src="ExpCollapse.js"></script><script language="JavaScript" src="tfplug.js"></script>If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

Thank for the Quick Reply

If the Interest Rate is 5% per Month

And i have to Calculate for Days only

In Column F i want to Calculate for Days only @ 5% per Month

See Example
<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:68px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:87px;" /><col style="width:87px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; text-align:center; border-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-left-width:2px; border-color:#000000; ">S.No</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Due Date</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Amount</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Due Days</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Interest Rate</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:2px; border-right-width:1px; border-bottom-width:2px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Total</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Feb-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5%</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Mar-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5%</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-Apr-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5%</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">28-May-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5%</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:2px; border-left-width:2px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">28-Jun-06</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">6292</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">15</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">5%</td><td style=" border-right-style:solid; border-right-width:1px; border-right-color:#000000; ">*</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[venkat1926]

In f2 type the formula
=C2*E2*D2/30
copy F2 down.
this will be little approximate because the number of days in a month may be 28,29,30 or 31.

 

[hardeep.kanwar]

In f2 type the formula
=C2*E2*D2/30
copy F2 down.
this will be little approximate because the number of days in a month may be 28,29,30 or 31.

Thanks Venkat1926

Hardeep kanwar