So I have an interesting problem - I need to calculate the compounding interest on an amount. Easy. It needs to be done daily. Easy. It needs to properly factor in leap years. Not easy.

I've read over Excel formula to convert per-annum interest rate to compounding daily and weekly rates , which is a good primer on interest and calculating interest. I have the current formula:

=InitialAmount*(1+0.1/365)^(TODAY()-EOMONTH(StartDate,0))-F8

Please note- Start Date is given to me as a date within the month, however interest only starts calculating at the end of the month.

Same difference. However, when I hit a leap year, it breaks down by pennies. I don't like being off by pennies - How can I have the daily compounding interest properly account for leap years?

Thank you

Edit: To be completely clear, I need to be able to figure out interest amounts that cross both leap years and non-leap years - For example, if I start calculating interest on 21st July 2015, the rest of 2015 is regular interest, 2016 is leap year interest, and 2017 is back to regular interest.

Also note that the stated interest rate is per year, regardless of the number of days in the year. So the daily interest rate will be different for leap years.

Since there is still some confusion:

Example:

1st day is December 31st, 2015. Initial value is 100,000.00. Interest rate is 20% annually. 100,000.15 = 100,000*(1+.2/365)^(1/365).

2nd day is January 1st, 2016. Initial value is now 100,000.15. Interest rate is still 20% annually. 100000.30 = 100,000.15*(1+.2/366)^(1/366).

3rd day is January 2nd. 100000.30 = 100,000.15*(1+.2/366)^(1/366).

~368th day is January 1st. The multiplier is now back to (1+.2/365)^(1/365).

I'm looking for a formula that flawlessly jumps to and from leap years while calculating the compounding interest properly.

(100000*(1+.2/365)^(1/365))*(1+.2/366)^(1/366) - is the 1st + the 2nd day combined.

Edit 2: The current state of the problem: The solution looks something like this:

=PV*(1+rate/365)^DaysNotInALeapYear*(1+rate/366)^DaysInALeapYear

Where rate is the annual rate.

  • It looks like you have an interest rate that is stated as an annual rate, but it is supposed to be compounded every day. This makes no sense mathematically (is the daily interest rate lower during leap years than other years?), but I believe the world of finance has its own definition of what this means. You may need to ask on a financial website what the calculation should be. – Blackwood Nov 30 '17 at 23:11
  • From the financial world: 10% daily means that it's 10% per year, and divided by the number of days. So yes, in leap years the per-day interest rate changes. That part is no problem - it's writing the formula to handle it that I'm struggling with. – Selkie Dec 1 '17 at 0:50
  • Tell me do you want the Final Amount or Daily Amount. – Rajesh S Dec 1 '17 at 7:44
  • Final amount - it doesn't matter what the amount is daily, I need the final compounding amount – Selkie Dec 1 '17 at 16:07
  • A minor note: beware of leap-year corrections for the hundredth and thousandth years - hundreds are not LY, but thousands are. – DrMoishe Pippik Dec 1 '17 at 16:13

I believe the below screenshot of a spreadsheet will tell you what it takes to do this. Column B gives the formula, column C gives the value. There are several steps here:

  1. Determine how many days there are from the start date to the end date.
  2. Determine how many years there are from the beginning of the start year to the end of the end year
  3. Determine how many days there are in those years
  4. Subtract 365*number of years: the difference is the number of leap years
  5. Determine how many days there are in the start year and the end year: this determines whether they are a leap year
  6. Determine how many leap days there are in the total period from start date to end date. These accrue at rate/366
  7. The total number of days minus the number of leap days accrue at rate/365
  8. Now you have what you need to compute the number you were after

It is possible that there is a mistake of 1 day at either end of my calculations - the Excel formula =DAYS(stopDate, startDate) returns 1 for consecutive days; I'm not sure if a loan that is opened on Monday and paid on Tuesday should incur 1 or 2 days of interest. But that's something you can probably figure out.

Let me know if this makes sense!

enter image description here

PS1:

You could probably do this as a single monster equation, but it would be very, very messy. Some simplification might be possible if you don't calculate numNormalDays separately (B17) but instead computed

