• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Semi-Annual Cash Flower to Fiscal Year

Bercilak

New Member
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!!!
 
I use 365 so routinely employ Lambda functions. The sort of formula I might write to calculate the temporal overlaps between to series of time intervals is
Code:
= Allocateλ(FYSrt,FYEnd,semiSrt,semiEnd)
where the name 'Allocateλ' is defined to be
Code:
= LAMBDA(FYSrt, FYEnd, semiSrt, semiEnd,
     MAKEARRAY(ROWS(semiSrt), ROWS(FYSrt),
        LAMBDA(sa, fy,
            LET(
                laterSrt,   MAX(INDEX(semiSrt, sa), INDEX(FYSrt, fy)),
                earlierEnd, MIN(INDEX(semiEnd, sa), INDEX(FYEnd, fy)),
                duration,   earlierEnd - laterSrt,
                IF(duration > 0, duration, 0)
            )
        )
     )
  )
78162
 

Attachments

  • Convert to FY.xlsx
    16.2 KB · Views: 6
Back
Top