Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Aging Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have attached a spreadsheet that shows a summary of an account to date. I want to put in formulas to continue as new charges and payments occur. Thanks in advance - and I hope the upload worked!!

View Answers     

Similar Excel Tutorials

Get Formulas from Cells in Excel with VBA
Get a formula from a cell using VBA in Excel. This method returns the actual formula instead of the output value. G ...
Count the Number of Unique Values in a List in Excel
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
How to Input, Edit, and manage Formulas and Functions in Excel
In this tutorial I am going to introduce how to input, edit and manage excel formulas. To start entering a formula, ...
Apply Conditional Formatting to Multiple Cells with a Single Formula
How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Get the Decimal Part of a Number in Excel
How to get the decimal part of a number without any rounding in Excel. This tip allows you to simply remove the who ...

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel

Similar Topics







I have seen a number of ticket aging requests, and there was some good information in there, but I have so far not been able to find something that would help with my specific needs.

We have a report which contains some member information, including the date the date received. We would like to have a separate column which would express the ticket aging, in 30, 60, and 90 day intervals.

In this case, however, we do not want the aging to be from the date received, we would like the aging to be from the 1st of the month of the date received. So if the line is dated 2/27/2011, and it is now 3/1/2011, the record should be marked as being 30 days old.

I have attached a spreadsheet which will hopefully explain what I am talking about better.

Additionally, if there is a way that the entore row can change the background color based on this aging value, that would be great as well.

Thank you.
Daniel


I have a excel file with 8000 rows. It is an worksheet of AR invoice aging.
I want to be able to create spreadsheets for the following. 1. Total Company
Aging - (summary of this spredsheet) 2. Summary Aging per Customer #
(summary of each customer) I have a total of 400 customers in this file. I
don't know how to use VGA - just formuals. I can go the vlook ups, but I am
just a beginner with those. Any suggestions/ideas as to how I can manage
this data? If you have other ideas I would like to hear them. I know
someone out there has done this before. At my old job I had some programers
that could do this for me. Here I am that person if I want the report.
HELP!! Thanks!
EX: these are the column headings that I have. I have to manually age
into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120 >120




Need a formula to enter invoice amount in correct aging column by comparing invoice date to a random date entered in cell A1 to determine days outstanding. See attached worksheet.


I want to calculate the number of payments that have been made on a mortgage between two dates (the first date being the first payment date and the other date being some random date in the future).

Payments are made monthly on the same day of the month. So, if the first payment date is 2/15/1999 then the next payment will be made on 3/15/1999, and the third payment on 4/15/1999, etc.

So basically I need a formula that counts how many times a particular day (determined by the day of first payment) occurs each month between two dates, e.g. between 5/15/2005 and 7/14//2006 the 15th day of the month occurs 14 times (including the first payment date).

Let's say I want to know how many payments were made between 2/1/1999 and 3/25/2011 (REMEMBER: payments are made on the 1st of the month in this case), what would the formula be for figuring that out???

NOTE:
A1: First Payment Date
A2: Second Date (any future date, not necessarily a payment date)

I've used, to no avail, =DATEIF(A1,A2,"m"). This function doesn't account for the fact that payments are made on the same day of each month. For example, with A1:1/1/2011 and A2: 3/31/2011 the formula returns 2 months even though 3 months of payments would have been made.

Can you produce a formula that counts the number of payments between two dates, recognizing that the day of first payment will be the day on which each following monthly payment is made???

THanks in advance.




Hi Guys,

I am new to this forum. I have been working on this for the last 3 years to no avail, I just dont understand how to obtain what I am after and could really use a hand!

I am working with a set of invoices issued to a "customer". Each invoice has a date, and must be paid within 14 days otherwise interest accrues at the rates provided for in my attached workbook.

Over the years the customer has incurred various bills as can be seen, and made various payments. There is an amount oustanding.


I want to Workout the interest owing total and I am wondering what the best way to do this would be? I have a list of bills and payments owing with payments made and the date of each.
- I know my interest rate
- I know my overdue dates as that would be date of payment minus date due.

