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

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.

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