=(1+rate/365)^totalDays*(1-rate*365/366)^leapDays

since leapDays<<totalDays, that approximation should work (and then you don't have to use the result of B16 twice, which would double the size of your equation if you tried to do it in a single line.

As you can see, in the example given the difference between the two methods is in the 8th significant digit. Not sure if that is enough to have conniptions over...

PS2:

It would be much better to hide all this work in a VBA function - it would only need to be properly validated once, and then your spreadsheet can use it everywhere. It would actually be much less prone to errors in use (since there are many ways that copy-pasting a monster equation can and will go wrong... and it would be so hard to troubleshoot).

The VBA function might look like this (very close to the method used in the spreadsheet, some differences in implementation):

Option Explicit

Function compoundInterestLeap(startDate, endDate, rate)
' compute the interest that accrues from the end of the month that includes startDate, to endDate
' taking account of the fact that interest accrues more slowly in leap years (1/366 th per day).
' needs error checking?

Dim startYear, endYear, totalYears, leapYears, totalDays, leapDays, normalDays
Dim missingDaysFirst, missingDaysLast, totalYearDays
Dim yearOneIsLeap, yearNisLeap As Boolean
Dim eom As Date

eom = Application.WorksheetFunction.EoMonth(startDate, 0)

startYear = year(startDate)
endYear = year(endDate)
totalYears = endYear - startYear + 1
With Application.WorksheetFunction
    totalDays = .Days(endDate, eom)
    totalYearDays = .Days(DateSerial(endYear + 1, 1, 1), DateSerial(startYear, 1, 1))
    missingDaysFirst = .Days(eom, DateSerial(startYear, 1, 1))
    missingDaysLast = .Days(endDate, DateSerial(endYear, 12, 31))
End With

leapYears = totalYearDays - totalYears * 365

leapDays = leapYears * 366
If isLeapYear(startYear) Then
    leapDays = leapDays - missingDaysFirst
End If

If isLeapYear(endYear) Then
    leapDays = leapDays - missingDaysLast
End If

normalDays = totalDays - leapDays

compoundInterestLeap = (1 + rate / 365) ^ normalDays * (1 + rate / 366) ^ leapDays

End Function

Function isLeapYear(year)
' return True if the year passed as an argument is a leap year
' no error checking...
If Application.WorksheetFunction.Days(DateSerial(year, 12, 31), DateSerial(year - 1, 12, 31)) = 366 Then
    isLeapYear = True
Else
    isLeapYear = False
End If

End Function
up vote 1 down vote accepted

Alrighty, this took quite awhile, and the solution is a complete bear. Credit to @Floris for helping me work this out.

The question calls for everything to end up in a single formula, but I'm going to break everything down by the pieces, then bring them back together.

I'm going to gloss over some of the basics of compounding interest. For primers, see https://www.investopedia.com/terms/c/compoundinterest.asp for the concepts, and Excel formula to convert per-annum interest rate to compounding daily and weekly rates for simple examples of the math behind it in excel.

We'll start with our standard interest formula:

FV = PV(1+r/n)^(t*n)

, where t is in years. This means daily compounding (for a normal year) is

FV = PV*(1+r/365)^DaysNotLeap

And our compounding interest for a leap year is

FV = PV*(1+R/366)^DaysInLeapYears

We're mathematically allowed to bring these two together like so:

FV= PV*(1+r/365)^DaysNotLeap*(1+R/366)^DaysInLeapYears

Now the trick is to figure out, how many days are in leap years and how many days are normal days between two dates. This is fairly complicated, but once we have one, we have the other.

I'm going to call them StartDate and EndDate.

How many days are between the two?

DaysDifference=EndDate-StartDate+1
DaysDifference=Days(EndDate,StartDate)+1

These two formulas are the same thing, just written a bit differently. One uses Excel's built-in days function, the other is simple subtraction.

Why the +1? If I start on Jan 2nd, and end on Jan 3rd, I have the 2nd and the 3rd. But if I subtract the dates, I only get 1. Need to add one back in to compensate.

That was easy. How many leap-years are between the two, including if we start on a leap year, end on a leap year, or completely skip over a leap year? We can do this by calculating how many days we'd expect between the two years versus how many days we actually have between the two years.

DaysInYearsBetween=DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1))

