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.

are. – DrMoishe Pippik Dec 1 '17 at 16:13