Stumps

S

stumps

New Member
Joined
Jan 26, 2018
Messages
22
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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Interest[/TD]
[TD]

​
[/TD]
[TD]

​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]@ 5%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]
15/02/2016​
​
[/TD]
[TD="align: center"]200,000.00[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]17/02/2016
[/TD]
[TD="align: center"]95,667.46
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]54.79[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]02/03/2016[/TD]
[TD="align: center"]100,000.00[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]526.63[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]03/03/2016
[/TD]
[TD="align: center"]130,000.00[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]217.12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]15/03/2016[/TD]
[TD="align: center"]200,000.00[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]2,556.32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
In C4 and drag down :
=A4-A3

In D4 and drag down :
=ROUND(B$3*0.05/365*C4,2)
 
Last edited:
Upvote 0
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 :

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]

​
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Interest[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]@ 5%[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2016-02-15[/TD]
[TD="align: center"]Loan[/TD]
[TD="align: center"]100,000.00[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2016-02-19[/TD]
[TD="align: center"]Build Costs[/TD]
[TD="align: center"]24,500.00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2016-05-22[/TD]
[TD="align: center"]Build Costs[/TD]
[TD="align: center"]12,600.00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]2016-07-15[/TD]
[TD="align: center"]Loan[/TD]
[TD="align: center"]200,000.00[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]2,054.79[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]2016-08-12[/TD]
[TD="align: center"]Build Costs[/TD]
[TD="align: center"]7,459.00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]2016-07-15[/TD]
[TD="align: center"]Build Costs[/TD]
[TD="align: center"]5,600.00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]2016-10-02[/TD]
[TD="align: center"]Build Costs[/TD]
[TD="align: center"]14,600.00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]2016-10-12[/TD]
[TD="align: center"]Loan[/TD]
[TD="align: center"]100,000.00[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]3,682.59[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD]
G​
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD] Date[/TD]
[TD]
[/TD]
[TD]Amount[/TD]
[TD]Days[/TD]
[TD]Interest[/TD]
[TD="align: center"]Total Excl. Int. [/TD]
[TD="align: center"]Total Incl. Int[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2016-02-15[/TD]
[TD]Loan[/TD]
[TD]100,000.00[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]100,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2016-02-19[/TD]
[TD]Build.........[/TD]
[TD] 24,500.00[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]100,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2016-07-15[/TD]
[TD]Loan[/TD]
[TD]200,000.00[/TD]
[TD="align: center"]151[/TD]
[TD="align: center"]2,068.49[/TD]
[TD="align: center"]300,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2016-08-12[/TD]
[TD]Build Costs[/TD]
[TD] 7,459.00[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]300,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2016-10-12[/TD]
[TD]Loan[/TD]
[TD]100,000.00[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]3,682.75[/TD]
[TD="align: center"]400,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2016-12-17[/TD]
[TD]Build Costs[/TD]
[TD] 14,600.00[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]400,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2016-12-30[/TD]
[TD]Loan[/TD]
[TD]900,000.00[/TD]
[TD="align: center"]79[/TD]
[TD="align: center"]4,391.01[/TD]
[TD="align: center"]1,300,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2017-01-18[/TD]
[TD]Build Costs[/TD]
[TD] 28,679.00[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]1,300,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2017-05-08[/TD]
[TD]Build Costs[/TD]
[TD]27,500.00[/TD]
[TD="align: center"]129[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]1,300,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2017-12-23[/TD]
[TD]Loan[/TD]
[TD]900,000.00[/TD]
[TD="align: center"]358[/TD]
[TD="align: center"]64,250.81[/TD]
[TD="align: center"]2,200,000.00[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Cumulative Deposits in Indian Banks and TDS thereon
Replies
1
Views
130
vsuram
V
M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
648
Peter_SSs
Peter_SSs
A
  • Solved
Calculate principal amount on daily interest
Replies
5
Views
797
aurelius33
A
E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
311
Fluff
Fluff
O
  • Solved
Find and match header in different location for the column based on sheet name
Replies
8
Views
125
Omar M
O

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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