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?

Principal	Start Date	End Date	Annual Simple interest rate	Daily interest	No of days	Interest total
£10	1 Jan 2015	31 Dec 2015	8%	0.002191781	364	0.797808219 <tbody> </tbody>
£10	2 Jan 2015	31 Dec 2015	8%	0.002191781	363	0.795616438 <tbody> </tbody>
£10	3Jan 2015	31 Dec 2015	8%	0.002191781	362	0.793424658 <tbody> </tbody>
£10	4 Jan 2015	31 Dec 2015	8%	0.002191781	361	0.791232877 <tbody> </tbody>
£10	5 Jan 2015	31 Dec 2015	8%	0.002191781	360	0.789041096 <tbody> </tbody>

<tbody>
</tbody>

 

[Bizzybee]

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.

 

[Eric W]

Have you tried the closed-form formula from post #9?

 

[dreid1011]

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.

Is your data sorted by client and if yes, are there id's for each client?

 

[B___P]

Hi Bizzybee,
give this table a look

	A	B	C	D	E	F
1	3	2	1	0	time
2	10	10	10	10	payments
3
4	10*,08*3/365	10*,08*2/365	10*,08*1/365	10*,08*0/365	interest formula
5
6	0,006575342	0,004359562	0,002191781	0	interest
7
8	10*0,08/365	*	(3+2+1+0)
9			=
10			3/2*4
11			=
12			Combin(4,2)
13
14	10*0,08/365	*	#fulldays/2*(#fulldays+1)
15
16	example 5 days		4 full days + present day (without interest)
17
18	10*,08/365*(4/2*5)		0,021917808
19
20

<tbody>
</tbody>

Foglio4​

the compact formula is:

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

Hope this helps and you like it. Have a good weekend

 

[B___P]

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

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Amount	$10
2	Rate	8%	0.000219	per day
3	Number	50
4	Interest	$48.44	Total	$548.44
4a

Cell Formulas
Range	Formula
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.

 

[Dave Patton]

This may be closer to the calculation that you require.
What result did you calculate?

Excel 2010
	A	B	C	D
1	Amount	$10
2	Rate	8%	0.000219	per day
3	Number	364
4	Interest	$145.60	Total	$3,785.60
4a

Cell Formulas
Range	Formula
C2	=B2/365
B4	=SUMPRODUCT(B1*ROW(INDIRECT("A1:A"&B3)))*B2/365
D4	=B1*B3+B4

 

[Dave Patton]

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
	A	B	C	D
1	Amount	$10
2	Rate	8%	0.000219	per day
3	Number	364
4	Interest	$145.60	$145.60	$145.60
5	Total	$3,785.60
4a

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
Int	='4a'!$B$2
Num	='4a'!$B$3
Prin	='4a'!$B$1

 

[Dave Patton]

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
	A	B	C	D	E
1	Amount	10
2	Rate	8%	0.000219	per day
3	Number	364
4	Interest		145.60	145.60	145.60
5	Total	$3,785.60			145.60
6
4a

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
Int	='4a'!$B$2
Int_Day	='4a'!$C$2
Num	='4a'!$B$3
Prin	='4a'!$B$1