Free Microsoft Excel 2013 Quick Reference

Calculating late fees and accruing interest

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!


Post your answer or comment

comments powered by Disqus
Have modified an amortization (for a loan i have given to somebody) to allow
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)

I'm trying to come up with a formula to calculate intest for me here at work.
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.

I'm creating a chart for a company to calculate late fees. There is a start date, 5-day grace period before it is late and then and end date (in which the payment was received).

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

I am looking for a formula that will calculate a accrued late fee with two
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……

I'm trying to come up with a formula to calculate intest for me here at work.
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.

Hi, I'm trapped in a circular reference problem since I need to calculate "late fee interest" :

* 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

Hello everyone!

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!!!

Hi,

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

I need to enter the dates due and received, beginning balance, interest rate,
amount of late fees, date payment is considered late.

For a class assignment I have to draft a demand letter and show my work on Excel. I have a principal amount, an interest percentage, and late fees of $35 month. I believe that the interest would be on the principal amount plus the late fee each month. Does anyone have a formula or know how I would set up this spreedsheet? Thanks so much!

I am trying write a formula that will automatically change the value
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.

Dear VBA Gurus,

============================
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 am attempting to calculate accrued interest on day end balances for the month. $0 to $4999 earn 0% interest, $5-14999K earn .6%, 15-34999 earn 1.5%, $35-59999 earn 1.75% and $60000 plus earn 2.25%.

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!

On the morgage amortization schedule, I would like to include computed late
fees.

Ok. So what I am trying to do is add the text "Late" to column J and add the number "5" to column K, but only if date entered in column G is past the 20th of whatever month. My customers are subject to a $5 late fee if monthly payment isn't received by the 20th. My business is growing and it is becoming difficult to do this on a one-by-one basis. I have tried to play around with the SUMIF formulas but I really think that this one is over my head. Any help with this matter is greatly appreciated!!

Thanks again for your time!
LaurenL

Accounts Receivables.xlsx

Bifurcate the Accrued Interest portion Financial YearWise 1st Apr XX To 31st Mar XX+1

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 am trying to automate calculation of expected volume and revenue figures for a financial services provider based on the current date, the expected closing date of the loan(s). Revenue from loans is based on a one time fee and ongoing interest income from outstanding loan volumes. Timing of closings will also impact revenue as interest will be earned on only the time period during the year the loan is outstanding. The attached file shows a simplified version of my input and the desired information I need, although this doesn't necessarily need to be in this output form.

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

JUST TO LET YOU KNOW THAT I HAVE 17 SPREADSHEET FILES AVAILABLE FOR
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

Does Excel contain a simple interest loan calculation function built in? Or do I need to specify how exactly simple interest calculates on a per diem basis? What I am trying to do is calculate finance charge based on daily interest such as a standard auto loan contract. I am supplying the interest rate, term, and days to first payment - which in itself contains all of the necessary data for calculation (assuming excel has the loan calculation built in), however simple interest is based daily, so the calculation needs to understand days in each month, and totals days across a long period of time, and I seem to remember excel having basic loan calculation features built in, however general excel help only seems to have basic functions. What would be the best way to approach this?

I work for a property management company and details of our rental
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 am not sure the best way to approach this. I was thinking of doing it through functions but a VBA script may work best. Any guidence on how to accomplish these two tasks are greatly appreaciated.

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.

hello everyone..

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.

I have a goal to try and figure out how to Calculate Elapsed Date and Time
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)

Hi i have a problem in excel ..i m new of excel so dont have enough
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.