Simple Interest Calculation Help in Excel

[zaska]

Hello,

I am stuck in calculating Effective Interest in the following situation.

Principal Amount : 10,000
Interest Amount : 1,500 (For 100 Days)
Repayment : 100 Days

Assuming that Rs.100 /- is Repaid Daily and Rs.10,000 is Rotated Thrice in 365 Days i.e 300 Days.

Here The Loan Taker will Get Only Rs.8500/- i.e The interest amount is collected in advance.

Could anyone help to calculate what will the interest rate for the whole year and how much amount the money lender could have earned?


Thanks,

Zaska

 

[zaska]

I think I understand now. If the interest rate = 15.00%, the daily compound rate is equal to 16.1798% APY. If both the 16.1798% interest and the $1,500 fee are paid upfrount the APY = 120.0508%:

<table style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border="0" cellpadding="0" cellspacing="0" width="264"><colgroup><col style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width="97"><col style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width="92"><col style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width="75"></colgroup><tbody><tr style="HEIGHT: 14.25pt" height="19"><td style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: #dce6f1; WIDTH: 198pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: black 2pt double" class="xl85" colspan="3" height="19" width="264">From the borrower's standpoint</td></tr><tr style="HEIGHT: 13.5pt" height="18"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: #dce6f1; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl75" height="18">Received</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl76"> $ 8,056.72 </td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class="xl77">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: #dce6f1; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl75" height="17">Paid Back</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl76"> $ 10,000.00 </td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class="xl77">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: #dce6f1; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl75" height="17">Return</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl78">24.12%</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class="xl77">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: #dce6f1; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl75" height="17">Annually</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl79">3.650000 </td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class="xl77">
</td></tr><tr style="HEIGHT: 13.5pt" height="18"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: #dce6f1; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl75" height="18">APR</td><td style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class="xl80">88.0382%</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class="xl77">
</td></tr><tr style="HEIGHT: 14.25pt" height="19"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: #dce6f1; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl75" height="19">APY</td><td style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: #d4d0c8" class="xl81">120.0508%</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class="xl77">
</td></tr><tr style="HEIGHT: 14.25pt" height="19"><td style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: #dce6f1; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl82" height="19">
</td><td style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl83">
</td><td style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class="xl84">
</td></tr></tbody></table>

Here the Borrower received Rs.8500 after deducting Rs 1500 towards interest ( It is not upfront fee ) and no other charges are levied.

I think there is an oppourtunity for the lender to Lend First 1500 to some other person ( for 300 days) Second 1500 for 200 Days and Thrid 1500 for 100 Days.


<table border="0" cellpadding="0" cellspacing="0" width="346"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:48pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="64">No.</td> <td class="xl65" style="width:99pt;font-size:11.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="132">Amount Received</td> <td class="xl64" style="width:63pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="84">Interest</td> <td class="xl64" style="width:50pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="66">Tenure</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">1</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">$8,500.00 </td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">1500</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">100 Days</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">2</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">$8,500.00 </td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">1500</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none">100 Days</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">3</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">$8,500.00 </td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">1500</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none">100 Days</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">
</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">
</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none">4500</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">
</td> </tr> </tbody></table>
any futher inputs please? I am sorry if my question is not properly framed

Thank you once again

 

[zaska]

any further inputs plz?

 

[Don777777]

I'm sorry. I thought I understood what you wanted, but obviously I do not. Maybe someone else can help.