Interest Calculator - Monthly Savings

[Cra5h]

Hi,

I have created an Interest Calculator to work out interest earned on a monthly savings account based on 1) Date of monthly deposits 2) Monthly Savings Amount and 3) Interest Rate.

It works fine with the exception of when the monthly date is after 28th Month - due to February or on 31st Month (due to all the months with only 30 days).

I have attached sheet - is there an easy way i can compensate as in practice the monthly amount would go in on the 1st of the subsequent month in these cases???

[Bernie Deitrick]

What formula are you trying to fix? Where is the error that you think you have?

[Cra5h]

Apologies Bernie i uploaded the wrong excel

I have now uploaded the correct one and you can see in row 38 where my error occurs due to the days in Feb

[Bernie Deitrick]

In cell B8:

To use the 28th if there is no 30th:

=IF(OR(AND(D8<$G$1,D8>D9),D8=$G$1),B7+$B$2,B7)


To use the 1st if there is no 30th in the previous month:

=IF(OR(AND(D7<$G$1,D8<D7),D8=$G$1),B7+$B$2,B7)

Copied down....

[Cra5h]

Thanks for your help Bernie - the 2nd one seems to work and cover all the months and on initial testing works great - really appreciate your help as it was making my head hurt!