how to compute the interest during years that have leap years within the period concerned.

Robert Salonikios

New Member
Joined
Jun 23, 2005
Messages
13
I am looking for a financial/mathematical formula (or array) whereby the calculation of interest varies in case the base year happens to be a leap year (within the period considered).
I am presenting my query through the attached excel worksheet whereby the variable data to be inserted are in cells A1 to D1.
The idea is to make sure that if there is a leap year within the time period (cells C1:D1) then the computations during this period are based on 366 days and not 365.
My attached excel is limited to a period (D1 minus C1) of 8 years otherwise I would have to expand my formulae beyond column O. This is a limiting factor but this at least serves my current needs.
I would appreciate any assistance to make my process simpler through a formula (array) or a macro.
Thank you.
 

Attachments

  • Capture.JPG
    Capture.JPG
    65.3 KB · Views: 44

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Not sure if this will help but this: datediff("d","01/01/2020","01/01/2021")
returns 366 whereas this: datediff("d","01/01/2021","01/01/2022")
returns 365
 
Upvote 0
Worksheet Formula solution may not be a good choice, it requires multi columns helpers (like your sample), complex combination formula (hard to understand and mainternance)
I suggest UDF solution (User define formula).
Is it OK for you?
 
Upvote 0
UDF solution:
With A1 = amount; B1 = rate, C1 = start date, D1 = end date,
E1 = interest(A1,B1,C1,D1)
with interest is UDF. Follow below steps:

1) Alt-F11 to open Visual Basic window

2) Insert/ Module/ Module1

3) paste below code in edit window (right hand window):
VBA Code:
Option Explicit
Function Interest(ByVal Amt As Range, ByVal Rate As Range, ByVal StartD As Range, ByVal EndD As Range)
Dim i As Double, rSum As Double
    For i = StartD.Value2 To EndD.Value2
        rSum = rSum + Rate / (365 + IIf((Year(i) Mod 4) = 0, 1, 0)) * Amt ' running sum = running sum + 2%/(365 or 366)* $1000
    Next
Interest = rSum
End Function

4) Close Visual Basic window

5) in E1 type = interest(A1,B1,C1,D1)

1652758469596.png
 
Upvote 0
Solution
Upvote 0
UDF solution:
With A1 = amount; B1 = rate, C1 = start date, D1 = end date,
E1 = interest(A1,B1,C1,D1)
with interest is UDF. Follow below steps:

1) Alt-F11 to open Visual Basic window

2) Insert/ Module/ Module1

3) paste below code in edit window (right hand window):
VBA Code:
Option Explicit
Function Interest(ByVal Amt As Range, ByVal Rate As Range, ByVal StartD As Range, ByVal EndD As Range)
Dim i As Double, rSum As Double
    For i = StartD.Value2 To EndD.Value2
        rSum = rSum + Rate / (365 + IIf((Year(i) Mod 4) = 0, 1, 0)) * Amt ' running sum = running sum + 2%/(365 or 366)* $1000
    Next
Interest = rSum
End Function

4) Close Visual Basic window

5) in E1 type = interest(A1,B1,C1,D1)

View attachment 64815

THAT WORKED PERFRCT. THANK YOU SO SO MUCH.
 
Upvote 0
Hello Robert
I am glad that you received the answer that you required.

Does the UDF provide the same answer as the spreadsheet shown with the picture that you provided.
What is the total interest for say 1,000,000?
 
Upvote 0
Hello Robert
I am glad that you received the answer that you required.

Does the UDF provide the same answer as the spreadsheet shown with the picture that you provided.
What is the total interest for say 1,000,000?

Hello Dave,
Yes it perfectly does.
I am now going to insert that code into my pre-existing VBA macro ( which runs other computations) and see the overall result.
I am glad my query is resolved which makes me now interested to read about UDF.
I would appreciate receiving links or sources to learn about building UDFs.
Thank you.
 
Upvote 0

Dear bebo021999

I need to make a small modification to my formula whereby I need to increase the start date by one day within the formula without changing the actual value in cell C1.
In other words, while keeping all the cells with their initial values, I need to modify the below formula from

E1 type = interest(A1,B1,C1,D1)
to become
E1 type = interest(A1,B1,(C1+1),D1) but doing that results in an error message.

How can I make the formula accept adding a day to C1?

Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top