Im getting stuck though in effecting this.

I think I need a running total of the "amount oustanding" and instead of applying each payment to a partciular invoice, it should be each payment goes toward the "amount oustanding" and interest is worked out date to date between each payment and charged accordingly.

I have a attachedan IMAGE of what I think it should look like, but just dont know how to put this into excel.

ANY HELP WOULD BE AWESOME. Thank you guys.

INTEREST CHARGES.xlsx Excel Schedule (Updated)

interest.jpg (sample calculation image)

The IRR formula is set up for annual payments. How can it be changed to
account for monthly payments? I know the NPV formula will work if you divide
the discount rate bay 12 for monthly payments. Do I multiply the solution by
12? I tried this and the result did not look correct. I do not want to use
the XIRR formula for this because it will be a template.

Thank you



Hi all,
Can anyone tell me how to solve for Interest RATE with 2 (or more) payments in advance. I know I can simply deduct the number of advance payments from the original PV but that does not return the same interest rate as I get on my HP calculator or by using T-Value (a financial amortization program). The issue is that when you have 2 payments in advance, standard amortization tables assume that you would have 1 payment of zero at the end of the cashflows before the residual (FV) becomes due (to make up for the fact that the last payment was paid at the start of the loan). I successfully created formulas that calculate PMT, PV and FV with multiple payments in advance but I keep getting "#NUM!" error messages when I try to solve for RATE. I turned on "Enable Iterative Calculations" under Excel Options/Formulas but none of the formulas I've tried work. If anyone can help, I would really appreciate it.
Thanks in advance.
Thane


Hi All,
Please provide the formula for below aging.
0 < 90 days
90 >90-180 Days
180 >180 Days
Column M has the aging details.
I am attaching the file below.
Thank you in advance for the help.


I need to create an aging column. Please help.

*I have created a column that subtracts the due date from the current day's date. However, the rows that have no data, return "35965."

Thanks for the help in advance.


Hi every one... Can you help me in making formula of my Aging Report. I attached already the file for editing. Any kind of effort is very much appreciated. Thank you so much. More power to this site..


HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)


Could use some help with the following:

I created a spreadsheet to track payments made to contractors. In this spreadsheet I have a simple formula that tells when the payment has to be made based on the date it was rec'd from the contractor and adding on the payment terms.

Now what I have to do is add in some date constraints. Our paying office only processes payments between certain windows during the month. Lets say that for this month the payment window closes Jan 27th to Feb 2nd. What I need to do is if after someone enters the date the payment is rec'd and the 14 days is added for the payment terms, should the date the payment is made end up falling within the payment black-out period (Jan 27th to Feb 2nd), I then need excel to adjust the must be paid by date to 3 days prior to the black-out period.

Hope this makes sense....I've tried a whole bunch of different things and have looked online at several sites with no luck. Would SERIOUSLY appreciate it if someone can help me!

Thanks!


I have an Excel timesheet that the supervisor loads the time (in and out) for all the shop employees. These employees may work up to six different jobs in one day at various points throughout the day. I have no problem with calcs and formats on the daily time entry tab (sheet1). What I need is a way to track each job using a running total so that as the job is in progress we can see how we are doing against estimated hours. I thought perhaps I should at least make a weekly time summary pivot table thinking that it would make it easier to calculate, but it didn't help with running totals. The summary included (5) copies of the same daily entry tab (sheet1) just given a different name, in this case ..a simple date change and save the sheet with the date used. OK, here's the problem. How do I create a running total that would track each job? I forgot to mention earlier that each daily time entry sheet is saved and named by the date for which it represents. The weekly time summary pivot table works fine....but it only gives me the grand total of hours worked for each day...not a running total summary. I can view the pivot tables and go to the calcualtor and add the hours up per job, but that's defeating the purpose. Anyway, I hope this is clear enough to at least get some responses. Then, maybe by then I'll have a better understanding how to ask the right questions.

