i am sure i have asked this question before, many years ago but cannot find a trace. I have to phase savings across several financial years. For each item, I have Start and End Date and amount. So if i declare $200,000 savings on 30 September 2022 for a contract that runs until 30 June 2023 (Aussie Financial Year), all savings - $200,000 - would be recorded against the 2023 Fin Year. However, if the contract end date was 31 August 2023, the savings would be split across the years, with $100000 in each year column. what formula can i use to do this phasing? the table below uses calendar year where i need to use fiscal year July to June.
Start | End | Months | Amt | 2022 | 2023 | 2024 | 2025 | 2026 | |
26/08/2022 | 30/06/2023 | 10 | $ 53,568.40 | $ 26,784.20 | $ 32,141.04 | $ - | $ - | $ - | |
1/09/2022 | 30/06/2023 | 9 | $ 200,000.00 | $ 88,888.89 | $ 133,333.33 | $ - | $ - | $ - | |
1/01/2023 | 30/03/2026 | 39 | 500000 | $ - | $ 153,846.15 | $ 153,846.15 | $ 153,846.15 | $ 38,461.54 | |