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.
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)) |