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]

Re: Calculation Help in Excel

Bump

 

[zaska]

Re: Calculation Help in Excel

Can anyone Help me?

 

[noori]

Re: Calculation Help in Excel

It is 4500 for 300 days.
Hope answer the question.

 

[zaska]

Hi,

Yes it is 4500 in theory but in Practial situation the lender is giving only 8500 taking the 1500 on the first day itself...so he has 1500 to lend again for 300 days...

I want to calculate the total return he can generate in 300 days with 15% interest ( for 100 days)

I am sorry if i was not able to communicate the question properly.

The article which i have read says that the effective Interest rate works out to 120 % p.a

but it didn't show the calculations involved.

Looking for any financial analyst to help me in excel

Thank you

 

[zaska]

Can anyone help me..?

Here the flat rate system is used.

 

[Don777777]

<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=246><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3072" width=86><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3413" width=96><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Received</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=86> $ 8,500.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=96> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Paid Back</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> $ 10,000.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Return</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>17.65%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>=B2/B1-1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Annually</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>3.650000 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>=365/100</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>APR</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>64.4118%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>=B3*B4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>APY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>80.9760%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>=(1+B3)^B4-1</TD></TR></TBODY></TABLE>

I don't know if this helps, but a $10,000 loan paid back in 100 days with $1,500 in interest would have an Annual Perentage Rate of 64.41% and an Annual Percentage Yield of 80.98%

 

[zaska]

<table style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border="0" cellpadding="0" cellspacing="0" width="246"><colgroup><col style="WIDTH: 48pt" width="64"><col style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3072" width="86"><col style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3413" width="96"></colgroup><tbody><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl63" height="19" width="64">Received</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl64" width="86"> $ 8,500.00 </td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class="xl65" width="96">
</td></tr><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl63" height="19">Paid Back</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl64"> $ 10,000.00 </td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl65">
</td></tr><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl63" height="19">Return</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl66">17.65%</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl69">=B2/B1-1</td></tr><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl63" height="19">Annually</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl67">3.650000 </td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl69">=365/100</td></tr><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl63" height="19">APR</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl68">64.4118%</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl70">=B3*B4</td></tr><tr style="HEIGHT: 14.4pt" height="19"><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl63" height="19">APY</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl68">80.9760%</td><td style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class="xl70">=(1+B3)^B4-1</td></tr></tbody></table>

I don't know if this helps, but a $10,000 loan paid back in 100 days with $1,500 in interest would have an Annual Perentage Rate of 64.41% and an Annual Percentage Yield of 80.98%

Hello,
Thank you it helped me a bit , but the article mentioned that APY is 120 %.

In the current situation money lender is deducting the interest in advance on the first day itself. So he has Rs.1500 /- on hand to lend the same again @ 15% ( for 100 days). He is also getting Rs 100 /- being repaid everyday.

The money lender is also able to Rotate Rs.10000 three times in a year ( 100 Days x 3 ).

Can you provide any further inputs?

Thank you

 

[Don777777]

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 cellSpacing=0 cellPadding=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><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 height=19 width=264 colSpan=3>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>

 

[Don777777]

More information:
15% =16.17984 compound daily <TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=216><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #dce6f1; WIDTH: 163pt; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl76 height=21 width=216 colSpan=2>=((1+0.15/365)^(365))-1 = .1617984

</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 92pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=122><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 92pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=19 width=122>0.16179/365*100*10000 = $443.28 + $1,500 = $1,943.28 up front.

$10,000 - $1943.28 = $8,056.72 received

Paid back $10,000 after 100 days
</TD></TR></TBODY></TABLE>