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

M

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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

M
  • Solved
Formula Help.
Replies
3
Views
63
Fluff
Fluff
B
  • Question
countifs and remove data completed
2
Replies
10
Views
390
MuchToLearn
M
Z
  • Question
Formula to calculate unique number of days falls under a particular month.
Replies
4
Views
58
Zee996
Z
AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
271
AOB
AOB
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
841
Dave Patton
D

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

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