Days we actually have

Days we'd expect to have: First we calculate how many years there are between the two days:

YearsBetween=YEAR(enddate)-YEAR(startdate)+1

Then we multiple by 365

ExpectedDaysBetween=YearsBetween*365

Full expansion of all of the formulas will occur at the final step.

So how many leap-years are involved? Let's subtract our two numbers:

LeapYearsInvolved = DaysInYearsBetween - ExpectedDaysBetween

This works because each leap year adds one extra day, so the number of extra days we have is the number of leap-years involved. Great!

We're going to need to know if a final or an end year is a leap year. This can be done with a pair of tests, testing in a similar fashion:

IsStartDateLeap=IF(DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366,TRUE,FALSE)

This is being very explicit, and showing everything needed. If you're a bit shakey with your excel, I recommend the above. You can shorten it to:

IsStartDateLeap=DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366

And the pair:

IsEndDateLeap=DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366

This gives us a fairly simple true/false if our starting or ending year is leap, which we can use to help calculate where those days belong.

Next, we need the number of days in the year not used on both our start and our end dates. For example, if we started on Jan 27th, there are 26 days that year we didn't use.

UnusedStartDays=startdate - DATE(YEAR(startdate)-1,12,31)

UnusedEndDays=DATE(YEAR(enddate)+1,1,1)-enddate

So now we have all of the pieces - let's bring them together. Leap days - Long version:

DaysInLeapYear=LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays)

Leap Days - Short Version

DaysInLeapYear=LeapYearsInvolved*366-IsStartDateLeap*UnusedStartDays-IsEndDateLeap*UnusedEndDays

Please note in Excel 2010 and earlier you may need to turn it into the format of (--IsStartDateLeap) to force the conversion from a boolean to a number.

DaysNotLeap=DaysDifference-DaysInLeapYear

As mentioned earlier, if we know one, we know both.

We have all of the pieces of the puzzle. Time to put them together via simple substitution. I won't be going over all the the details of the substitution, just posting in various stages.

FV= PV*(1+r/365)^(DaysDifference-DaysInLeapYear)*(1+R/366)^DaysInLeapYears

.

FV= PV*(1+r/365)^(DaysDifference-(LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays)))*(1+R/366)^(LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays))

.

FV= PV*(1+r/365)^(DaysDifference-(LeapYearsInvolved*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^(LeapYearsInvolved*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

.

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DaysInYearsBetween - ExpectedDaysBetween)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DaysInYearsBetween - ExpectedDaysBetween)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

.

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - YearsBetween*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - YearsBetween*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

And we finally get:

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - (YEAR(enddate)-YEAR(startdate)+1)*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - (YEAR(enddate)-YEAR(startdate)+1)*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

And there we go. How to calculate the daily compounding interest between two dates, adjusted for leap years.

For the question "Isn't it pretty close together" - yes they are. On a 10,000 initial value, over 8 years from April 24th 2008 to Feb 2nd 2016, Compounding "properly" gets us a value of $14,753.70, while compounding "improperly" gets us a value of $14,757.28.

Finally, I would like to suggest you the method used by Financial institutions. This method first counts number of days for Non Leap year and Leap year days. Then Calculates the interest. It can be calculated with a single Formula or using two separate Formulas.

My solution has two formula.

enter image description here

For the Regular days (31-01/2016 - 12-11-2015) = 80 days (Cell B589).

For Leap Year days = 29 days (Cell B590).

Formula for Regular Year =(((1+0.055)^(B589/365)-1)*B584)

Formula for Leap Year =(((1+0.055)^(B590/366)-1)*B584)

Then get the Total amount is, 160.55.

I do hope you find this a useful solution.

  • It's very nearly there! The only thing left to figure out is automatically calculating the days in a leap year and a regular year - you have it manually calculated for half, and a very narrow application for the other half, but I agree, this method will work – Selkie Dec 8 '17 at 22:13
  • @Selkie, YES it's working and let me say, I tried a single Formula but was unable to count days as well as CI. To count the days you can use DATEDIF, purposely I've not shown it here coz, my focus was to find the technically best formula for CI. – Rajesh S Dec 9 '17 at 6:49

The Formula you have used has something missing in part 2.

Formula which can be used to calculate Daily Compound interest is,

=Principal Amount*((1+Annual Int Rate/365)^(Investment Years*365)))

