Stumps

[stumps]

Hi everyone, is it possible to get a formula to work out the following :

The data which I capture is the date in column A and amount in column B, I then manually work out the days and interest amount, am not always sure that my calculations are correct :

Is there a formula that can work out the days in column C and Interest Amount in column D

Thanks guys

	A	B	C	D
1	Date	Amount	Days	Interest	​	​
2				@ 5%
3	15/02/2016​ ​	200,000.00	0	0.00
4	17/02/2016	95,667.46	2	54.79
5	02/03/2016	100,000.00	13	526.63
6	03/03/2016	130,000.00	4	217.12
7	15/03/2016	200,000.00	11	2,556.32

<tbody>
</tbody>

 

[footoo]

In C4 and drag down :
=A4-A3

In D4 and drag down :
=ROUND(B$3*0.05/365*C4,2)

 

[stumps]

Apologies, there are numerous entries in-between the Loan date entries, the interest is calculated on the
loan balance plus the interest(accumulates) - so the spreadsheet looks like this :

	A	​	B	C	D	E
1	Date	Description	Amount	Days	Interest
2					@ 5%
3	2016-02-15	Loan	100,000.00	0	0.00
4	2016-02-19	Build Costs	24,500.00
5	2016-05-22	Build Costs	12,600.00
6	2016-07-15	Loan	200,000.00	150	2,054.79
7	2016-08-12	Build Costs	7,459.00
8	2016-07-15	Build Costs	5,600.00
9	2016-10-02	Build Costs	14,600.00
10	2016-10-12	Loan	100,000.00	89	3,682.59

<tbody>
</tbody>

 

[footoo]

Need to add a column : Enter "Loan Total" in F1

Enter these formulas and drag down :

D4 : =IF(B3="Loan",A4-A3,D3+A4-A3)

E4 : =IF(B4="Loan",(F3+SUM(E$3:E3))*0.05/365*D4,0)

F4 : =IF(B4="Loan",F3+C4,F3)

(Note : In your sample data the first Days number should be 151)

 

[stumps]

Hey, many thanks for the reply and answer - I will do as you say and check it out - I cannot believe all this, good stuff - thanks once again

 

[footoo]

I forgot to mention to enter 100,000 in F3

 

[stumps]

Hey footoo, thanks very much, the sums work out 100% - would you have a formula to insert into column G , it would be great if this column could have a running Total that Includes the Loan Amount and the Interest.

	A	B	C	D	E	F	G​
	Date		Amount	Days	Interest	Total Excl. Int.	Total Incl. Int
1	2016-02-15	Loan	100,000.00	0	0.00	100,000.00
2	2016-02-19	Build.........	24,500.00	4	0.00	100,000.00
3	2016-07-15	Loan	200,000.00	151	2,068.49	300,000.00
4	2016-08-12	Build Costs	7,459.00	28	0.00	300,000.00
5	2016-10-12	Loan	100,000.00	89	3,682.75	400,000.00
6	2016-12-17	Build Costs	14,600.00	66	0.00	400,000.00
7	2016-12-30	Loan	900,000.00	79	4,391.01	1,300,000.00
8	2017-01-18	Build Costs	28,679.00	19	0.00	1,300,000.00
9	2017-05-08	Build Costs	27,500.00	129	0.00	1,300,000.00
10	2017-12-23	Loan	900,000.00	358	64,250.81	2,200,000.00

<tbody>
</tbody>

 

[stumps]

Many thanks Footoo, checked it out, all working 100% - Would it be possible to have another column with a running Total that Includes the Interest?
Many thanks

 

[footoo]

In G2 : =F2+SUM(E$2:E2)