Generate a Date Range and Daily Compound Interest in Excel
Question details
Generate a daily date range and calculate a running compound-interest balance for each date.

- 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.
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.
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.
Enter Date in A6 and Balance in B6.
In A7, enter =SEQUENCE(B2-B1+1,,B1,1). Select the spilled cells and use Home > Number Format to display them as dates.
In B7, enter =SCAN(B3,SEQUENCE(B2-B1+1),LAMBDA(balance,day,balance*(1+B4/365))). The results spill down alongside the dates.
Select the results in column B and choose a currency or number format from Home > Number Format.

Build the Schedule with Helper Columns
Use ordinary row-by-row formulas when dynamic array functions are unavailable.
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. 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. 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. Add the next day's formulas: Enter =A7+1 in A8 and =B7*(1+$B$4/365) in B8.
- 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.

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.




