Calculate principal amount on daily interest

[aurelius33]

Greetings, I'm in search of a formula to calculate the principal amount on loans with daily interest. Currently, this is done inside of a program; which is far too rigid. By moving things into Excel, I gain a ton of flexibility. The payment is straightforward. I have reversed-engineered the math and figured out the program does not use a "banker year/month" for the calculations. Also, interest is accruing from the start date and continues until a payment is made. You'll see in the first row the interest is higher than expected using IPMT, etc. I figure the daily interest for the period with: balance*rate/365days

I can get close with PPMT but it still isn't dead-on. Also, simply subtracting the interest from the payment for each period working 99% of the time. Only issue is the oddball rounding that pops up here and there. Below is an example of data I am working with to recreate the output in Excel:

LoanScrubber.xlsx
	K	L	M	N	O	P	Q
2	Amt Financed:	$ 65,610.00				Weeks:	52
3	Interest Rate:	16.50%				Num Days / Year:	365
4	Loan Start Date:	23-Apr
5	Num of Pmts:	86
6
7		Date	Pmt Num	Pmt	Prin	Interest	Balance
8		4-May	1	872.93	546.68	326.25	65063.32
9		11-May	2	872.93	667.04	205.89	64396.28
10		18-May	3	872.93	669.16	203.77	63727.12
11		25-May	4	872.93	671.27	201.66	63055.85
Payments

When I subtract the interest from the payment for period 2, I end up with 667.05 and not 667.04.

 

[StephenCrump]

Welcome to the Forum!

You haven't shown any of the formulae you're using, but based on what you've said:

Interest in O9 should be ROUND(65063.32*0.165*7/365,2) = 205.89

hence principal in O10 will be 872.93 - 205.89 = 667.04.

Note that the Excel financial functions like IPMT and PPMT assume periods of equal length. Here, the repayments are 7 days apart, but the first repayment is 11 days after loan drawdown.

 

[Cubist]

I can't match the PMT amount. Based on the formula in D3, the answer is $872.62, but everything else matches.

Book1
	A	B	C	D	E	F	G	H
1	Amt Financed:	$ 65,610.00					Weeks:	52
2	Interest Rate:	16.50%					Num Days / Year:	365
3	Loan Start Date:	4/23/24		$872.62	<-Does not match
4	Num of Pmts:	86
5
6
7		Date	Pmt Num	Beg Balance	Pmt	Principal	Interest	Ending Balance
8		4/23/24						$ 65,610.00
9		5/4/24	1	$ 65,610.00	$872.93	$546.68	$ 326.25	$ 65,063.32
10		5/11/24	2	$ 65,063.32	$872.93	$667.04	$ 205.89	$ 64,396.28
11		5/18/24	3	$ 64,396.28	$872.93	$669.16	$ 203.77	$ 63,727.12
12		5/25/24	4	$ 63,727.12	$872.93	$671.27	$ 201.66	$ 63,055.85
13		6/1/24	5	$ 63,055.85	$872.93	$673.40	$ 199.53	$ 62,382.45
14		6/8/24	6	$ 62,382.45	$872.93	$675.53	$ 197.40	$ 61,706.92
15		6/15/24	7	$ 61,706.92	$872.93	$677.67	$ 195.26	$ 61,029.26
Sheet4

Cell Formulas
Range	Formula
D3	D3	=PMT(B2/365*7,B4,-B1,0)
B8	B8	=B3
H8	H8	=B1
C9:C94	C9	=SEQUENCE(B4)
F9:F15	F9	=E9-G9
G9:G15	G9	=$B$2/365*H8*(B9-B8)
H9:H15	H9	=D9-F9
B10:B15	B10	=B9+7
D9:D15	D9	=H8
Dynamic array formulas.

 

[StephenCrump]

I can't match the PMT amount. Based on the formula in D3, the answer is $872.62,

The quoted repayment is 827.93, which (coincidentally?) is PMT(B2/52,B4,-B1).

Therefore there is a small inconsistency between PMT, which uses interest of 16.5%/52 per week, and the calculated interest amounts which use interest of 16.5%*7/365 per week.

If you calculate interest as 16.5%*7/365 per week, and allow correctly for the 11 day first payment period, the level repayment is:

PMT(B2*7/365,B4,-B1*(1+B2*(B9-B8)/H2),,1) = 874.19.

On this basis, the amortisation schedule (when payments and interest are rounded to the cent) is $874.19 for 85 payments, and $874.14 for the 86th and final.

 

[roscoe]

Interesting that I just found this as I had this exact scenario (I think) pop up today. We bought a car yesterday and I could not for the life of me reconcile his payment with the principal & rate.

Turned out that he made the first payment 45 days after the loan initiation date instead of 30 as most program and calculations assume. So I calculated the interest charged for half of a month (principal x APR / 24) and added that result to the starting principal. Then the payment calculations came out to be exactly correct.

 

[aurelius33]

Thanks to all who jumped in. Glad someone randomly found what they needed. I actually have an old account of about a decade (scrounge33). 99% of the time, if I keep combing the forums, I find what I'm looking for. And a bunch of things I didn't know I was looking for, lol. So, I have never needed to post anything. I made a new account as I don't use that email much anymore and didn't feel like waiting for a response from the team.