Table of Contents
Calculating daily compound interest in Excel is a simple process that can be done using the PMT, FV, and NPER functions. First, the user inputs the initial principal amount, the annual interest rate, and the number of days for which interest will be compounded. Then, the PMT function is used to calculate the amount of interest that will be compounded for each day, followed by the FV function to calculate the final value of the principal. Finally, the NPER function is used to calculate the number of periods for which the interest will be compounded.
We can use the following formula to find the ending value of some investment after a certain amount of time:
A = P(1 + r/n)nt
where:
- A: Final Amount
- P: Initial Principal
- r: Annual Interest Rate
- n: Number of compounding periods per year
- t: Number of years
If the investment is compounded daily, then we can use 365 for n:
A = P(1 + r/365)365t
The following example shows how to use this formula in Excel to calculate the ending value of some investment that has been compounded daily.
Example: Daily Compound Interest Formula in Excel
Suppose we invest $5,000 into an investment that compounds at a rate of 6% annually. Assume the investment compounds on a daily basis.
The following screenshot shows how to use the compound interest formula in Excel to calculate the ending value of this investment after 10 years:
This investment will be worth $9,110.14 after 10 years.
The following screenshot shows how to calculate the ending investment after each year during the 10-year period.
Note: Column F shows the formula we used in each corresponding cell in Column E:
From the output we can see:
- At the end of year 1, the investment is worth $5,309.16.
- At the end of year 2, the investment is worth $5,637.43.
- At the end of year 3, the investment is worth $5,986.00.
To visualize the investment growth over time, simply highlight the cells in the range E2:E11, then click the Insert tab along the top ribbon, then click the 2-D Column Chart option with the Charts group:
The x-axis shows the year and the height of the bars represent the investment value at the end of each year.