I am working on an Excel project where I need to calculate interest on a late invoice payment based on monthly interest rates. However, I am struggling to achieve this using Excel formulas and would like to know if it can be done using VBA.

Details:

Invoice Due Date: 24/08/2022

Actual Payment Date: 14/07/2023

Invoice Amount: 14,976.43

Days Outstanding: 324 days (14/07/2023 - 24/08/2022)

The interest rates are per annum but charged monthly, as outlined in a provided table. My challenge is to split the 324 days into months, apply the corresponding monthly interest rates, calculate the interest for each month, and sum them together.

This is just a one line example as I have a whole worksheet with over 100 rows that need to be calculated.

I would greatly appreciate any guidance or a VBA solution to automate this process.

Excel Summary

I've tried using normal excel formules but I cant get it to be dynamic

I am working on an Excel project where I need to calculate interest on a late invoice payment based on monthly interest rates. However, I am struggling to achieve this using Excel formulas and would like to know if it can be done using VBA.

Details:

Invoice Due Date: 24/08/2022

Actual Payment Date: 14/07/2023

Invoice Amount: 14,976.43

Days Outstanding: 324 days (14/07/2023 - 24/08/2022)

The interest rates are per annum but charged monthly, as outlined in a provided table. My challenge is to split the 324 days into months, apply the corresponding monthly interest rates, calculate the interest for each month, and sum them together.

This is just a one line example as I have a whole worksheet with over 100 rows that need to be calculated.

I would greatly appreciate any guidance or a VBA solution to automate this process.

Excel Summary

I've tried using normal excel formules but I cant get it to be dynamic

• excel

• 3 Please edit your question to include what you have tried; how that didn't work, and your expected output from your data. You'll also get more people to look at the problem if you present your data (the interest rate table) as text which can be copy/pasted. Formulas, by nature are dynamic so what do you mean by that? – Ron Rosenfeld Commented Mar 17 at 11:11
• 1 Also need some info on how calculate fraction month? (by day or total month or no count) – Black cat Commented Mar 17 at 13:05

1 Answer 1

Try:

=LET(InvDt,$B$1, PmtDt,$B$3, Bal,$B$5, Rate_Years, A8:A31, Rate_Months, B8:B31, RateVals, C8:C31,
RateKeys,SCAN(0,Rate_Years,LAMBDA(a,v,IF(v<>"",v,a)))*100 + MONTH(DATEVALUE("1-" & Rate_Months)),
ActMonths,LET(Months, EDATE(EOMONTH(InvDt,-1)+1,SEQUENCE(DATEDIF(InvDt,PmtDt,"M")+2,1,0,1)), FILTER(Months,Months <= PmtDt)),
Strts,IF(ActMonths < InvDt,InvDt,ActMonths),
Ends, IF(EOMONTH(ActMonths,0) > PmtDt,PmtDt,EOMONTH(ActMonths,0)),
DaysInPerd,Ends-Strts+1,
DaysInYear,DAY(DATE(YEAR(Strts),3,1)-1)-28+365,
MthInt, DaysInPerd / DaysInYear *
       BYROW(ActMonths,
             LAMBDA(r,FILTER(RateVals, RateKeys=YEAR(r)*100+MONTH(r)))),
Bal * PRODUCT(1+MthInt))

This is compounding monthly (at month ends), both the InvoiceDate and the PaymentDate are inclusive.
I hope it is self-explanatory.
The way you are holding the monthly rates is complicating things; it could be easier to hold each monthly interest rate against, say, the first of each month (a real date).