I would like to set up a worksheet to automatically calculate the total amount of accrued interest and late penalties on a delinquent customer.

Our policies are: 10% penalty on any amount due not paid by the first, plus 10% annual interest on any amount due.

I have several very delinquent clients who we are tracking amounts due in preparation of a coming lien sale. I'd like to have a simple worksheet that I can send to my directors that will automatically update - these individuals are making no payments.

I've attached a sample of what I'm thinking of. I've been tracking using the manual method on the second tab, but I wonder if there is a clean formula I can use to track int more automatically and cleanly, as in the first tab.

I am looking for the function or formulas that will get the right result in the yellow highlighted ares areas in the "A little Automated" worksheet.

Thanks!

Our policies are: 10% penalty on any amount due not paid by the first, plus 10% annual interest on any amount due.

I have several very delinquent clients who we are tracking amounts due in preparation of a coming lien sale. I'd like to have a simple worksheet that I can send to my directors that will automatically update - these individuals are making no payments.

I've attached a sample of what I'm thinking of. I've been tracking using the manual method on the second tab, but I wonder if there is a clean formula I can use to track int more automatically and cleanly, as in the first tab.

I am looking for the function or formulas that will get the right result in the yellow highlighted ares areas in the "A little Automated" worksheet.

Thanks!

- Amortization - calculating late fees and penalty interest
- Calculating Late Charges and Intrest
- Calculating late fees every month by date
- Accrued late fee calculation
- Calculating Intrest and Late Fees
- How to Avoid Circular Reference with Late Fee Calc. Problem
- Logical calculation of fees and currencies
- Monster Formula Help on Late Fees
- How do I calculate interest and deduct late fees based on date pa.
- Calculating interest with late fees
- If Statement for Recurring Interest and Late Fees
- Bond Accrued Interest (NOT Price) Calculation
- Accrued interest calculation
- Morgage schedule that accumulates interest and late fees
- Add late fee. Inserting text and numbers in sep clumns IF date entered is past 20th
- Bifurcate the Accrued Interest portion Financial YearWise 1st Apr XX To 31st Mar XX+1
- Calculate Revenue Volume Based On Current Date & Other Variables
- AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS!
- Calculating finance charges on simple interest
- Adding late fees automatically
- Calculate Opponents Record and Strength of Schedule
- Late fee charges...
- Calculate Elapsed Date and Time
- Problem of calculating years months and days

me to modify and put in actual payments received. I need to include the

actual dates of payment as most are not received when they are due I need to

calculate late payment fees and penalty interest on overdue amounts. If I

just make a seperate worksheet and enter the values for late interest and

fees on both sheets manually, then I have to calculate the late interest

myself which I cannot do. To add more confusion, I really need to calculate

interest on a daily basis so I can charge late interest based on the number

of days overdue and compounding each day. I am no expert when it comes to

devising formula solutions, but I am pretty good at copying and pasting (yes

that was an attempt at humour)

Here'e my example i'm working on now.

I have a customer that owes $118.00.

Each month late there is a $15.00 late fee

and then 2% intrest added on top of that.

This customer is 11 months late.

What i am having trouble with is getting a formula to addthe 2% intrest each month after the $15.00 charge is added each month.

My fomula adds the inital charge, then the late fees, then the intrest based off that.

Right now my formula reads

[initial balance plus late fees]*1.02^late months

but over 11 months that comes to $351.84

it should come up $333

My friend came up with a program answer, which doesn't help in the case of Excel

Sorry, but all I have is a program oriented answer.

;variables

n=11

i=.02

p=118

f=15

;loop equation

for n

p=(p+f)*(1+i)

;outputs

month 1 118

month 2 135.66

month 3 153.67

month 4 172.05

month 5 190.79

month 6 209.90

month 7 229.40

month 8 249.29

month 9 269.58

month 10 290.27

month 11 311.37 Note, his is a little off; month 1 = month 0, month 2 would actully equal 1 month late.

Every month after the 5-day grace period, a late fee of 1.5% is accessed every month until payment is received.

This is what I mean:

John owes: $100 (due on the 1/1/2008)

John pays the $100 on 4/1/2008)

- there is a 5-day grace period that occurred 1/1-1/5 and on 1/6 was technically late.

That makes John 3 months late...he owes 1.5% of 100 per month...

please help - you can reply here or email me at calvinwitcher@yahoo.com

variables â€ślate feeâ€ť and â€śintrestâ€ť.The folloing in the logic behind what Iâ€™m

trying to do. My goal is to do this with one formula ? I have been exploring

the financial formulas with absolutely no luck.....