Check out the weekly time summary pivot table...hope I can upload.

Newbie to Forum

Thanks everybody!

Charlie


Hi Everybody!

Is there anyone have the formula of creating Aging Reports in Excel.


My company uses the aging method of determining bad debt expense. I need to calculate the appropriate balance in the allowance for bad debts account. Based on historical analysis: amounts less than 16 days outstanding have a 98% chance of being collected; between 1 and 30 days probability is 90%; 31-45 days=85%; 46-60=75%; 61-75=40%; and over 75 days outstanding, there is no chance of collection.

Please help me build a spreadsheet that will have the following features:
1.Calculates the appropriate balance in the allowance account
2.Allows the user to enter the dollar amount of accounts outstanding in each category. These amounts are available from another computer report, the aged trial balance.
3.Is easy to follow and understand
4.Does not allow unauthorized changes to likelihood of collection percentages


Hi All,

I've created a spreadsheet for a lottery syndicate (20 members), the cells in column A has the Member Names (1st name in A2)and the cells in row 1 has the weekly dates (1st date "12/10/2010" is in B1).

When a member makes a payment ($10/ week), I open the spreadsheet and write "10" in the which ever the next empty weekly cell is.

I'd like to be able to tell at a glance which members are falling behind in their payments and have just come across "conditional formatting".

The formula needs to see If the cell for a given date is empty (I've not entered 10 in it) and if it's also within 28 days of today then I'd like the entire row (not just the cell) to change red.

I cant get it to work, any ideas ?

This seems a great feature and I hope it can do what I want here, I'll just have to play around until I stumble on the right formula.

Jack


I'm trying to calculate how many quarterly payments will fall in a defined fiscal year but can't figure out how to do it.

If I enter into a lease that starts on 1/1/07 I will have four payments in 2007. My payments are set up as quarterly in arrears so the first one will be due 4/1/07 the second will be due 7/1/07, the third 10/1/07 and the last on 1/1/08. Simple. Problem is, if I enter into a lease in some month other then January then I will end up not making 4 payments this year. I'd make at most 3 payments.

For cash flow purposes, I need to project into the future how many payments I will make on each lease each fiscal year. It will be four payments for each year other then the first and last year the lease is still active. To make matters worse, my fiscal year is 9/1 through 8/31 and leases are signed each month and generally with terms of 5 to 7 years.

The spreadsheet I use to track them includes a column for the first payment date, the last payment date (the last payment at the end of the lease term), the number of payments over the term, say 28 for a 7 year lease, and the payment amount per quarter. Then off to the right I simply fill in columns with a formula multipling the payment amount by the number of payments that will occure that fiscal year. One column per year 8 or so years into the future. Each column heading included the fiscal year i.e. "FY 2008".

I'm hoping someone has developed a formula that can determine how many payments will occure in each of the next 8 or so fiscal years based on the information I manually plug in now.


I need a formula that will help determine what "bucket" (based on today's date & due date) a date would fall under. I have 5 buckets:

RRC
0-14 Days
15-22 Days
23-30 Days
31+ Days

I am using the following formula:

=IF(D2="","N/A",IF(TODAY()-D2-26,TODAY()-D2-12,TODAY()-D2-5,TODAY()-D20,"31 + days"))))))

This formula works fine, however the next day I run the report the formula has to be slightly modified to place everything in the correct aging "bucket". Is there a formula that would alleviate the need to update the formula everytime I would like to check the inventory?

The dates are actually based on a weekly (Sunday through Saturday) schedule. So if the Due date is Prior to today's date it would fall into the 31+ category. If due up to 14 days (including today) it would fall under the 0-14 Day "bucket". And so on...

Any help is greatly appreciated.


Hi,

I need to find a way to historically track aging of open issues (30, 60, 90, 120 days old). I have thousands of rows of data that include the open date, closed date, and a days old column (calculated as of TODAY for all currently open issues). I need to be able to fill a table with how many issues were open over 30, 60, 90, and 120 days for every week ending date since the beginning of the year.

