need excel formula to calculate interest on no days left...........

[Malleshg24]

Hi Team,

Attached excel sheet , I want to calculate interest on outstanding amount.
rate of interest is 18% want to apply on no of days delayed.

till date of each month cut off dates

I have filled amount received with payments dates

₹ 2,200

14.06.22

₹ 2,200

14.06.22

₹ 2,200

14.06.22

₹ 2,200

=MAX(0, OFFSET(Interest!$G$2, 12-CEILING.MATH(AI2/2200), 0)) ------------------ someone suggested this formula , not sure how to apply it
any other formula most welcome

Month	From	To	Days	Rate of Interest	Interest Amount	Cumulative Interest Amount
Apr-23	11-04-2023	24-02-2024	319	18%	₹ 346	₹ 1,968
May-23	11-05-2023	24-02-2024	289	18%	₹ 314	₹ 1,622
Jun-23	11-06-2023	24-02-2024	258	18%	₹ 280	₹ 1,308
Jul-23	11-07-2023	24-02-2024	228	18%	₹ 247	₹ 1,029
Aug-23	11-08-2023	24-02-2024	197	18%	₹ 214	₹ 781
Sep-23	11-09-2023	24-02-2024	166	18%	₹ 180	₹ 567
Oct-23	11-10-2023	24-02-2024	136	18%	₹ 148	₹ 387
Nov-23	11-11-2023	24-02-2024	105	18%	₹ 114	₹ 240
Dec-23	11-12-2023	24-02-2024	75	18%	₹ 81	₹ 126
Jan-24	11-01-2024	24-02-2024	44	18%	₹ 48	₹ 44
Feb-24	11-02-2024	24-02-2024	13	18%	₹ 14	-₹ 3
Mar-24	11-03-2024	24-02-2024	-16	18%	-₹ 17	-₹ 17

 

[Monty]

=Outstanding_Amount * Interest_Rate * (Number_of_Days_Delayed / 365)

 

[pecoflyer]

Cross post https://www.mrexcel.com/board/threa...umulative-interest-on-no-of-days-etc.1254518/

 

[vletm]

Malleshg24​

Forum Rules are for everyone:

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.