Open Amount [$1,182.50] + (2% late fee) + (1.5% interest) = "Month 1 Amount"

Month 1 Amount + (2% of $1,182.50)) + (1.5% of Month 1 Amount) = "Month 2

Amount"

Month 2 Amount + (2% of $1,182.50) + (1.5% of Month 2 Amount) = "Month 3

Amount"

.....and so on for the x of months overdue...

any help would be much appreciatedâ€¦â€¦

Here'e my example i'm working on now.

I have a customer that owes $118.00.

Each month late there is a $15.00 late fee

and then 2% intrest added on top of that.

This customer is 11 months late.

What i am having trouble with is getting a formula to addthe 2% intrest each

month after the $15.00 charge is added each month.

My fomula adds the inital charge, then the late fees, then the intrest based

off that.

Right now my formula reads

[initial balance plus late fees]*1.02^late months

but over 11 months that comes to $351.84

it should come up $333

My friend came up with a program answer, which doesn't help in the case of

Excel

Quote: Sorry, but all I have is a program oriented answer.

;variables

n=11

i=.02

p=118

f=15

;loop equation

for n

p=(p+f)*(1+i)

;outputs

month 1 118

month 2 135.66

month 3 153.67

month 4 172.05

month 5 190.79

month 6 209.90

month 7 229.40

month 8 249.29

month 9 269.58

month 10 290.27

month 11 311.37 Note, his is a little off; month 1 = month 0, month 2 would actully equal 1

month late.

* Rent

* Late Pay Interest calculated on Rent balance

* Payment

* Payment Part Rent

* Payment Part Late Fee (to be deducted from payment preferrentially)

* Rent Balance

* Late fee Balance

* Combined Balance

* as soon as I want to calculate the Late Fee based on the Rent Balance, the circular reference happens and nothing calculates right any more. I didn't figure out yet how to avoid this.

* Do I have to do this completely different ?

http://www.herber.de/bbs/user/69698.xls

Thx for help.

Regards,

Frank

So I have a pretty basic expenses sheet that looks like this:

Payment method | Gross amount | Currency | Fees | Net amount | Total to date

So my idea was to calculate the fees based on the "Payment method" field - eg. I'll input somewhere the values for Payment method and what fee they actually represent. For example if the Payment method is say "cash" then the fee is =0, if the payment method is "credit card" then the fee is 0.5% and inside the "Fees" column it would calculate the fee based on that value and the "Gross amount".

Then for "Currencies" - I'm working with two currencies - USD and EUR. My basic/main currency is EUR so in the "Total to date" column I want everything to be in EUR - eg. it will have to re-calculate the "Net amount" based on the "Currency" column and add that to the "Total to date".

Thanks in advance everyone!!!

DonkeOte, previously helped me with this Monster formula and I'm asking for a second look.

