excel - VBA calculation of Interest between a range of dates - Stack Overflow

admin管理员组

文章数量:1388089

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

Share Improve this question Follow edited Mar 17 at 10:36 Collins asked Mar 17 at 10:34 CollinsCollins 111 bronze badge 2

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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).

本文标签： excelVBA calculation of Interest between a range of datesStack Overflow