Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
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, ...
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 ...

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 spreadsheet1 (Aging) with names and dates, Names can appear more than once. Spreadsheet2 (Customers) has a list of customers and formulas to calculate aging date. Customers appear only once. I want to match Aging names with Customer list then apply formula to caluculate aging date. Exp:Aging shows Customer Smith with a date of 3/01/09, Customer list shows Smith with a formula of B2+10. Column B is the date col. Answer would be 3/11/09. I have used VLookup. It returns the formula not the result.
Any help for this novice would be appreciated.


I would like a more detailed Aging of Account in Excel in a scenario like this: If I give advance to a customer and he clears his oustanding with supply of goods. Outstanding will be cleared on FIFO basis.

I want an aging analysis of the balance.


Is it possible to create an IF formula that considers two cells in the logical argument? If so, how can it be done? If not, any suggestions?

I'm writing a formula to calculate a due date for payments. It must take into account the first two payment dates (they need to be 11-14 days apart) as well as the number of days in the month. For example - recurring payments can't occur on any day past the 28th of the month because February only has 28 days. Lastly, the due date can't be more than fifteen days away from any of the payments (in either direction - late or early it doesn't matter). It just can't be more than 15 days.

Thanks for your help!


Hi All

I have a spreadsheet that I am currently using to input payments onto and then create a CSV file using VBA which is working well, but I now need to batch the payments together so therefore need to do a summary before saving as a CSV file.

The column headings are *Matter Number, *Payment Amount, *Beneficiary Account Number in columns A:C,

On the new summary page it should just show *Payment Amount and *Beneficiary Account Number, one line for each different account number and the payment amount should be the total for all payments being sent to that account.

Any help would be greatly appreciated.

Cheers
Mike


Hi All

I have a spreadsheet that I am currently using to input payments onto and then create a CSV file using VBA which is working well, but I now need to batch the payments together so therefore need to do a summary before saving as a CSV file.

The column headings are *Matter Number, *Payment Amount, *Beneficiary Account Number in columns A:C,

On the new summary page it should just show *Payment Amount and *Beneficiary Account Number, one line for each different account number and the payment amount should be the total for all payments being sent to that account.

Any help would be greatly appreciated.

Cheers
Mike

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 pivot table that has "date" as a row field and "sum of charges" in the data field.
In a macro I need to remove "sum of charges" and insert "sum of payments". Both Charges and Payments columns are included in the PT source. How may I do this in vb?

(I have an area on my worksheet referencing the PT and is the source for my chart).

Thanks very much..


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




Hello,

I know there are many questions on this topic already, but I have tried the different formulas, and they are not working for me.


Basically I just need to do a simple aging for health claims, using the Date of Service (DOS) and paid amounts.

Example:

Patient DOS Paid Amount

John Doe 20100801 15.55
Jane Doe 20100601 62.00


All i want is a formula or solution to go through about 10000 claims like above and just sort the data into Current (0-30 days), 31-60, 61-90, 91-180, 181-360, over 360


Also, Column G has the Date of Service, Column P has the Paid Amount
and Columns R, S, T, U, V, W are the aging columns respectively.

THANK YOU IN ADVANCE!


I use aging reports at work for accounts which I import into excel 2003 each month. These are worked according to amounts owed on the accounts and how far past due they are. A lot or most of these accounts are carried forward from last months report because they are still in aging again the next month. I would like to link the individual accounts either by workbook or worksheet when they are a repeat from the previous month or months report to avoid having to research the account anew.

This way I would know I worked on this account last month and would have access to the notes from the previous month and even though they are on a new report it would be like a continuation of work done on the account rather than having just this month's information




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 was looking at Microsoft's website and they had a nice amortization schedule (see attached) but I have a few problems and questions with it.

1. If you have a base year of 2005 and a base month of 6, the Payments in the First 12 Months box shows payments for January through May. That isn't right. If it started in June, there wouldn't be any payments right?

2. It would be nice if it had a drop down for principal and interest loan versus a interest only loan. I know there's 0 amortization for interest only but it would be nice to have a spreadsheet that adapts for that as well. Any ideas?

3. How can I make this schedule account for a mortgage that begins in the middle of the month? I would only want to pay half that month's payment. Not so worried about days but would like it to be able to account for a mortgage that begins in the middle of the month.


Hi,

I need to create a formula to look up 2 dates from another report and bring back a total $ for those within that date range.

Example is below...this is the summary sheet. On another report will have my data. If the amount billed was in Jan but received in march, it would go in the march column. Sort of like an AR aging.

Jan Feb Mar
Jan $50
Feb
Mar

Please help.

Thank you,


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)

I have two sets of data on a single worksheet, as so;
Short example - actual LeftData = Rows 4-601 RightData = Rows 4-823
RightData is more comphrehensive than LeftData


LeftData RightData
ColA ColB ColC ColD Col E Col F Col G
Row XYZ XYZ
4 1 Avg Charges 65760.37 1 Avg Charges 45,511 65,760
5 Count of XYZ 2 Count of XYZ 10 2
6 3 Avg Charges 31309.41 2 Avg Charges 29,073 0
7 Count of XYZ 3 Count of XYZ 8 0
8 7 Avg Charges 44233.39 3 Avg Charges 48,608 31,309
9 Count of XYX 2 Count XYZ 6 3

Formula for G4 (at present is):
=IF(ISNA(INDEX($A$4:$C$601,MATCH(D4,$A$4:$A$600,0),3)),0,INDEX($A$4:$C$601,MATCH(D4,$A$4:$C$600,0),3 ))
<< which produces the 65,760 (Cell C4).

