Interest on increasing capital amount, not compounded

[Bizzybee]

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]

 

[dreid1011]

What should the formula be calculating? The interest total for each row?

If that is the case, then assuming data starts in A2, this will calculate the total interest for each row:

=((D2/365)*A2)*F2

 

[Bizzybee]

I would like to know the total interest, i.e. the sum of of the 7th column

 

[dreid1011]

I would like to know the total interest, i.e. the sum of of the 7th column

You cant to add all the totals together? Where are you putting the sum? Can you provide a small sample of the expected result?

 

[Bizzybee]

Sorry, I wasn't clear. I need one formula that will calculate the entire interest for a period in which the principal amount increases daily by the same amount.

 

[Bizzybee]

Data:
Start Date
End Date
Principal amount which accrues every day
Interest rate

Output:
Interest accrued in period

 

[Bizzybee]

To give you some context:

Someone borrows £10 off me every day for 1 year. I charge simple interest (8% per annum in other words 8%/365 per day) on that £10. At the end of the year, I want to know how much interest the debtor needs to pay me in addition to the principal (which is 365 * £10 = £3650)

 

[Bizzybee]

So far I have got to this in my thinking:

P = principal
i = P * 0.08/365
d = 365 (being the difference between the start and end date)
T = total interest for period

T = (P*i*d) + (2P*i *(d-1)) + (3P*i*(d-2)) + ((4P*i*(d-3)) etc

I can't type that out for data that spans 10 years! How can I make that into a shorter formula?

 

[Eric W]

Assuming my algebra is correct, your equation resolves to:

	A	B	C	D
Principal
Interest
Daily Interest
Days

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.076712[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.076712[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.000219[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet19​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=B1*B3*5+2*B1*B3*4+3*B1*B3*3+4*B1*B3*2+5*B1*B3*1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=B1*B3*COMBIN(B4+2,3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=B2/365[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

Try this:

=SUMPRODUCT(((D2:D6/365)*A2:A6)*F2:F6)

Where:
D2:D6 is the Annual Simple Interest column
A2:A6 is the Principle column
F2:F6 is the No of Days column

And then if you want to get the combined total of Principle and Interest, you can add do this:

=SUM(SUMPRODUCT(((D2:D6/365)*A2:A6)*F2:F6),A2:A6)