Assume I have column A as the identifier, B as the Open Date, C as the Closed Date, and D as the Age.

I have a system that works for calculating this real-time (how many for each bucket as of right now), but I really need to be able to fill in each bucket for previous dates. I know nothing about VBA, so if there is a way to do this without VBA, that would be very helpful.

Thanks in advance for any help.


Hi,

I wan to calculate the aging between two date and time stamps, the result should be in no. of days. But it should exclude the weekends and the holiday list as well.

Date 1 7/23/2009 10:00 Date 2 Today's date Holiday List 1-Jan-10 26-Jan-10 27-Jan-10 10-Apr-09 1-May-10 9-Aug-09 20-Sep-09 27-Nov-09 25-Dec-09


I am trying to create an issues log that includes the aging of issues that are in "open" or "in progress" status for the following periods of time:
0-30 days
31-60 days
61-90 days
91-120 days
over 120 days

The aging should be from the date the issue was opened to the current date (including weekends). Once the aging is calculated, i want to be able to count the number of issues that fall within each range.


I have a pivot table with 100 accounts showing charges against each account for each month over a time span. When I show a table with accounts listed in rows and monthly charges in columns, I get 100 rows of data with the grand total in the rightmost column. THINGS ARE GOOD.

When I build a table showing subtotals of charges over shorter time spans, some accounts will have no charges, and rows are not shown for the zero-value accounts. Hence, I get something less than 100 rows of data. NOT WHAT I WANT.

My objective is to build a table with 100 rows and various time span subtotals in columns. I am attempting to build this final table by manipulating the pivot table and cutting & pasting the grandtotal and/or subtotals into a final table (repeated as needed). THIS IS AN OBSTACLE.

The problem is that the number of rows changes depending on how many rows have zero values. Is there a way to force each pivot table to show 100 rows even if some of the values are zero? YOUR EXPERTISE IS NEEDED HERE.

Your help will be greatly appreciated.


I am trying to report on the number of dates that fall between standard aging dates (30-60-90-120+) given the TODAY() function. See attached, i need to count how many clients have had a "Service Campaign" run in the last 30-60-90 days.


I am giving a loan with a large initial disbursement, and then once certain contingencies are met, there are other disbursements. Payments won't start until next year, and then will be quarterly. (The assumption is that all the disbursements will happen before payments start.) I am trying to figure out the formula that will calculate the interest and the payments:

Total of $280,000
First disbursement: $150,000
Contingency One: $30k disbursement
Contingency Two: $20k
Contingency Three: $20k
Contingency Four: $20k
Contingency Five: $40k
The interest rate is 7%, accrued monthly.

Quarterly payments will start in March of 2011.

I know the date of the first disbursement, but not the dates of the subsequent disbursements.

Any help with be much appreciated. And, by the way, if you know of any way to do this, please don't hesitate to respond. Even an inelegant way is better than where I am today. Thank you!


Hi! I've been perusing a spreadsheet in Excel 2003 I've been working on with lots of array formulas to calculate averages based on two criteria. I thought my formulas were working until I discovered my averages seemed abnormally low. I checked this using a filter on the data and confirmed it.

I have several cases where there is no data to average for a cell, but the cell is counted since it meets the two if criteria. I believe the array is then counting this as a zero and averaging accordingly.

Is there an easy way to write the array formula to ignore the blank cells?

Here, for example, is what I am doing now:

=AVERAGE(IF(SUMMARY!$AF$2:SUMMARY!$AF$300=$A3,IF(SUMMARY!$BK$2:SUMMARY!$BK$300=$A$2,SUMMARY!BG$2:SUM MARY!BG$300)))

I want the cell to go to the Summary tab, check column AF and BK and then if the two criteria are met, average the data in cell BG. However, there are one or two rows in BG that are blank in some cases. How do I write the formula to exclude those cells?

Thanks for your help. I can attach a spreadsheet if necessary.