Excel 2007 : Extract data that isn't there in the first place

[HangMan]

I have a pivot table which reflects loan repayments, there are several hundred separate sheets that feed into the one pivot table, reflecting loan repayments over a 25 year period, all with different start and end dates.

The loan repayments are scheduled every six months, so for example 31 March and 30 September for the duration of the loan. There are no values for the other 10 months in the year as just two repayments per year are made.

I need to find a way to 'Automatically' extract to a table, equivalent monthly returns (I appreciate that these won't be exactly the same amounts had the loan repayments been calculated on a monthly basis using PMT but this is not an option). So, for say a six month period, I have written a formula that looks up the amount owing against the appropriate start date in the pivot table, divides this by the number of days between the end of September the previous year and the end of March this year (which is the six month period this repayment refers to) and then multplies this number by the number of days in the appropriate month, eg x 31 days for October, 30 days for November etc.

This is my formula, where Column B in the pivot table holds the repayment dates and A11 holds the start date and A12 the end date to lookup. It might be that I need to calculate monthly (equivalent) repayments from say March 2010 to March 2025. The start and end dates are user defined.

Please Login or Register  to view this content.

Example
Amount due to be repaid as at 31 March = $21,000
Number of days between 30 September and 31 March = 182 (non leap year)
Number of days in October = 31

($21,000 / 182) x 31 = $3,576.78 (October equivalent repayment)

November has 30 days so

($21,000 / 182) x 30 = $3,461.40 (November equivalent repayment) and so on...

What I am somehow hoping to achieve is to expand this formula into some sort of loop so that it cycles through every row in the pivot table between the user defined start and end dates and then applies the formula above to calculate values for each month and then somehow automatically outputs this into separate rows in a table or similar on a spearate worksheet.

I've considered things like the Advanced Filter, SUMPRODUCT and various IF formula, but I can't figure a way to cycle through the date range, perform the calculations, updating accordingly depending on the number of days in the month and then automatically, dynamically placing the results for each month in a separate row in a new table.

Am I asking too much of Excel or can anyone kindly suggest an approach or solution to this problem.

If anything is unclear, please let me know.

Many thanks...

[davesexcel]

When you have a long winded question, people tend to loose interest, are you not able to ask the question in 1 sentence?
Then supply a SAMPLE WORKBOOK that directly refers to the question.

[HangMan]

Hi davesexcel

Fair point, however there is no way, even with a sample file this problem could be explained in a single sentence. I adopted the approach of trying to explain the problem (I obviously failed) because I so often see questions which people have asked in a single sentence followed by lots of people saying, I don't understand what you are asking.

Anyway, I think I have actually worked out a solution after many hours working on it, though it isn't quite there. I may make a new post an re-phrase the problem and add an example spreadsheet as per your suggestion.

Thanks for your feedback though, I will bear this in mind in future.