Calculation Issues

Generate a Date Range and Daily Compound Interest in Excel

Emma BrownEmma Brown Nov 18, 2026 871 views

Question details

Generate a daily date range and calculate a running compound-interest balance for each date.

Generate a Date Range and Daily Compound Interest in Excel
Product
Microsoft Excel for Microsoft 365
Device & OS
not provided
Scenario
A user has a start date, end date, starting balance, and annual interest rate and wants a daily schedule.
Observed behavior
The desired result is an inclusive list of dates with a corresponding compounded balance for each day.
Before you start

Enter the start date in B1, the end date in B2, the starting balance in B3, and the annual interest rate as a percentage in B4. Ensure B2 is not earlier than B1 and leave the output area below row 6 empty.

Solution 1Recommended

Use SEQUENCE and SCAN to Generate the Daily Schedule in Excel

Use dynamic array formulas in a version of Excel that supports SEQUENCE, SCAN, and LAMBDA.

This example applies interest on the start date and once on every following date, including the end date. It assumes a fixed annual rate divided by 365, with no deposits or withdrawals.

1
Add column headings

Enter Date in A6 and Balance in B6.

2
Generate the dates

In A7, enter =SEQUENCE(B2-B1+1,,B1,1). Select the spilled cells and use Home > Number Format to display them as dates.

3
Calculate daily balances

In B7, enter =SCAN(B3,SEQUENCE(B2-B1+1),LAMBDA(balance,day,balance*(1+B4/365))). The results spill down alongside the dates.

4
Format the balances

Select the results in column B and choose a currency or number format from Home > Number Format.

Use SEQUENCE and SCAN to Generate the Daily Schedule in Excel
Choose the right convention: Adjust the divisor and first-day calculation if your agreement uses a different day-count or compounding convention. Keep the spill ranges clear to avoid a #SPILL! error.
Calculate daily balances in WPS

Create a Daily Compound-Interest Schedule in WPS Spreadsheet

Use date and balance formulas in WPS Spreadsheet to build the same daily schedule without relying on SCAN.

  1. 1. Enter the inputs: Open WPS Spreadsheet and enter the start date in B1, end date in B2, starting balance in B3, and annual percentage rate in B4.
  2. 2. Set the first day's results: Enter =$B$1 in A7 and =$B$3*(1+$B$4/365) in B7. This applies interest on the start date.
  3. 3. Add the next day's formulas: Enter =A7+1 in A8 and =B7*(1+$B$4/365) in B8.
  4. 4. Fill and format the schedule: Select A8:B8 and drag the fill handle down until the date in column A matches B2. Use the number-format controls on the Home tab to format the dates and balances.
Calculate a running balance for each date with helper columnsOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and number formattingWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does the balance on the start date include interest?

Yes. The formulas apply one day's interest on the start date. If that date should show the unchanged starting balance, enter =B3 as the first balance and begin compounding on the next row.

What should I enter for the annual interest rate?

Enter the rate in B4 as a percentage, such as 5% for a five-percent annual rate. Entering 5 without percentage formatting would represent 500%.

Why does the formula divide the rate by 365?

The example uses a fixed annual rate divided by 365 for each daily period. Check your agreement and adjust the divisor or calculation if it uses another day-count convention.

What if Excel shows a #SPILL! error?

Clear any existing content from the cells below A7 or B7 where the dynamic array needs to expand, then check that the formula has enough room to return all dates and balances.

Can these formulas account for deposits or changing rates?

Not as written. Add columns for transactions or daily rates and modify the balance formula to apply them in the order required by your calculation.