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.
And by the end of year 10, the investment is worth $9,110.14.
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Find the Antilog of Values in Excel
How to Solve a System of Equations in Excel
How to Calculate a Five Number Summary in Excel
Perfect explanation and formula. Thank you!
This sheet is not answering the question I was seeking an answer for – it gives ANNUAL compound interest result.
My ISA is (approx) £100,000 @ 4.07%, interest calculated daily, credited monthly.
The 365 days (366 in 2024) are thus broken into 28 (29) 30 or 31 interest payment periods, plus 20 and 11 (days of capital sum change)
Initial sum was £20,000, followed 20 days later by £80,000 transfer
(So first year 2023/24 has £20k accruing interest for the full 366 days (leap year), 100k for 236 days.
The second year, the full £100k (plus 1st vear interest) gains interest for 365 days.
I’m a novice, “O” level basic maths 65 years ago (I’m 81, no stats grounding) – what’s the tweak, please, to get my monthly period interest figures ?
Correction to my post – TYPO – the first year, full £100k is only 346 days (not 236 !!)