Excel formula for calculating interest due.

[Golfpro1286]

I am trying to calculate interest due on a Per-Diem basis when the interest rate changed during a given time period. For example, our institution sends out $100M on 05/20/2018 and charges an interest rate of WSJ Prime (adjusting daily) on a Per-Diem Basis. We receive a payoff request for 6/30/18 and need to calculate the interest due. At the time of disbursement interest rate was 4.75%, but on 6/14/18 Prime changed to 5.00%. So from 5/20/18-6/14/18 the $100M would be earning interest at 4.75% and from 6/15/18-6/30/18 it earned interest at rate of 5.00%. Below is the spreadsheet we use to track this.

[TABLE="width: 0"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]DISB DATE
[/TD]
[TD]Loan #
[/TD]
[TD]NAME
[/TD]
[TD]WIRE AMOUNT
[/TD]
[TD]PAYOFF DATE
[/TD]
[TD]INTEREST DUE
[/TD]
[TD]# DAYS CLS/PUR
[/TD]
[/TR]
[TR]
[TD]5/20/2018
[/TD]
[TD]123456
[/TD]
[TD]TEST1
[/TD]
[TD]$100,000.00
[/TD]
[TD]6/30/2018
[/TD]
[TD]
[/TD]
[TD]41
[/TD]
[/TR]
</tbody>[/TABLE]

I have a table in a separate tab indicating dates of interest rate change:

[TABLE="width: 0"]
<tbody>[TR]
[TD]Effective Date
[/TD]
[TD]Rate
[/TD]
[/TR]
[TR]
[TD]9/27/2018
[/TD]
[TD]5.25%
[/TD]
[/TR]
[TR]
[TD]6/14/2018
[/TD]
[TD]5.00%
[/TD]
[/TR]
[TR]
[TD]3/22/2018
[/TD]
[TD]4.75%
[/TD]
[/TR]
[TR]
[TD]12/14/2017
[/TD]
[TD]4.50%
[/TD]
[/TR]
[TR]
[TD]6/15/2017
[/TD]
[TD]4.25%
[/TD]
[/TR]
[TR]
[TD]3/16/2017
[/TD]
[TD]4.00%
[/TD]
[/TR]
[TR]
[TD]12/15/2016
[/TD]
[TD]3.75%
[/TD]
[/TR]
[TR]
[TD]12/17/2015
[/TD]
[TD]3.50%
[/TD]
[/TR]
[TR]
[TD]12/16/2008
[/TD]
[TD]3.25%
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any help!

 

[Eric W]

