Interest calculation based on dates

System

This discussion was created from comments split from: Help with a Currency Conversion.

laurenceharper

Hi

I have another issue and wonder whether you could make a suggestion from a solution.

I will first give you some background... with some simplified numbers to make things easy explain. We make interest payments to investors on a Quarterly basis. It is a fixed term of 24 months split into quarters, lets imagine 1% interest payment per month x 3 to give you the quarterly interest payment on the investment. The quarters per year are defined so depending on when an investor invests and change the first and last payments of their 24 month cycle. i.e:

2021 Q1 = 2/1/21 to 1/4/21

2021 Q2 = 2/4/21 to 1/7/21

2021 Q3 = 2/7/21 to 1/10/21

2021 Q4 = 2/10/21 to 1/1/22

Therefore if an investor invested mid quarter depending of the date, their first interest payment could be 1% x 1 month, 1% x 2 months or 1% x 3 months. In the case of first interest payment being 1 or 2 months, the final interest payment would need to equal the total 24 months.

See below a snap shot of what I have to date.

Also, depending upon the funds received date, there would be a formula in year and quarter which would automatically appear.