Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

amortization - calculating late fees and penalty interest

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)


Post your answer or comment

comments powered by Disqus
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!

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.

Trying to create a spreadsheet that calculates compounding interest and
penalties, where the interest rates change per quarter.

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

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

I'm trying to figure out how to create an amortization
calculation in Excel using a mortgage amount, interest
rate, & monthly payment amount. I'm looking to
automatically calculate the number of payments, total
interest paid, & the last payment.

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'm trying to figure out how to create an amortization
calculation in Excel using a mortgage amount, interest
rate, & monthly payment amount. I'm looking to
automatically calculate the number of payments, total
interest paid, & the last payment.

Hi Guys,

Currently have a spreadsheet that i made that will calculate the hours worked when a user enters start and finish time.

e.g

C9(Start Time): 09:00
E9(End Time): 18:30
F9(Hours Paid): 9.00 ((IF(AND(ISNUMBER(C11),ISNUMBER(E11)),MOD(E11-C11,1)*24))-D9)
I9(TIL Earnt): 2.25
J9(TIL Accured):2.25
H9(TIL Used): 0

Now to calculate how many hours is owed to the user:, i take the result above, minus standard working day hours (7.5), that gives me 1.5 hours owed.
It then needs to be multiplied by weekday penalty rate(1.5) or weekend penalty rate(2.0) (*'OT Rates'!$B$2))

C9(Start Time): 09:00
E9(End Time): 18:30
F9(Hours Paid): 9.00 (=(IF(AND(ISNUMBER(C11),ISNUMBER(E11)),MOD(E11-C11,1)*24))-D9)
I9(TIL Earnt): 2.25 (=(((IF(AND(ISNUMBER(C9),ISNUMBER(E9)),MOD(E9-C9,1)*24))-D9)-'OT Rates'!$B$5)*'OT Rates'!$B$2)
J9(TIL Accured):2.25
H9(TIL Used): 0

I assume there would be an easier way to do this envolving dates but am completely unsure on how to go about it

where 'OT Rates' is another worksheet which contains details on standard day and penalty rates.

Everything works fine if hours worked are greater or even then a standard working day(7.5 hours),

E.G

C9(Start Time): 09:00
E9(End Time): 18:30
F9(Hours Paid): 9.00
I9(TIL Earnt): 2.25
J9(TIL Accured):2.25
H9(TIL Used): 0

however if they work say 6 hours, it does the same calculation.

E.G

C9(Start Time): 12:00
E9(End Time): 18:30
F9(Hours Paid): 6.00
I9(TIL Earnt): -2.25
J9(TIL Accured):0
H9(TIL Used): 0

What idealy should happen:

E.G

C9(Start Time): 12:00
E9(End Time): 18:30
F9(Hours Paid): 6.00
I9(TIL Earnt): -1.00
J9(TIL Accured):1.25
H9(TIL Used): 1.00

sample timesheet if the above confuses you:
sampleTimesheet.xlsx

cheers,

daniel

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)


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