You pretty much have to calculate the interest on a daily basis and add. Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Effective Date	Rate	Date	Balance	Interest	Rate
6/22/2018
6/29/2018

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]DISB DATE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Loan #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]WIRE AMOUNT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PAYOFF DATE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]INTEREST DUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]# DAYS CLS/PUR[/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5/20/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]123456[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]TEST1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$100,000.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6/30/2018[/TD]
[TD="align: right"]$546.66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/16/2008[/TD]
[TD="align: right"]3.25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/20/2018[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/17/2015[/TD]
[TD="align: right"]3.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/21/2018[/TD]
[TD="align: right"]$100,013.01[/TD]
[TD="align: right"]$13.01[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/15/2016[/TD]
[TD="align: right"]3.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/22/2018[/TD]
[TD="align: right"]$100,026.03[/TD]
[TD="align: right"]$13.02[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/16/2017[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/23/2018[/TD]
[TD="align: right"]$100,039.05[/TD]
[TD="align: right"]$13.02[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/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]

[TD="align: right"][/TD]
[TD="align: right"]6/15/2017[/TD]
[TD="align: right"]4.25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/24/2018[/TD]
[TD="align: right"]$100,052.06[/TD]
[TD="align: right"]$13.02[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/14/2017[/TD]
[TD="align: right"]4.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/25/2018[/TD]
[TD="align: right"]$100,065.09[/TD]
[TD="align: right"]$13.02[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/22/2018[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/26/2018[/TD]
[TD="align: right"]$100,078.11[/TD]
[TD="align: right"]$13.02[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/14/2018[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/27/2018[/TD]
[TD="align: right"]$100,091.13[/TD]
[TD="align: right"]$13.02[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9/27/2018[/TD]
[TD="align: right"]5.25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/28/2018[/TD]
[TD="align: right"]$100,104.16[/TD]
[TD="align: right"]$13.03[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/29/2018[/TD]
[TD="align: right"]$100,117.18[/TD]
[TD="align: right"]$13.03[/TD]
[TD="align: right"]4.75%[/TD]

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

[TD="align: center"]12[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/30/2018[/TD]
[TD="align: right"]$100,130.21[/TD]
[TD="align: right"]$13.03[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/31/2018[/TD]
[TD="align: right"]$100,143.24[/TD]
[TD="align: right"]$13.03[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]$100,156.28[/TD]
[TD="align: right"]$13.03[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/2/2018[/TD]
[TD="align: right"]$100,169.31[/TD]
[TD="align: right"]$13.03[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/3/2018[/TD]
[TD="align: right"]$100,182.35[/TD]
[TD="align: right"]$13.04[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/4/2018[/TD]
[TD="align: right"]$100,195.38[/TD]
[TD="align: right"]$13.04[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/5/2018[/TD]
[TD="align: right"]$100,208.42[/TD]
[TD="align: right"]$13.04[/TD]
[TD="align: right"]4.75%[/TD]

[TD="align: center"]19[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/6/2018[/TD]
[TD="align: right"]$100,221.46[/TD]
[TD="align: right"]$13.04[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/7/2018[/TD]
[TD="align: right"]$100,234.51[/TD]
[TD="align: right"]$13.04[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/8/2018[/TD]
[TD="align: right"]$100,247.55[/TD]
[TD="align: right"]$13.04[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/9/2018[/TD]
[TD="align: right"]$100,260.60[/TD]
[TD="align: right"]$13.05[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/10/2018[/TD]
[TD="align: right"]$100,273.64[/TD]
[TD="align: right"]$13.05[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/11/2018[/TD]
[TD="align: right"]$100,286.69[/TD]
[TD="align: right"]$13.05[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/12/2018[/TD]
[TD="align: right"]$100,299.74[/TD]
[TD="align: right"]$13.05[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/13/2018[/TD]
[TD="align: right"]$100,312.80[/TD]
[TD="align: right"]$13.05[/TD]
[TD="align: right"]4.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/14/2018[/TD]
[TD="align: right"]$100,326.54[/TD]
[TD="align: right"]$13.74[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/15/2018[/TD]
[TD="align: right"]$100,340.28[/TD]
[TD="align: right"]$13.74[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/16/2018[/TD]
[TD="align: right"]$100,354.03[/TD]
[TD="align: right"]$13.75[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/17/2018[/TD]
[TD="align: right"]$100,367.77[/TD]
[TD="align: right"]$13.75[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/18/2018[/TD]
[TD="align: right"]$100,381.52[/TD]
[TD="align: right"]$13.75[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/19/2018[/TD]
[TD="align: right"]$100,395.27[/TD]
[TD="align: right"]$13.75[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/20/2018[/TD]
[TD="align: right"]$100,409.03[/TD]
[TD="align: right"]$13.75[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/21/2018[/TD]
[TD="align: right"]$100,422.78[/TD]
[TD="align: right"]$13.75[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$100,436.54[/TD]
[TD="align: right"]$13.76[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/23/2018[/TD]
[TD="align: right"]$100,450.30[/TD]
[TD="align: right"]$13.76[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/24/2018[/TD]
[TD="align: right"]$100,464.06[/TD]
[TD="align: right"]$13.76[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/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]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/25/2018[/TD]
[TD="align: right"]$100,477.82[/TD]
[TD="align: right"]$13.76[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/26/2018[/TD]
[TD="align: right"]$100,491.58[/TD]
[TD="align: right"]$13.76[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/27/2018[/TD]
[TD="align: right"]$100,505.35[/TD]
[TD="align: right"]$13.77[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/28/2018[/TD]
[TD="align: right"]$100,519.12[/TD]
[TD="align: right"]$13.77[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$100,532.89[/TD]
[TD="align: right"]$13.77[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/30/2018[/TD]
[TD="align: right"]$100,546.66[/TD]
[TD="align: right"]$13.77[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=VLOOKUP(E2,L2:M43,2)-D2[/TD]
[/TR]
[TR]
[TH]L2[/TH]
[TD="align: left"]=A2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=D2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L3[/TH]
[TD="align: left"]=L2+1[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M3[/TH]
[TD="align: left"]=M2+N3[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N3[/TH]
[TD="align: left"]=M2*O3/365[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O3[/TH]
[TD="align: left"]=VLOOKUP(L3,$I$2:$J$10,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]





Starting with the date and balance in L2 and M2, the L3 formula just increments the date on a daily basis. The O3 formula looks up the rate from your table. Then the N3 formula calculates the daily interest, and the M3 adds it in. Copy the L3:O3 formulas down the columns as far as needed. Then the F2 formula calculates the interest from the last balance minus the original amount. (Note that I inverted your interest table for ease of use.)

I'm a little surprised you're using Excel for this though. There are some specialized software tools for this kind of thing, and if you're dealing with $100M loans, I think you'd want to get something designed for it.

 

[Golfpro1286]

It is a small group of one-time shorter term loans so they don't want to pay for, just need something to make sure our math is right. I will give this a shot and let you know if there are any issues.

Thanks!

 

[Golfpro1286]

How would I do this for multiple loans with different principal amounts?

 

[Eric W]

The formulas are based on A2, D2 and E2. Change those three cells, and make sure the L2:O3 formulas are copied down as far as needed, then you can do multiple loans. If you want to have multiple loans visible at once, I'd just add a new tab, then copy these formulas to the new tab and change the A2:E2 values. In theory, I could write a macro that calculates the total in F2 without the need for columns L:O, but you'd lose all the detail.

 

[jtakw]

Hi,

Here's another approach, if you have Multiple loans to evaluate, we can adjust the set up to suit:

Book1
	A	B	C	D	E	F	G
1	DISB DATE	Loan #	NAME	WIRE AMOUNT	PAYOFF DATE	INTEREST DUE	# DAYS CLS/PUR
2	5/20/2018	123456	TEST1	$100,000.00	6/30/2018	544.52	41
3
4
5	Effective Date	Rate
6	9/27/2018	5.25%	0.00
7	6/14/2018	5.00%	219.18
8	3/22/2018	4.75%	325.34
9	12/14/2017	4.50%	0.00
10	6/15/2017	4.25%	0.00
11	3/16/2017	4.00%	0.00
12	12/15/2016	3.75%	0.00
13	12/17/2015	3.50%	0.00
14	12/16/2008	3.25%	0.00
Sheet295

Cell Formulas
Range	Formula
F2	=SUM(C6:C14)
C6	=IF(OR(A6>E$2,A5),0,IF(AND(A$2E$2),(E$2-A6)*B6*D$2/365,(A5-A$2)*B6*D$2/365))

C6 formula copied down.