Now, the very first issue is what should be the Formula for the Leap year?

It's simple, only one correction is required, instead of 365, use 366, since Leap year has 29 days in February. So the formula is,

=Principal Amount*((1+Annual Int. Rate/366)^(Investment Years*366)))

Now let me put some value in the Formula.

Principal Amount = 10000.

Annual Interest = 10% .

Investment Years = 5 .

NB: For the Leap Year Formula produce 16,486.09 .

Since we are calculating the Future value so it can be calculated at the time of Loan given, like on Day 1.

Now returning to your Formula. In the second part you have written,

(TODAY()-EOMONTH(StartDate,0))

If simply put some value in that.

Where Start Date is 05/01/2017, Formula is producing 304. Means you want to find Compound Interest of 304 days on today (i.e. 01/12/2017).

For this you can use this simple formula,

= Principle Amt*(1+Inst. rate)^ Nu. Of Days

Hope this help you, in case if my Solution differs just drop a Comment, will help me to fix the issue in better way.

  • Your solution works in a leap year, no problem. It doesn't work on non-leap years. My issue is writing a single formula that both accomodates leap years, non-leap years, and situations where I'm partly in a leap year, and partly not. For example. if my interest started on 5th July 2015, and I need to calculate to today, the rest of 2015 is a non-leap year, 2016 is a leap year, and 2017 is not a leap year. – Selkie Dec 1 '17 at 16:05
  • Since, in original post the query was precise around the leap year only so that I suggested the solution accordingly. Okay no problem soon I'll post the Formula will First check the Years are Leap or not, and calculate the Future Value. – Rajesh S Dec 2 '17 at 8:19

Considering your issue with LEAP YEAR, I would like to show you, that how Excel considers it while calculating Compound interest.

Check the Screen Shot.

enter image description here

For better understanding I've taken TWO samples, first is for Regular second for LEAP year, and I've used two different Formula.

Check the Loan date, has Year 2016 was Leap year, Compounding in Year 2017.

Next to it has Year 2015 as Loan date & Year 2016 Compounding Year (is Leap Year).

In next Row you can find 2 similar values(10,550.00) First for Regular year Second for Leap Year.

The formula I've used are,

=E542*(1+0.055)^ROUNDDOWN((E545-E544)/365,0)

=E542*(1+0.055)^ROUNDDOWN((E545-E544)/366,0)

NB:Cell E542 has Principal amount. E545 is Calculation Date & E544 is Loan given Date.

in the last row again you find two similar amounts & the Formula I've used are,

=E542*(1+5.5%/365)^365

=E542*(1+5.5%/366)^366

Now the main issue how to get involve the LEAP YEAR. For this I've used Formula to determine the Leap year is,

=IF(MOD(YEAR(E545), 4) = 0, "Leap", "Regular")

So the conclusion is, first test whether the year is Leap or not then apply the formula to count compound interest. And the Formula is,,

=IF(MOD(YEAR(F545), 4) = 0, E542*(1+0.055)^ROUNDDOWN((F545-F544)/366,0), E542*(1+0.055)^ROUNDDOWN((F545-F544)/365,0))

This is the best Solution I found to calculate CI considering the LEAP year.

Hope you find it useful, in case if again it differs just write.

  • Sure. This properly switches between leap years and non-leap years. It doesn't properly calculate the compounding between the two - your formula is correct for a single day snapshot calculation. – Selkie Dec 3 '17 at 14:35
  • Something is missing in between !! BCoz I've tried what OP described. The Formula what I've used in my second post is one of the popular practices in day to day working. If u need I can suggest you Daily Calculation in simplest form. – Rajesh S Dec 4 '17 at 10:30

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.