How To: Calculate Compound Interest in Excel

Compound interest is very commonly in Finance and yet there is no simple and usable function built into Excel. So I thought of making this function I have been using for almost 10 years (improved over time) to be publicly available.

Download Addin

You can use this from your spreadsheets and the function signature is pretty self explanatory.


CompoundInterest(ByVal principle As Double, ByVal rate As Double, ByVal startdate As Date, ByVal enddate As Date, Optional ByVal split As Boolean) as Double

The last argument split if passed as true, will use linear method for intra-year interest. This function can only be used for compounding Annually. You are free to modify it to work with other terms.

Download this addin and install it. You should be able the use the function =CompoundInterest.

And yes.. you can use XIRR function to validate these interest numbers. And this function only returns the interest part of it. To match with XIRR you need to add principal to it.

For those fellow developers who is interested just in code:

Public Function CompoundInterest(ByVal principle As Double, ByVal rate As Double, ByVal startdate As Date, ByVal enddate As Date, Optional ByVal split As Boolean)
    Dim interest As Double
    Dim days As Long
    Dim leapcount As Long
    Dim fullperiods As Long
    Dim remdays As Long
    
    If IsMissing(split) Then split = False
    
    days = DateDiff("d", startdate, enddate)
    
    Dim yr As Long
    leapcount = 0
    
    If days > 365 Then
        For yr = Year(startdate) To Year(enddate)
            If isLeapYear(yr) Then
                If Not ((yr = Year(startdate) And Month(startdate) > 2) Or (yr = Year(enddate) And enddate < ("02/29/" & yr))) Then
                leapcount = leapcount + 1
                End If
            End If
        Next
    End If
    
    days = days - leapcount
    
    If split Then
        fullperiods = Application.WorksheetFunction.Quotient(days, 365) ' Abs(days / 365)
        remdays = days Mod 365
        Dim interestfull As Double
        interestfull = principle * ((1# + rate) ^ fullperiods)
        
        Dim remainingpartial As Double
        remainingpartial = interestfull * (1# + (rate * (remdays / 365#)))
        interest = remainingpartial - principle
    Else
        interest = (principle * ((1# + rate) ^ (days / 365#))) - principle
    End If
    CompoundInterest = interest
End Function


Public Function isLeapYear(yr As Long) As Boolean
    isLeapYear = False
    If (yr Mod 400) = 0 Then
        isLeapYear = True
    ElseIf (yr Mod 100) = 0 Then
        isLeapYear = False
    ElseIf (yr Mod 4) = 0 Then
        isLeapYear = True
    End If
End Function

Happy Compounding...