Simply stated I take an aged analysis of accounts receivable that repeats the interval between accounts. The formula recognizes that sequence and totals the calculate late fees. It also subtotals any previously charged late fee ((LF-) and then adds it all up to expose the late fee to be charged. I'm getting 'False' and "#value' as shown on the attached. Is it possible to overcome these???

Mucho Thanks

Mike

amount of late fees, date payment is considered late.

of the "Late fee" column to show the total owed on the 1st of every Month after the due date. For example:

Col “A” Due Date 1 May 2011

Col “B” Date Paid

Col “C” Paid Amount

Col “D” Late Fee Owed

1 May 2011-31 May 2011 if the person has not paid the fee of $194.00 on 1 Jun 2011 they will ($194.00 [amount owed] * 5%[interest]) + $20.00 [late fee] = 223.96. If they still have not paid by 1 Aug 2011, they will now owe( 223.96*5%)+$20.00=$255.16 and so on until they pay.

============================

Date: 13-Sep-05 {using fn =TODAY(), for instance}

Bond Coupon Maturity Par Freq AccrInt

B1 5.00% 15-May-2015 10,000,000.00 S ???

B2 10.00% 31-Oct-2012 10,000,000.00 Q ???

============================

In Excel, using in-built functions as far as possible (eg. ACCRINT, etc.), how do I calculate the Accrued Interest figure (as of today, and w.r.t. the last coupon date) for each of these 2 bonds? The last coupon dates, based on each bond's stipulated coupon frequency (Semiannual for B1, Quarterly for B2), would have been 15-May-2005 and 31-Jul-2005, resp.

I tried using ACCRINT, but couldn't really get logical answers with it. My back-of-the-envelope estimates for the accrued interest, till today (13-Sep-2005), on these 2 bonds are $123,000.00 and $40,000.00 resp.

Please advise what formulae I should be using...

Thanks in advance.

Indebtedly yours,

Om

I would like a set formula that can be used throughout my spreadsheet (for 31 days of the month) that will calculate daily what the interest is on the closing day balance based on the above parameters.

I have started with this =IF(B19>=5000,B19*E11/365*(G1-F12),IF(B19>=15000,B19*F11/365*(G1-F12),IF(B19>=35000,B19*G11/365*(G1-F12))))

However,it is only working for balances between $5000 and 15000 so obviously I am missing something!

Thanks in advance for your assistance!

Thanks again for your time!

LaurenL

Accounts Receivables.xlsx

Dear Forum,

I am Filing my Income Tax Returns and require some help in calculating the Notional Accrued Interest portion Financial YearWise .

In India, the Financial Year begins from 1st April XXXX to 31st Mar XXXX+1.

My Chartered Accountant has suggested that any Income arising out of Term Deposit Receipts should be filed in advance though it may be maturing in the next Financial years to come..

This is done by calculating the Notional Interest that can be achieved with the Term Deposit in each Financial Year...separately

I am using the Traditional Formula : = RECEIVED(OpnDt,MatDt,Amt,Int%,Basis)

Not exactly sure whether this would be good in getting the actual maturity value like the bank.

So please feel free to suggest any other formula which can suffice the requirement.

However, my actual need is to be able to Split or Bifurcate the Interest component in different Financial years.

Ex:-

Investing Amount -Rs 10,00,000

Opening Date- 15-Feb-08

Maturity Date- 24-Apr-12

Int %------------- 5.75

Basis------------2

= RECEIVED(OpnDt,MatDt,Amt,Int%,Basis)

Maturity Value- Rs 1,51,515

Principal- Rs 1,00,000

-----------------------------------------------

Interest Component- Rs 51,515

As a One-Time Maturity amount is received after the Term Deposit is matured on Maturity Date- 15-May-08 and this seems easy to calculate using the in-built formula, however , I require to split the Interest component accruing every Financial year wherever applicable.

Ex:

Opening Date- 15-Feb-08

Maturity Date- 24-Apr-12

Tenure : 1 Year, 4 Months and 16 Days.

I need to calculate the below :

Interest portion from 15-Feb-08 to 31-Mar-08 This would change the Principal + Int for the next step

Interest portion from 1-Apr-08 to 31-Mar-09 This would change the Principal + Int for the next step

Interest portion from 1-Apr-09 to 31-Mar-10 This would change the Principal + Int for the next step

Interest portion from 1-Apr-10 to 31-Mar-11 This would change the Principal + Int for the next step

Interest portion from 1-Apr-11 to 31-Mar-12 This would change the Principal + Int for the next step

Interest portion from 1-Apr-12 to 24-Apr-12

The portions marked above in the RED font is the Starting and END and is Uneven and BLUE FONT is from one FInancial Year to the other.

This is more to do with placing the interest component in different Financial Years so the reference of the Opening Date, Maturity Date and the Principal in reference changes..

Please see the attachment

Warm regards

e4excel

I think I need to have the output show the impact for both F2008 and F2009, as we are almost in F2009 (starting Nov 1st) and will continue to benefit from the volumes added (but not fees earned) in F2008. A truly elegant solution would be to have the current year's income statement information be displayed automatically based on the input of the current date (ie. once Nov 1 hits, revenue will be adjusted for all accounts closed in the prior fiscal year).

Thanks in advance for any assistance you may be able to provide.

Regards,

Kevin

YOU TO DOWNLOAD FREE TO YOUR COMPUTER! THESE INCLUDE A COMPOUND

INTEREST CALCULATING SHEET (NO NEED FOR TABLES OF THESE NOW!),

FRACTION TO LOWEST TERMS SHEET, INTERPOLATING AND EXTRAPOLATING SHEET

AS WELL AS MANY DAYS/DATES CALCULATING SHEETS (ALSO RETIREMENT DATE

CALCULATING SHEETS) PLUS DATE AND/OR TIME CALCULATION SHEETS AND "DAY

OF THE WEEK" RETURNING SHEETS. ALL THE DATE RELATED CALCULATION

SHEETS ARE OFFERED TO YOU AS EITHER A UK DATE FORMAT SHEET OR A US

ONE. THE 17 URLs FROM WHERE YOU CAN DOWNLOAD EACH RESPECTIVE

CALCULATION SHEET A

http://uploads.savefile.com/redir/80049.xls

http://uploads.savefile.com/redir/80059.xls

http://uploads.savefile.com/redir/80060.xls

http://uploads.savefile.com/redir/80061.xls

http://uploads.savefile.com/redir/80062.xls

http://uploads.savefile.com/redir/80063.xls

http://uploads.savefile.com/redir/80064.xls

http://uploads.savefile.com/redir/80065.xls

http://uploads.savefile.com/redir/80067.xls

http://uploads.savefile.com/redir/80068.xls

http://uploads.savefile.com/redir/80069.xls

http://uploads.savefile.com/redir/80070.xls

http://uploads.savefile.com/redir/80071.xls

http://uploads.savefile.com/redir/80072.xls

http://uploads.savefile.com/redir/80073.xls

http://uploads.savefile.com/redir/80074.xls

http://uploads.savefile.com/redir/80076.xls

P.S. THE 80061.XLS AND 80062.XLS URLs CORRESPOND TO MY AGE CALCULATING

SPREADSHEETS. REMEMBER TO CLICK "ENABLE MACROS" ON THE BOX WHICH

APPEARS WHEN YOU OPEN EITHER OF THESE SHEETS ON YOUR COMPUTER. THESE

WILL CALCULATE ANY TYPE (YES ANY TYPE!) OF AGE (NO: DAYS BETWEEN "AGE

TYPES" ARE CALCULATED, SO THE USER CAN "TAILOR" THE CALCULATIONS GIVEN

BY THE SHEET TO ANY AGE TYPE THAT THEY LIKE - I HAVE SEEN SOME PAST

DEBATE THREADS ON USER GROUPS ABOUT THIS - LEAP YEARS AND THE LIKE!.

THE 17 SHEETS COVER ALL THE FUNDAMENTAL AGE, DATE AND DAY CALCULATIONS

FOR PENSIONS AND LIFE INSURANCE/ASSURANCE FUNDAMENTAL CALCULATIONS

PLUS MORE (E.G. THE GENERAL DATE AND TIME CALCULATING SHEETS). THE

SHEETS ARE ALL THE "RICHARD MARYTREE" RANGE. NJOY! RICHARD

properties are stored in Excel workbooks, one month to a worksheet. I

have been asked to make the spreadsheets automatically change the value

of the "Late fee" column to $60 on the 5th of each month if the rent

has not been paid (ie if the following column is blank). I need to

know if this is possible in Excel, and if so, how do I do it?

I have a spreadsheet I have created to calculate rankings. I have figured out how to look up the teams wins and losses. Now I need a way to figure out how to look up all of the oponents wins and losses. The sheet is pulling all data from a score page sheet where the scores of games are entered. I now need to calculate oponents wins and losses that they have currently played.

The second peice is Strength of Schedule and Opponents Strenght of Schedule.

I would like to use current rankings as a strength adder. The last place team will be assigned a 1 counting up to the top ranked team. When a team plays a home game against the lowest ranked team, it will add a 1 (opponents strength adder) to the teams strength of schedule pool. If a team plays an away game it will be the teams I will add 1.5 times the opponents strength adder to the teams schedule strength pool. Then the team with the highest pool with have the hardest strength of schedule. as games are entered I would like the formula to change with the rankings of the teams.

Any help on this and the issue above is greatly appreciated. Attached is the spread sheet I am designing.

in our school system we charge late fee amount which accordoing to our currency is 15 per day , i have three classess with different rate of fee like

playgroup=3940

nursery=3980

kindergarten=4020

after 15 of each month we charge amount of Rs. 15, i use following formula which check search student name from another file using vlookup,

=IF(E5="Play Group",3940, IF(E5="Nursery",3980, IF(E5="Kindergarten",4020)))

now how i can put somthing in this forumula that check if date > 15 charge 15 than on next day 15+15=30 an so on..

help is required in this regards.

basic user of excel.

and then format it for a specific way. Here is what I have thus far:

Date Requested: [Cell G4 reads:] 03/25/1994 01:30:12 PM

Date Submitted: [Cell H4 reads:] 5/1/1998 12:00:00 PM

Now I can calcualte the Date Interval Elapsed with the following formula:

=YEAR(H4)-YEAR(G4)-IF(OR(MONTH(H4)

knowledge so do help me ..suppose i have the following data

A2 = Years = 9

B2 = Months = 175

C2 = Days = 392

i want to calculate these months and dates as supoose i have 0 years 24

months and 32 days so answer should be as 2 years 1 month and 2 days

because (24 months = 2 years and 32 days = 1 month and 2 days are left

as in the column of days).......

so plz help me thanks in advance......bye take care friendssss

--

naughtyboy

------------------------------------------------------------------------

naughtyboy's Profile: http://www.excelforum.com/member.php...o&userid=37151

View this thread: http://www.excelforum.com/showthread...hreadid=568830

No luck finding an answer? You could always try Google.