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)

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)

- Calculating late fees and accruing 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
- Spreadsheet for accruing interest and penalties that vary
- Morgage schedule that accumulates interest and late fees
- Add late fee. Inserting text and numbers in sep clumns IF date entered is past 20th
- Calculate Revenue Volume Based On Current Date & Other Variables
- Amortization Calculation in Excel worksheet
- AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS!
- Calculating finance charges on simple interest
- Adding late fees automatically
- Amortization Calculation in Excel worksheet
- Calculating Hours worked and Time in Liu owed.
- Calculate Opponents Record and Strength of Schedule
- Late fee charges...
- Calculate Elapsed Date and Time

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!

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.

penalties, where the interest rates change per quarter.

Thanks again for your time!

LaurenL

Accounts Receivables.xlsx

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

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.

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?

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.

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

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