Any formula for calculating loan using Effective interest rate without compounding?

[kiatkiat]

Hi everyone,

lets say i take a loan from a bank, the loan amount is 3,232,400, flat rate 3.18%, effective interest rate is 4.6981266%, 3 months tenor.

if i use the flat rate to compute the loan interest amount it is 102,790 per month (3,232,400 x 3.18%),

if i use the effective interest rate to compute the loan interest amount it is 151,862 on the first month (3,232,400 x 4.6981266%),
in the second month, the effective interest amount is computed using the outstanding principal loan applied the effective interest rate which amounts to 103,547 (2,204,005 (outstanding loan in the 2nd month) x 4.6981266%).

is there a quick formula to calculate the second and third month of the effective interest rate on cell E14 - 103,547 without linking to the outstanding loan? mini sheet below, thanks.

Book1.xlsx
	A	B	C	D	E	F	G
1
2	Loan amount	3,232,400	Input
3	Flat rate	3.18%	Input
4	Effective interest	4.6981266%
5	Tenor (mth)	3	Input
6	Purchase Date	09-09-2021	input
7	Bill Date	18	input
8	Due Date	28	input
9	Installment	1,180,257	/month
10
11	Installment No	Baseline Date	Due Date	Principal	Interest	OutstandingPrincipal
12						3,232,400
13	1	09-09-2021	28-09-2021	1,028,395	151,862	2,204,005
14	2	29-09-2021	28-10-2021	1,076,710	103,547	1,127,295
15	3	29-10-2021	28-11-2021	1,127,295	52,962	0
16	4			0		0
17	5			0		0
18	6			0		0
19	7			0		0
20	8			0		0
21	9			0		0
22	10			0		0
23	11			0		0
24	12			0		0
25	Total			3,232,400	308,371
26
27
28		date	month	year
29		28	9	2021
30		28	9	2021
31
32
InstSchedule

Cell Formulas
Range	Formula
B4	B4	=RATE(B5, (1+B5*B3)/B5, -1)
B9	B9	=ROUND((B2+(B2*B3*B5))/B5,0)
F12	F12	=B2
B13	B13	=B6
C13	C13	=DATE(D30,C30,B30)
D13:D24	D13	=IF(A13<$B$5,$B$9-E13, IF(A13=$B$5,F12,0))
E13	E13	=ROUND($B$4*F12,0)
F13:F24	F13	=F12-D13
B14:B24	B14	=IF(A14<=$B$5,C13+1, "")
C14:C24	C14	=IF(A14<=$B$5,EDATE($C$13,A13), "")
E14:E24	E14	=IF(A14<=$B$5,ROUND($B$4*F13,0), "")
D25:E25	D25	=SUM(D13:D24)
B29	B29	=B8
B30	B30	=B29
D30	D30	=IF(C29>12,YEAR(B6)+1,YEAR(B6))

 

[StephenCrump]

Welcome to the Forum!

You could use the IPMT() function.

Your highlighted cell, for example: =IPMT(B$4,2,B$5,-B$2) = $103,546.96 (where 2 indicates the 2nd interest payment)

 

[kiatkiat]

hi stephen, thanks for your response, I believe this formula is assuming the interest payment is the same throughout the period?
in this case, the loan installment is the same throughout the period and taking into account of the effective interest rate, the interest loan will be different in the other periods as well as the principal loan, however if you add up both the principal and interest amount in every period, it is the same in cell B9, where they take the flat rate to compute the monthly installment,

hope to hear from you soon, thanks,

 

[StephenCrump]

I believe this formula is assuming the interest payment is the same throughout the period?

No, it's replicating your amortisation schedule, where the interest component reduces over time.

Try: =IPMT(B$4,A13,B$5,-B$2), which will replicate the result in your cell E13 = $151,862.14.

Copy down two more rows, and you should replicate your results in cells E14:E15?