Interest on increasing capital amount, not compounded

B

Bizzybee

New Member
Joined
Feb 22, 2017
Messages
10
I am hoping someone can help. I have spent all afternoon trying to figure this one out, to no avail.

Basically, I have a principal amount which stays the same and recurrs daily, say £10 (see table below).

I want to add interest on that at 8% simple annual interest from the date it is incurred to a specified end date.

I then want to do the same for the next £10 that are incurred on the next day.

The end date is always the same. So each day a principal amount is incurred, but it has one day less interest on it that the amount the day before.

I would like a formula which will work it all out in one or two (or three) steps. The data I have covers many years and I have baout 3,000 sets to work out. So I can't be doing it all individually as below.

Can anyone help?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Principal[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Annual Simple
interest rate[/TD]
[TD]Daily interest[/TD]
[TD]No of days[/TD]
[TD]Interest total[/TD]
[/TR]
[TR]
[TD]£10[/TD]
[TD]1 Jan 2015[/TD]
[TD]31 Dec 2015[/TD]
[TD]8%[/TD]
[TD]0.002191781[/TD]
[TD]364[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]0.797808219[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]£10[/TD]
[TD]2 Jan 2015[/TD]
[TD]31 Dec 2015[/TD]
[TD]8%[/TD]
[TD]0.002191781[/TD]
[TD]363[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]0.795616438[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]£10[/TD]
[TD]3Jan 2015[/TD]
[TD]31 Dec 2015[/TD]
[TD]8%[/TD]
[TD]0.002191781[/TD]
[TD]362[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]0.793424658[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]£10[/TD]
[TD]4 Jan 2015[/TD]
[TD]31 Dec 2015[/TD]
[TD]8%[/TD]
[TD]0.002191781[/TD]
[TD]361[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]0.791232877[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]£10[/TD]
[TD]5 Jan 2015[/TD]
[TD]31 Dec 2015[/TD]
[TD]8%[/TD]
[TD]0.002191781[/TD]
[TD]360[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]0.789041096[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Thank you, but the issue is yhat I dont have the daya in a table. I would have to make 3000 tables otherwise. The table above was just for illustration.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Bizzybee said:
Thank you, but the issue is yhat I dont have the daya in a table. I would have to make 3000 tables otherwise. The table above was just for illustration.
Click to expand...

Is your data sorted by client and if yes, are there id's for each client?
 
Upvote 0
Hi Bizzybee,
give this table a look

[TABLE="class: grid"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD]time[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD]payments[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]10*,08*3/365[/TD]
[TD]10*,08*2/365[/TD]
[TD="align: center"]10*,08*1/365[/TD]
[TD]10*,08*0/365[/TD]
[TD]interest formula[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]0,006575342[/TD]
[TD="align: center"]0,004359562[/TD]
[TD="align: center"]0,002191781[/TD]
[TD="align: center"]0[/TD]
[TD]interest[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]10*0,08/365[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"](3+2+1+0)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3/2*4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Combin(4,2)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]10*0,08/365[/TD]
[TD="align: center"]*[/TD]
[TD]#fulldays/2*(#fulldays+1)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]example 5 days[/TD]
[TD][/TD]
[TD]4 full days + present day (without interest)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]10*,08/365*(4/2*5)[/TD]
[TD][/TD]
[TD]0,021917808[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Foglio4​

the compact formula is:

=10*0.8/365*combin(fulldays+1, 2)

Hope this helps and you like it. Have a good weekend
 
Last edited:
Upvote 0
in compact formula there was a typo
=10*0.8/365*combin(fulldays+1, 2)
the amended one is
=10*0.08/365*combin(fulldays+1, 2)

there is an issue with leap yers but the workaround is to divide by 366 this way

=10*0.08/366*combin(fulldaysIN366+1, 2) for periods included in leap years
and former formula
=10*0.08/365*combin(fulldaysIN365+1, 2) for periods included in regular years

Have a nice sunday
 
Upvote 0

Excel 2010
ABCDE
1Amount$10
2Rate8%0.000219per day
3Number50
4Interest$48.44Total$548.44
4a
Cell Formulas
RangeFormula
C2=B2/365
B4=SUMPRODUCT(B1*ROW(INDIRECT("A1:A"&B3)),B3+1-ROW(INDIRECT("A1:A"&B3)))*C2
D4=B1*B3+B4


50 days @ 10 and total interest


The interest on a series can be calculated with SumProduct.
Try the concept that I prepared with a small example and review it with Formula Evaluation.
Edit as required for your requirements.
 
Upvote 0
This may be closer to the calculation that you require.
What result did you calculate?


Excel 2010
ABCD
1Amount$10
2Rate8%0.000219per day
3Number364
4Interest$145.60Total$3,785.60
4a
Cell Formulas
RangeFormula
C2=B2/365
B4=SUMPRODUCT(B1*ROW(INDIRECT("A1:A"&B3)))*B2/365
D4=B1*B3+B4
 
Upvote 0
You do not have to use the Named Cells; you can just reference the relevant cells.

The image did not post correctly so I deleted it.

The formula is

=(Num+1) / 2*Num*Prin*Int / 365 or =(B3+1) / 2*B3*B1*B2/ 365 compared to

the SumProduct version

=SUMPRODUCT(Prin*ROW(INDIRECT("A1:A"&Num)))*Int/365


Excel 2010
ABCD
1Amount$10
2Rate8%0.000219per day
3Number364
4Interest$145.60$145.60$145.60
5Total$3,785.60
4a
Cell Formulas
RangeFormula
B4=SUMPRODUCT(Prin*ROW(INDIRECT("A1:A"&Num)))*Int/365
B5=Prin*Num+B4
C4=(Num+1) / 2*Num*Prin*Int / 365
D4=(B3+1) / 2*B3*B1*B2/ 365
Named Ranges
NameRefers ToCells
Int='4a'!$B$2
Num='4a'!$B$3
Prin='4a'!$B$1
 
Last edited:
Upvote 0
No feedback from Bizzybee.

Try whichever approach you prefer. It is not necessary to use named ranges.
I would not use the formula with Indirect.
I included B___P's suggestion (Combin)


Excel 2010
ABCDE
1Amount10
2Rate8%0.000219per day
3Number364
4Interest145.60145.60145.60
5Total$3,785.60145.60
6
4a
Cell Formulas
RangeFormula
B5=Prin*Num+C4
C4=(Num+1)/2*Num*Prin*Int_Day
D4=(B3+1)/2*B3*B1*B2/365
E4=10*Int_Day*COMBIN(Num+1, 2)
E5=SUMPRODUCT($B$1*ROW($A$1:INDEX($A:$A,$B3)))*C2
Named Ranges
NameRefers ToCells
Int='4a'!$B$2
Int_Day='4a'!$C$2
Num='4a'!$B$3
Prin='4a'!$B$1
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Simple interest rate calculation
Replies
1
Views
244
Joe4
Joe4
V
  • Question Question
Cumulative Deposits in Indian Banks and TDS thereon
Replies
1
Views
134
vsuram
V
A
  • Solved
Calculate principal amount on daily interest
Replies
5
Views
856
aurelius33
A
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
112
aurelius33
A
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
159
ellison
E

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top