suggest excel formula to calculate cumulative interest on no of days etc ......

M

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
983
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

We maintain excel sheet for society's maintenance, we had to charge interest payment of 18%. for late payer.

=OFFSET(Interest!$G$2,12-CEILING.MATH(O2/2200),0) someone has initially used this formula, he calculated interest based on no of days left ,
he has created separate sheet named interest. used that formula on outstanding amount. please suggest correct and better way of doing this.

for every month we update sheet, at the end of year , we calculate interest on outstanding amount


Sr.
No.		Wing & Flat No.Name of the CustomerContact No.Maintenance Due Date From 01-04-2023 To 31-03-2024
(12 Months) (Due on 10th of every month)		Maintenance Applicable (2200 x 12 = 26400)Maintenance Amount Received (Apr 2023)Date Of Payment Received (Apr 2023)Maintenance Amount Received (May 2023Date Of Payment Received (May 2023)Maintenance Amount Received (Jun 2023)Date Of Payment Received (Jun 2023)Maintenance Amount Received (Jul 2023)Total OutstandingTotal Interest Payble
1A-101Mrs. Mugdha Prashant Shindexxxxxx01-04-202331-03-202426,400220013.07.23220013.07.23220013.07.23=26400-6600
2200​
2A-102Mrs. ****al Namdev Kale & Mr. Namdev Rangnath Kalexxxxxx01-04-202331-03-202426,40013.04.2313.04.2313.04.23
8800​
3A-103Ms. Puja Ashok Bendre alias Mrs. Puja Ganesh Nikam & Mr. Ganesh Ashok Nikamxxxxxx01-04-202331-03-202426,400220004.04.23220003.05.2305.07.23
4400​
15400​


No of days calculations

MonthFromToDaysRate of InterestInterest AmountCumulative Interest Amount
Apr-2211-04-202223-02-202468318%₹ 741₹ 6,708
May-2211-05-202223-02-202465318%₹ 708₹ 5,967
Jun-2211-06-202223-02-202462218%₹ 675₹ 5,259
Jul-2211-07-202223-02-202459218%₹ 642₹ 4,584
Aug-2211-08-202223-02-202456118%₹ 609₹ 3,942
Sep-2211-09-202223-02-202453018%₹ 575₹ 3,333
Oct-2211-10-202223-02-202450018%₹ 542₹ 2,758
Nov-2211-11-202223-02-202446918%₹ 509₹ 2,215
Dec-2211-12-202223-02-202443918%₹ 476₹ 1,707
Jan-2311-01-202323-02-202440818%₹ 443₹ 1,230
Feb-2311-02-202323-02-202437718%₹ 409₹ 788
Mar-2311-03-202323-02-202434918%₹ 379₹ 379
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: need excel formula to calculate interest on no days left...........
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Count of active employees between dates.
Replies
3
Views
667
Fluff
Fluff
Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
159
RobP
R
M
  • Solved
Formula Help.
Replies
3
Views
437
Fluff
Fluff
sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
530
sharky12345
sharky12345
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
361
Cubist
Cubist

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

Share this page

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