For a 40-year daily income/expense facility development amortization (not a traditional loan amortization), I’m trying to create a form workbook that automates each development’s amortization by matching dates in the amortization worksheet to an income/expense summary worksheet.
For example, I would like to have the amortization worksheet test each of its dates to see if a match exists with the dates in the summary income/expense worksheet. For each date where a match exists, the amortization worksheet would then reference the corresponding income/expense amount set forth on the summary sheet (formula replicated per income/expense type, not looking for the formula to figure out what’s expense and what’s income).
The following is an example of the summary income/expense event worksheet:
Column A Column B Column C Column D
Date Income Expense A Expense B
1/6/2016 $55,000 $30,000
1/7/2016 $21,000
1/8/2016 $7,500 $5,000
1/9/2016
1/10/2016
The following is an example of how the amortization worksheet is set up with the dates running for 40 years in their own row with income and several other expenses that occur.
Column A Column L Column M Column N
Date Income Expense A Expense B
1/1/2016
1/2/2016
1/3/2016
1/4/2016
1/5/2016
1/6/2016 [Ref B1] [Ref C1]
1/7/2016 [Ref B2]
1/8/2016 [Ref B3] [Ref D3]
1/9/2016
1/10/2016
etc……
If I had no more than 7 instances to test, I would assume to use an “if/then” formula handle this. But with 480 possible income/expense instances, I’m stymied.
Is there, and what is the best formulaic method for managing something like this? I’ve looked within the forum and see the match/index suggestions, but am not sure if that’s what I should be looking at, or which one.
Thanks very much in advance for any suggestions.
Bookmarks