Snasui Excel Forum

Hi there,

I needs your help how to calculate interest remaining for loan paid off.
for example i have a loan schedule.
assume client comes to pay off on 07 oct 2022 so amount principal would be as K3 and amount interest would be k4 (sum all left month he/she not yet paid interest).i need to do manual but how can i write formula without manual calculation
is it possible if i type pay off date in J3(Green color cell) it would pop up value in yellow cells say k3 and k4

i attach a template for your reference

tks

Please try with these formulas,

K2 =MATCH(MONTH(J3),MONTH(INDEX(RIGHT(B2:B25,10),0)),0)

K3 =OFFSET(H1,K2-1,0)

K4 =SUM(OFFSET(E2,K2,0,12-K2+1))

it return #N/A .the prone issue ច័ន្ទ 11-07-2022 is from system format as ddd dd-mm-yyyy

Please attach file for see and make it easy for members solve this problem.

tks

Please check the attachment

I guess it is up to your regional setting in your machine. In my machine it shows the correct result. Please see the picture below.

IT set regional setting mm-dd-yy but in this schedule when I right function and extract date it give wrong dd-mm-yy .how to adjust formula without set regional setting because IT department don't allow.
Thanks

You can try to change formula on K2 to:

=MATCH(MONTH(J3),INDEX(--MID(RIGHT(B2:B25,10),4,2),0),0)

Thank you so much .now it is working fine