Not all banks treat leap years the same, when calculating interest on deposits. Many financial institutions use basically the same method (or a close variation) I used in this formula. Check with your bank before relying on this method.

The method I am referring to is:

Interest is compounded daily. The balance on any given day is a combination of the previous days balance and the interest earned on the previous days balance. The rate used to calculate the previous days interest is the APR divided 365, assuming the previous day fell in a normal year, and 366 if the previous day fell in a leap year. This process continues until the funds are withdrawn. This means every day within a leap year earns 366th of the stated APR. This makes sense, as long as the funds are held for the full year, and receive the interest for the extra day, February 29th.

Now to the formula used to count the number of leap year days and non leap days between two dates. Download the workbook here. The formula I came up with uses an array, given the first and last date, plus the **APY**. Remember to enter the formula with a Ctrl-Shift-Enter to create the array.

={PRODUCT(1+(365*((1+C10)^(1/365))-365)/((–(MONTH(DATE(YEAR(ROW(INDIRECT(C8&”:”&(C9-1)))),2,29)))=2)+365))*C12}

The first step in the formula is to convert the APY to an APR. The APY in this case is in cell C10.

APR = 365*((1+APY)^(1/365))-365

Next, a formula to determining which dates are within a leap year:

=MONTH(DATE(YEAR(C9),2,29))=2

Cell C9 contains a date. After the YEAR function determines the year of the date, the DATE function combines the month, day, and year to look like this: DATE(2012,2,29) or 2/29/2012. The MONTH function of this date is a 2 so the formula will return 2=2 or TRUE. If the DATE function was to evaluate this DATE(2013,2,29) it would not recognize 2/29/2013 as a date, but would evaluate it as 3/1/2013 instead. Then, the MONTH function returns 3, which makes the formula 3=2, which is FALSE. So, this formula results in either a **TRUE** if a leap year day or **FALSE** if not.

The cell references C8 and C9 are the starting and ending dates. Excel recognizes the dates as numbers. The & signs combine the two date numbers, with the “:” separating the two. This appears to be a range, surrounded by two apostrophes or a text string. The INDIRECT function returns the reference specified by a text string. Then the ROW function will interpret the range as row numbers. The row numbers result in an array of date numbers, so we can now add the leap year formula to evaluate each date for leap year days. Notice that I subtracted 1 from the last date (cell C9). When calculating interest, the last day does not counted. The last date does not earn interest.

The array goes through each date to determine which are leap days. Each date results in a **TRUE** for a leap day and a **FALSE** for a non-leap day. Next, the double dash marks (–) convert each **TRUE** to the number 1 and **FALSE** to the number 0. Now I add the number 365 to each number 1 or 2 to get a 366 for leap days and a 365 for non-leap days.

Finally, the APR is divided by each day (either 366 or 365) and 1 is added to each. The results are then multiplied by each other using the PRODUCT function. Then multiplied by the present value ($1,000,000 in my example).

Use the Evaluate Formula option to step through the formula.

The on the “Proof” sheet I have manually constructed a number of rows, representing each date, along with column B to determine if each date is a leap year or not. Column E is a running total of interest. Cell G6 is linked to the formula to compare the results in cell E3.

Romans 1:17, “For in it the righteousness of God is revealed from faith for faith, as it is written, ‘The righteous shall live by faith.’”

Hi Don,

Thanks for sharing great excel models. Any chance you can do one on VADM and Z bond?

Thank you.

Stella,

Thanks for the comment.

I would have to build a model that would handle all the tranches for each CMO to get the cash flow of the Z bond. I think we will have to leave that to Bloomberg.

Don