Hi,
I'm trying to create a formula that identifies the difference of values based on multiple dates. I've attached a simplified portion of the financial model to help illustrate the question.
The output value (and formula I'm seeking) pertain to cells F13:F53 (Yellow background cells). I've manually inputted the value I am trying to automate to help explain. The value returned would be based on the dates in E13:E53 and looking up the difference in corresponding tax schedule in I4:X7.
I've tried hlookup, but haven't been able to do so successfully. Any help would greatly appreciated. Thank you.
I'm trying to create a formula that identifies the difference of values based on multiple dates. I've attached a simplified portion of the financial model to help illustrate the question.
The output value (and formula I'm seeking) pertain to cells F13:F53 (Yellow background cells). I've manually inputted the value I am trying to automate to help explain. The value returned would be based on the dates in E13:E53 and looking up the difference in corresponding tax schedule in I4:X7.
I've tried hlookup, but haven't been able to do so successfully. Any help would greatly appreciated. Thank you.
| Book1 | |||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
| 2 | Inputs | ||||||||||||||||||||||||
| 3 | Analysis Start Date | 1/1/2024 | TAX Schedule | ||||||||||||||||||||||
| 4 | FY | FY 2023 | FY 2024 | FY 2025 | FY 2026 | FY 2027 | FY 2028 | FY 2029 | FY 2030 | FY 2031 | FY 2032 | FY 2033 | FY 2034 | FY 2035 | FY 2036 | FY 2037 | |||||||||
| 5 | Base Tax Year | FY 2023 | Start Date | 7/1/2022 | 7/1/2023 | 7/1/2024 | 7/1/2025 | 7/1/2026 | 7/1/2027 | 7/1/2028 | 7/1/2029 | 7/1/2030 | 7/1/2031 | 7/1/2032 | 7/1/2033 | 7/1/2034 | 7/1/2035 | 7/1/2036 | |||||||
| 6 | Base Tax Rate | $8.00 | End Date | 6/30/2023 | 6/30/2024 | 6/30/2025 | 6/30/2026 | 6/30/2027 | 6/30/2028 | 6/30/2029 | 6/30/2030 | 6/30/2031 | 6/30/2032 | 6/30/2033 | 6/30/2034 | 6/30/2035 | 6/30/2036 | 6/30/2037 | |||||||
| 7 | Annual Increase | 3.00% | Annual Rate | $8.00 | $8.24 | $8.49 | $8.74 | $9.00 | $9.27 | $9.55 | $9.84 | $10.13 | $10.44 | $10.75 | $11.07 | $11.41 | $11.75 | $12.10 | |||||||
| 8 | |||||||||||||||||||||||||
| 9 | |||||||||||||||||||||||||
| 10 | |||||||||||||||||||||||||
| 11 | Manually entered formulas to illustrate desired output | ||||||||||||||||||||||||
| 12 | Dates | Delta (FY - Base Year) | |||||||||||||||||||||||
| 13 | Monthly table>>>>>> | 1/1/2024 | $0.02 | <<<Trying to take the difference of 2024 Annual Rate and 2023 (row 7). Convert to monthly amount so I divide by 12. Need help on a formula that automates value based on matching month to tax schedule | |||||||||||||||||||||
| 14 | Dates>>> Value in Column F would be based on date and corresponding value & date in tax schedule table. | 2/1/2024 | $0.02 | ||||||||||||||||||||||
| 15 | 3/1/2024 | $0.02 | |||||||||||||||||||||||
| 16 | 4/1/2024 | $0.02 | |||||||||||||||||||||||
| 17 | 5/1/2024 | $0.02 | |||||||||||||||||||||||
| 18 | 6/1/2024 | $0.02 | |||||||||||||||||||||||
| 19 | 7/1/2024 | $0.04 | <<<Trying to take the difference of FY 2025 Annual Rate and FY 2023 (row 7). Convert to monthly amount so I divide by 12 | ||||||||||||||||||||||
| 20 | 8/1/2024 | $0.04 | |||||||||||||||||||||||
| 21 | 9/1/2024 | $0.04 | |||||||||||||||||||||||
| 22 | 10/1/2024 | $0.04 | |||||||||||||||||||||||
| 23 | 11/1/2024 | $0.04 | |||||||||||||||||||||||
| 24 | 12/1/2024 | $0.04 | |||||||||||||||||||||||
| 25 | 1/1/2025 | $0.04 | |||||||||||||||||||||||
| 26 | 2/1/2025 | $0.04 | |||||||||||||||||||||||
| 27 | 3/1/2025 | $0.04 | |||||||||||||||||||||||
| 28 | 4/1/2025 | $0.04 | |||||||||||||||||||||||
| 29 | 5/1/2025 | $0.04 | |||||||||||||||||||||||
| 30 | 6/1/2025 | $0.04 | |||||||||||||||||||||||
| 31 | 7/1/2025 | $0.06 | <<<Trying to take the difference of FY 2026 Annual Rate and FY 2023. Convert to monthly amount so I divide by 12 | ||||||||||||||||||||||
| 32 | 8/1/2025 | $0.06 | |||||||||||||||||||||||
| 33 | 9/1/2025 | $0.06 | |||||||||||||||||||||||
| 34 | 10/1/2025 | $0.06 | |||||||||||||||||||||||
| 35 | 11/1/2025 | $0.06 | |||||||||||||||||||||||
| 36 | 12/1/2025 | $0.06 | |||||||||||||||||||||||
| 37 | 1/1/2026 | $0.06 | |||||||||||||||||||||||
| 38 | 2/1/2026 | $0.06 | |||||||||||||||||||||||
| 39 | 3/1/2026 | $0.06 | |||||||||||||||||||||||
| 40 | 4/1/2026 | $0.06 | |||||||||||||||||||||||
| 41 | 5/1/2026 | $0.06 | |||||||||||||||||||||||
| 42 | 6/1/2026 | $0.06 | |||||||||||||||||||||||
| 43 | 7/1/2026 | ^FORMULA^ | |||||||||||||||||||||||
| 44 | 8/1/2026 | ^FORMULA^ | |||||||||||||||||||||||
| 45 | 9/1/2026 | ^FORMULA^ | |||||||||||||||||||||||
| 46 | 10/1/2026 | ^FORMULA^ | |||||||||||||||||||||||
| 47 | 11/1/2026 | ^FORMULA^ | |||||||||||||||||||||||
| 48 | 12/1/2026 | ^FORMULA^ | |||||||||||||||||||||||
| 49 | 1/1/2027 | ^FORMULA^ | |||||||||||||||||||||||
| 50 | 2/1/2027 | ^FORMULA^ | |||||||||||||||||||||||
| 51 | 3/1/2027 | ^FORMULA^ | |||||||||||||||||||||||
| 52 | 4/1/2027 | ^FORMULA^ | |||||||||||||||||||||||
| 53 | 5/1/2027 | ^FORMULA^ | |||||||||||||||||||||||
| 54 | |||||||||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J4 | J4 | =F5 |
| K4:X4 | K4 | =J4+1 |
| J5 | J5 | =DATE($F$5-1,7,1) |
| K5:X5 | K5 | =+J6+1 |
| J6:X6 | J6 | =EDATE(J5,12)-1 |
| J7 | J7 | =$F$6 |
| K7:X7 | K7 | =J7*(1+$F$7) |
| F13 | F13 | =(K7-J7)/12 |
| F19 | F19 | =(L7-J7)/12 |
| F31 | F31 | =(M7-J7)/12 |
| E13 | E13 | =F3 |
| E14:E53 | E14 | =DATE(YEAR(E13)+0,MONTH(E13)+1,DAY(E13)+0) |
