I have been working to clean up a formula that converts a semi-annual cash flow to an annual fiscal year, is that the fiscal year ends, before the final payment of the calendar year. The formula that had been used is a monstrosity of Indirect, Address, Match, so shortening it would be a huge improvement. So here is an illustration of what I am trying to do:
Semi-Annual Cash Flow
A B C D
1 Date Principal Interest Total
2 4/21/2022 - -
3 11/1/2022 0 25,082.43 25,082.43
4 5/1/2023 23,762.30 23,762.30
5 11/1/2023 0 23,762.30 23,762.30
6 5/1/2024 23,762.30 23,762.30
7 11/1/2024 0 23,762.30 23,762.30
8
9 Fiscal Year Principal Interest Total
10 9/30/2022 20,902.02 20,902.02
11 9/30/2023 47,744.62 47,744.62
12 9/30/2024 47,524.60 47,524.60
For Simplicity purposes, let me focus on the 9/30/2023 calculation. The Interest total is a sum of the following three things: 1) 1/6 (30 days (9/30/22 to 11/1/22) / 180 days) of 11/1/22 interest payment, 2) the entire 5/1/23 interest payment and 3) 5/6 (150 days (5/1/23 to 9/30/23)/ 180 days) of 11/1/23 interest payment.
For now, I am using the following formula (line by line to show it more easily):
=+SUMPRODUCT((YEAR($F$2:$F$7)=(YEAR(F12)-1))*((MONTH($F$2:$F$7)=11)*($H$2:$H$7)*((DAYS360(EDATE(F12,-12),F3)-1)/180)))
+SUMPRODUCT((YEAR($F$2:$F$7)=YEAR(F12))*((MONTH($F$2:$F$7)=5)*($H$2:$H$7))*((DAYS360(F4,F5))/180))
+SUMPRODUCT((YEAR($F$2:$F$7)=YEAR(F12))*((MONTH($F$2:$F$7)=11)*($H$2:$H$7)*((DAYS360(F4,F12)+1)/180)))
This is already a huge improvement over what I had, but I was wondering if anyone had ever encountered this?
Thanks for any ideas!!!
Semi-Annual Cash Flow
A B C D
1 Date Principal Interest Total
2 4/21/2022 - -
3 11/1/2022 0 25,082.43 25,082.43
4 5/1/2023 23,762.30 23,762.30
5 11/1/2023 0 23,762.30 23,762.30
6 5/1/2024 23,762.30 23,762.30
7 11/1/2024 0 23,762.30 23,762.30
8
9 Fiscal Year Principal Interest Total
10 9/30/2022 20,902.02 20,902.02
11 9/30/2023 47,744.62 47,744.62
12 9/30/2024 47,524.60 47,524.60
For Simplicity purposes, let me focus on the 9/30/2023 calculation. The Interest total is a sum of the following three things: 1) 1/6 (30 days (9/30/22 to 11/1/22) / 180 days) of 11/1/22 interest payment, 2) the entire 5/1/23 interest payment and 3) 5/6 (150 days (5/1/23 to 9/30/23)/ 180 days) of 11/1/23 interest payment.
For now, I am using the following formula (line by line to show it more easily):
=+SUMPRODUCT((YEAR($F$2:$F$7)=(YEAR(F12)-1))*((MONTH($F$2:$F$7)=11)*($H$2:$H$7)*((DAYS360(EDATE(F12,-12),F3)-1)/180)))
+SUMPRODUCT((YEAR($F$2:$F$7)=YEAR(F12))*((MONTH($F$2:$F$7)=5)*($H$2:$H$7))*((DAYS360(F4,F5))/180))
+SUMPRODUCT((YEAR($F$2:$F$7)=YEAR(F12))*((MONTH($F$2:$F$7)=11)*($H$2:$H$7)*((DAYS360(F4,F12)+1)/180)))
This is already a huge improvement over what I had, but I was wondering if anyone had ever encountered this?
Thanks for any ideas!!!