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

kiatkiat

New Member
Joined
Mar 9, 2022
Messages
2
Office Version
  1. 365
  2. 2021
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
ABCDEFG
1
2Loan amount3,232,400Input
3Flat rate3.18%Input
4Effective interest4.6981266%
5Tenor (mth)3Input
6Purchase Date09-09-2021input
7Bill Date18input
8Due Date28input
9Installment1,180,257/month
10
11Installment NoBaseline DateDue DatePrincipalInterestOutstandingPrincipal
123,232,400
13109-09-202128-09-20211,028,395151,8622,204,005
14229-09-202128-10-20211,076,710103,5471,127,295
15329-10-202128-11-20211,127,29552,9620
164  0 0
175  0 0
186  0 0
197  0 0
208  0 0
219  0 0
2210  0 0
2311  0 0
2412  0 0
25Total3,232,400308,371
26
27
28datemonthyear
292892021
302892021
31
32
InstSchedule
Cell Formulas
RangeFormula
B4B4=RATE(B5, (1+B5*B3)/B5, -1)
B9B9=ROUND((B2+(B2*B3*B5))/B5,0)
F12F12=B2
B13B13=B6
C13C13=DATE(D30,C30,B30)
D13:D24D13=IF(A13<$B$5,$B$9-E13, IF(A13=$B$5,F12,0))
E13E13=ROUND($B$4*F12,0)
F13:F24F13=F12-D13
B14:B24B14=IF(A14<=$B$5,C13+1, "")
C14:C24C14=IF(A14<=$B$5,EDATE($C$13,A13), "")
E14:E24E14=IF(A14<=$B$5,ROUND($B$4*F13,0), "")
D25:E25D25=SUM(D13:D24)
B29B29=B8
B30B30=B29
D30D30=IF(C29>12,YEAR(B6)+1,YEAR(B6))
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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)
 
Upvote 0
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,
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,711
Messages
6,174,025
Members
452,542
Latest member
Bricklin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top