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.

A	B	C	D	E	F	G
DISB DATE	Loan #	NAME	WIRE AMOUNT	PAYOFF DATE	INTEREST DUE	# DAYS CLS/PUR
5/20/2018	123456	TEST1	$100,000.00	6/30/2018		41

<tbody>
</tbody>

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

Effective Date	Rate
9/27/2018	5.25%
6/14/2018	5.00%
3/22/2018	4.75%
12/14/2017	4.50%
6/15/2017	4.25%
3/16/2017	4.00%
12/15/2016	3.75%
12/17/2015	3.50%
12/16/2008	3.25%

<tbody>
</tbody>

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
1	DISB DATE	Loan #	NAME	WIRE AMOUNT	PAYOFF DATE	INTEREST DUE	# DAYS CLS/PUR		Effective Date	Rate		Date	Balance	Interest	Rate
2	5/20/2018	123456	TEST1	$100,000.00	6/30/2018	$546.66	41		12/16/2008	3.25%		5/20/2018	$100,000.00
3									12/17/2015	3.50%		5/21/2018	$100,013.01	$13.01	4.75%
4									12/15/2016	3.75%		5/22/2018	$100,026.03	$13.02	4.75%
5									3/16/2017	4.00%		5/23/2018	$100,039.05	$13.02	4.75%
6									6/15/2017	4.25%		5/24/2018	$100,052.06	$13.02	4.75%
7									12/14/2017	4.50%		5/25/2018	$100,065.09	$13.02	4.75%
8									3/22/2018	4.75%		5/26/2018	$100,078.11	$13.02	4.75%
9									6/14/2018	5.00%		5/27/2018	$100,091.13	$13.02	4.75%
10									9/27/2018	5.25%		5/28/2018	$100,104.16	$13.03	4.75%
11												5/29/2018	$100,117.18	$13.03	4.75%
12												5/30/2018	$100,130.21	$13.03	4.75%
13												5/31/2018	$100,143.24	$13.03	4.75%
14												6/1/2018	$100,156.28	$13.03	4.75%
15												6/2/2018	$100,169.31	$13.03	4.75%
16												6/3/2018	$100,182.35	$13.04	4.75%
17												6/4/2018	$100,195.38	$13.04	4.75%
18												6/5/2018	$100,208.42	$13.04	4.75%
19												6/6/2018	$100,221.46	$13.04	4.75%
20												6/7/2018	$100,234.51	$13.04	4.75%
21												6/8/2018	$100,247.55	$13.04	4.75%
22												6/9/2018	$100,260.60	$13.05	4.75%
23												6/10/2018	$100,273.64	$13.05	4.75%
24												6/11/2018	$100,286.69	$13.05	4.75%
25												6/12/2018	$100,299.74	$13.05	4.75%
26												6/13/2018	$100,312.80	$13.05	4.75%
27												6/14/2018	$100,326.54	$13.74	5.00%
28												6/15/2018	$100,340.28	$13.74	5.00%
29												6/16/2018	$100,354.03	$13.75	5.00%
30												6/17/2018	$100,367.77	$13.75	5.00%
31												6/18/2018	$100,381.52	$13.75	5.00%
32												6/19/2018	$100,395.27	$13.75	5.00%
33												6/20/2018	$100,409.03	$13.75	5.00%
34												6/21/2018	$100,422.78	$13.75	5.00%
35												6/22/2018	$100,436.54	$13.76	5.00%
36												6/23/2018	$100,450.30	$13.76	5.00%
37												6/24/2018	$100,464.06	$13.76	5.00%
38												6/25/2018	$100,477.82	$13.76	5.00%
39												6/26/2018	$100,491.58	$13.76	5.00%
40												6/27/2018	$100,505.35	$13.77	5.00%
41												6/28/2018	$100,519.12	$13.77	5.00%
42												6/29/2018	$100,532.89	$13.77	5.00%
43												6/30/2018	$100,546.66	$13.77	5.00%

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula F2 =VLOOKUP(E2,L2:M43,2)-D2 L2 =A2 M2 =D2 L3 =L2+1 M3 =M2+N3 N3 =M2*O3/365 O3 =VLOOKUP(L3,$I$2:$J$10,2) <tbody> </tbody>

<tbody>
</tbody>

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.