Formula for G5 (at present is):
=IF(ISNA(INDEX($A$4:$C$601,MATCH(D4,$A$4:$C$600,0)+1,3)),0,INDEX($A$4:$C$601,MATCH(D4,$A$4:$C$600,0) +1,3))

These above two work - so I've copied them (G4:G5) to (G6:G7);
Then copied G4:G7 to G8:G11; then copied G4:G11 to G12:G19
Copying the Blocks 2,4,8,16,32,64,128, etc.. fashion...
So my entire spreadsheet formulas in G4:G601 work fine,
It is just the step-copying I had to do that "bothers" me.
Could I have done this a different way (simplier???)
If so, HOW?

Thanks in Advance..





I am trying to get an aging from information. I need a formula that allows me to age data in a range over the last 7 days, 8 to 14 days, 15 to 21 days, 22 to 28 days and greater than 28 days. The problem is this aging has to adjust automatically on a daily basis.

Here's an example of the data:

INV # INV. DATE AMOUNT
529721 3/1/2007 268,135.00
529828 3/2/2007 239,004.00
529829 3/5/2007 312,310.00
529841 3/21/2007 613,108.00
529845 3/29/2007 160,720.00
529846 4/1/2007 603,920.00
529847 4/13/2007 15,420.00
529848 4/19/2007 9,675.00

Assuming the date is 4/20/07, I want to get a report that shows:

Last 7 days (4/20 - 4/13) 25,095
8 to 14 days (4/12 - 4/5) 0
15 to 21 days (4/4 - 3/28) 764,640
22 to 28 days (3/27 - 3/20) 613,108
Greater than 28 819,449


I've been trying to use Sumif, but that doesn't seem to work since I am looking at a date range instead of a set number.

Any suggestions?


I maintain a list of charges over time and have created a summary page in my workbook to identify the total charges in a given month. How do I sum the values in one column based on the date in another column falling in between the start and end of the month? As an example, my detail sheet looks like this:

1/3/07 $34.50
1/8/07 $20.36
2/1/07 $42.94
2/1/07 $26.23
2/10/07 $32.88
2/28/07 $29.45
3/8/07 $18.58
3/15/07 $26.43
3/26/07 $31.74

So I'm assuming SUMIF needs to be used, but how?


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,

I have a shared Timesheet workbook that each employee from various departments uses to record their time spent on a customer contract. What I need to be able to get a summary of hours worked on a contract at any time on a summary sheet.

I have attached an example. On the example are 3 employee time sheets and then there are 2 summary sheets where I have hand crafted examples what I would like my summary pages to look like.

I would imagine this is quite complex but any helo you can give would be much appreciated so thanks in advance.

One more thing to consider is that new contracts are added daily so the summary needs dynamically account for them.

Thanks.


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
Any one can teach me how to calculate aging, from the date created to current date.

Aging Type:
Less Than 7 days
Less Than 15 days
Less Than 30 days
More than 30 days

Thank You
Regards
Rashim


hi all,

i'm looking for a formula that will classify amounts according to the aging column automatically each time the month is changed.

my file is attached...


After looking thru the forum for some time, I have been unable to find a solution that could be adapted to my problem. I am trying to create a past due (aging) report. The data would be pulled from approx 400 worksheets (one for each contract number). Each worksheet is identical, as far as the information locations. The worksheet contains this information:

B1 = Contract Number

Bill Date(colA) Princ(colB) Int(colC) Total (colD) Running Balance (colE)
1/7/09_______$49.21___ $46.08__ $95.29 _____$95.29
2/3/09______ $49.64___ $45.65__ $95.29_____ $190.58
01/00/00____ $ 0.00____ $ 0.00__ $ 0.00_____ $190.58


This billing information is pulled in from another data input worksheet.
The running balance will reflect any payments that have posted to the account. The sheet is set up with 12 rows of data (one for each month), so there are "01/00/00" dates that have not been filled in yet.

I am hoping to be able to do this without having to change anything in these worksheets, as there are so many involved. (Having all formulas needed built into the new worksheet.)

I would like the Past Due (aging) report to be in a new sheet and look something similar to this:

Contract# Current 15-45 days 45-89 days over 90 days
486925__ $0.00__ $95.29___ $95.29____ $0.00


Am I asking the impossible? I only know the basics of Excel and then just enough to be dangerous.

Any advice/help will be greatly appreciated.
JW


Hello all, I am new to excel and new to these forums so please be gentle! I hope I can explain my problem well enough for someone to be able to help.

Basically I want to use excel to create a budget for my day-to-day and long term expenses as I have just moved into a new flat. I move my money every month to different bank accounts, depending on whether it is a monthly payment, yearly payment, or long term savings.

In one column I have a list of expenses: rent, travelcard, council tax, gas, electricity etc
in the second column I have the amount that expense costs.
In the third I have set up a drop-down box depending on which account the money is to be moved to, i.e. "monthly payment", "yearly payment", "keep in current account", "savings account". To help you visualise, things like rent, gas, electric, phone bill go to the monthly payment account, things like car tax, tv license and car MOT go to the yearly payment account, as it;s an easy way for me to split the costs month-by-month and dig out the account card once a year to pay them.

underneath these columns I want to be able to set three or four formulae, I.e. total amount going to monthly payments account, total amount going to yearly payments account, and total amount going to long-term savings account.

I know I could just do a simple SUM formula and select the expenses individually, but I really want it to change automatically based on which account is selected from the drop down box, as you and I all know that new charges and expenses crop up every month and this would allow me the freedom to add more expenses later, or even change which account I put the money in.

Thanks for any help you can provide, I hope I haven't confused you. Also, for what it's worth I created the drop-down list by writing the list on a separate sheet then going back to my "main" sheet and creating a validation data drop-down list.