For a single lump sum, enter =P*(1+r/m)^(m*t) in Excel. Here, P is the starting principal, r is the annual rate, m is the number of compounding periods per year, and t is the number of years.
For example, $1,000 invested at a nominal 5% annual rate compounded monthly for 10 years is calculated with =1000*(1+5%/12)^(12*10), producing approximately $1,647.01. The interest earned is approximately $647.01.
As an Amazon Associate I earn from qualifying purchases.
Excel’s FV function produces the same result and is more convenient when you have regular deposits: =FV(rate,nper,pmt,pv,type).
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesWhat compound interest means
Compound interest is interest calculated on the original principal and on interest that has already accumulated. A compound-interest model normally uses five inputs:
#1 Best Overall
- Fundamental, two-line calculator that combines statistics and advanced scientific functions for high school math and science
- Two-line display shows the entry and calculated result at the same time for easy understanding of the calculation
- Fraction features, conversions, and basic scientific and trigonometric functions
- Solar and battery powered
- Approved for use on SAT, ACT and AP exams
- Principal: the amount initially deposited, invested, or borrowed.
- Annual interest rate: the stated yearly rate.
- Compounding frequency: how often interest is added to the balance.
- Term: how long the money remains invested or borrowed.
- Future value: the balance at the end of the term.
For a savings calculation, the final balance is not necessarily the interest earned. If you add money during the term, the final balance includes your original principal, your later contributions, and the growth generated by interest.
Excel calculates a mathematical projection from the assumptions you provide. It does not automatically account for a bank’s fees, taxes, changing rates, transaction timing, day-count rules, deposit limits, or rounding policy.
Calculate compound interest with the basic Excel formula
The standard formula for a lump sum compounded during the year is:
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →A = P(1 + r/m)^(m*t)
This is the same general intra-year formula described in Microsoft’s compound-interest guidance.
In Excel:
=P*(1+r/m)^(m*t)Build a simple worksheet
Enter the following values in a blank worksheet:
| Cell | Label | Example value |
|---|---|---|
| A1 | Initial principal | 1,000 |
| A2 | Annual interest rate | 5% |
| A3 | Compounding periods per year | 12 |
| A4 | Number of years | 10 |
| A5 | Future value | Formula |
| A6 | Interest earned | Formula |
In B1:B4, enter the example values. In B5, enter:
=B1*(1+B2/B3)^(B3*B4)Format B5 as currency. It should display approximately $1,647.01.
To calculate interest earned separately, enter this in B6:
=B5-B1That produces approximately $647.01. Format the rate cell as a percentage and the result cells as currency.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchEnter the interest rate correctly
Excel accepts either 5% or 0.05 as a 5% rate. Do not enter 5 unless your formula explicitly converts it from a whole-number percentage:
=B1*(1+B2/100/B3)^(B3*B4)The safer approach is to format the rate cell as a percentage and enter 5%. A cell containing the number 5 represents 500% when used directly as a rate.
Account for monthly, quarterly, or daily compounding
The rate per period and the total number of periods must use the same time unit. For a nominal annual rate:
| Compounding frequency | Periods per year | Periodic rate | Periods over t years |
|---|---|---|---|
| Annual | 1 | r/1 |
1*t |
| Semiannual | 2 | r/2 |
2*t |
| Quarterly | 4 | r/4 |
4*t |
| Monthly | 12 | r/12 |
12*t |
| Weekly | 52 | r/52 |
52*t |
| Daily | 360, 365, or another stated convention | r/m |
m*t |
For monthly compounding, divide the nominal annual rate by 12 and multiply the years by 12:
Rank #2
- View multiple calculations at the same time: Compare results and explore patterns on-screen with the MultiView display that supports up to four lines
- See math exactly as it appears in textbooks: Display math expressions, symbols and stacked fractions exactly the way they appear in textbooks — no need to adapt to a technical syntax; provides quick access to frequently used functions
- Scientific notation output: View scientific notation with the proper superscripted exponents and see the output in scientific notation
- Explore (x,y) table of values: Students can easily explore an (x,y) table of values for a given function automatically or by entering specific x values
- The TI-30XS MultiView scientific calculator is ideal for general math, Pre-Algebra, Algebra 1 and 2, Geometry, Statistics, general science, Biology and Chemistry
=P*(1+r/12)^(12*t)For quarterly compounding:
=P*(1+r/4)^(4*t)Daily compounding requires the convention used by the financial institution. Some products use 360 days, some use 365, and others use an actual-day convention. Microsoft discusses both 360- and 365-day examples in its compound-interest documentation.
Use Excel’s FV function
Excel’s FV function calculates the future value of a cash-flow stream at a constant periodic interest rate:
=FV(rate,nper,pmt,[pv],[type])Microsoft documents the syntax and arguments in its FV function reference.
rate: interest rate for each period.nper: total number of periods.pmt: payment or deposit made each period.pv: present value, such as the initial principal.type:0for payments at the end of a period, or1for payments at the beginning.
For the $1,000 example, with no recurring deposit, enter:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →=FV(B2/B3,B3*B4,0,-B1,0)This returns approximately $1,647.01. The 0 payment argument says there are no regular deposits. The final 0 is immaterial in this particular formula because there are no regular payments, but it makes the payment-timing assumption explicit.
Why the initial investment is negative
Excel financial functions use cash-flow signs. Money paid into an investment is an outflow from the investor and is entered as negative; money received at the end is positive. That is why the initial principal appears as -B1.
If your result is negative when you expect a positive account balance, check the signs of the cash flows. Reversing the signs of the initial investment and deposits generally reverses the sign of the result.
Calculate compound interest with regular deposits
Suppose you start with $1,000, add $100 monthly, earn a nominal 5% annual rate, compound monthly, and leave the money invested for 10 years. Add this input:
| Cell | Label | Example |
|---|---|---|
| B1 | Initial principal | 1,000 |
| B2 | Annual interest rate | 5% |
| B3 | Compounding periods per year | 12 |
| B4 | Years | 10 |
| B5 | Deposit per period | 100 |
For deposits made at the end of each month, use:
=FV(B2/B3,B3*B4,-B5,-B1,0)The projected final balance is approximately $17,175.24. The -B5 sign treats each monthly deposit as money paid into the account.
For deposits made at the beginning of each month, use:
=FV(B2/B3,B3*B4,-B5,-B1,1)The projected balance is approximately $17,239.94. It is higher because every deposit receives one additional month of interest.
Rank #3
- 10-digit display; for general math, pre-algebra, algebra 1 and 2, trigonometry and biology
- Performs trigonometric functions, logarithms, roots, powers, reciprocals, and factorials
- Also add, subtract, multiply and divide fractions; 1-variable statistics (mean / standard deviation)
- Conversions: fractions/decimals, degrees/radians/grads, DMS/decimal/degrees, and polar/rectangular
- Battery-powered; includes slide case
Calculate interest earned when deposits are included
Total contributions in this example are the initial $1,000 plus 120 monthly deposits of $100. To isolate interest earned from the final balance, subtract both types of contribution:
Recommended Free Tools
=FutureValue-InitialPrincipal-(DepositPerPeriod*NumberOfPeriods)Using the worksheet cells, if the future value is in B6:
=B6-B1-(B5*B3*B4)For end-of-month deposits, the result is approximately $4,175.24. Calling the entire $17,175.24 balance “interest” would incorrectly include the $13,000 contributed by you.
Nominal annual rates are not the same as effective annual yields
Dividing an annual rate by 12 is appropriate when the quoted rate is a nominal annual rate compounded monthly. It is not automatically correct for a rate that is already an effective annual yield, such as an APY or another effective annual rate.
For a nominal annual rate of 5% compounded monthly, the monthly rate is:
Free tools Windows power users keep installed
One-click scans. No signup required.
=5%/12For an effective annual rate of 5%, the equivalent monthly rate is:
=(1+5%)^(1/12)-1Using that equivalent rate for 10 years gives:
=1000*(1+((1+5%)^(1/12)-1))^(12*10)which simplifies mathematically to:
=1000*(1+5%)^10Do not assume that APR, APY, annual return, nominal rate, and effective annual rate mean the same thing. Use the definition supplied by the bank, lender, investment provider, or account disclosure.
Calculate an effective annual rate in Excel
For a nominal annual rate r compounded m times per year, the effective annual rate is:
=(1+r/m)^m-1For a nominal 5% rate compounded monthly:
=(1+5%/12)^12-1Excel also provides EFFECT:
=EFFECT(5%,12)The first argument is the nominal annual rate and the second is the number of compounding periods per year. Microsoft lists EFFECT and related financial functions in its financial-functions reference.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Build a month-by-month compound-interest schedule
FV is compact and useful when the rate and deposits are constant. A schedule is more transparent when deposits change, withdrawals occur, rates vary, fees apply, or transaction dates do not line up neatly with compounding periods.
For a monthly schedule, use these columns:
| Column | Heading | Example |
|---|---|---|
| A | Period | 1, 2, 3… |
| B | Beginning balance | Principal in the first row; prior ending balance thereafter |
| C | Deposit or withdrawal | Input for that month |
| D | Interest | Formula based on timing |
| E | Ending balance | Beginning balance plus cash flow plus interest |
Deposits at the beginning of the month
If the deposit is available to earn interest during that month, use:
Rank #4
- Scientific Calculator with Graphic Function: All-in-one scientific and graphing calculator. Supports plotting functions, analyzing graphs, and solving complex equations. Displays graphs and formulas simultaneously for clear visualization. Ideal for algebra, calculus, and exam prep.
- Compact and Comfortable Design: This scientific and graphing calculator sized at 7 x 3.3 inches for a balanced and ergonomic feel. Fits easily in one hand or on a desk without taking up space. Ideal for long study sessions, test environments, and everyday academic or professional use; smooth button layout supports efficient input and navigation.
- Multiple Modes and 360+ Functions: Includes angle measurement, calculation, and display modes for flexible use across subjects. This scientific and graphing calculator supports over 360 functions such as fractions, complex numbers, statistics, linear regression, standard deviation, and variable solving. Ideal for mastering algebra, geometry, trigonometry, and advanced math applications.
- Durable and Portable Design: Built with an anti-drop body that resists everyday impacts for long-term use. This scientific and graphing calculator is lightweight and slim for easy carrying in a backpack or pocket that includes a protective case to guard the screen and buttons during travel or storage.
- If you cannot turn on the calculator, please press the reset button on the back! If you have any further problems, we offer a limited warranty of 365 days. Please contact us and we will give you an answer within 24 hours.
Interest = (Beginning balance + Deposit) * Monthly rateThen calculate ending balance as:
Ending balance = Beginning balance + Deposit + InterestFor example, if the beginning balance is in B2, the deposit in C2, and the monthly rate in $H$1:
D2=(B2+C2)*$H$1E2=B2+C2+D2
In the next row, reference the previous ending balance:
B3=E2Deposits at the end of the month
If the deposit is made after that month’s interest is calculated, use:
Interest = Beginning balance * Monthly rateEnding balance = Beginning balance + Interest + Deposit
With the same cell arrangement:
D2=B2*$H$1E2=B2+D2+C2
State the timing assumption clearly. Applying interest to the deposit in the same month when the real deposit occurs at month-end will overstate the result.
Why a schedule can be more accurate
A period-by-period model lets you enter:
- Different deposit amounts in different months.
- Withdrawals as negative cash flows.
- Fees or taxes in their actual periods.
- Rate changes at the dates they take effect.
- Irregular contributions and transaction dates.
- A different compounding schedule from the contribution schedule.
Keep full precision in the formulas and round only the displayed values unless the actual institution rounds the balance after every period. Rounding every row can create a small cumulative difference.
Handle changing rates with FVSCHEDULE
For one initial principal with a sequence of compound rates, use:
=FVSCHEDULE(principal,schedule)For example:
=FVSCHEDULE(1000,{5%,4%,6%})This applies 5%, then 4%, then 6% to the growing balance. If the rates are in B2:B4, use:
=FVSCHEDULE(1000,B2:B4)Microsoft describes FVSCHEDULE as returning the future value of an initial principal after applying a series of compound interest rates. Blank schedule cells are treated as zero, while nonnumeric entries can produce #VALUE!; see the FVSCHEDULE reference.
FVSCHEDULE alone is not a complete model for changing deposits or withdrawals. Use a schedule when both the rate and cash flows vary.
When deposits and compounding use different frequencies
A simple FV formula works most naturally when deposits and interest calculations occur in the same periods. For example, monthly deposits paired with a monthly periodic rate are straightforward.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →If deposits are monthly but the institution compounds quarterly or annually, do not automatically divide the annual rate by 12. That may create a rate the account does not actually use. Instead:
Best Value
- Natural Textbook Display presents formulas and results exactly as written in textbooks for intuitive learning.
- Convert a stated effective annual rate to an equivalent monthly rate if the product’s terms allow that interpretation.
- Build a schedule that applies interest on the institution’s actual compounding dates.
- Use transaction-level dates when deposits and withdrawals are irregular.
The account’s disclosure controls the appropriate convention. A spreadsheet formula should reproduce those rules rather than silently replacing them with a monthly approximation.
Adjust a nominal future value for inflation
The compound-interest result is a nominal future value: the number of currency units in the account. It does not show what those units will buy.
To estimate purchasing power using an assumed constant inflation rate, use:
Free tools Windows power users keep installed
One-click scans. No signup required.
=FutureValue/(1+InflationRate)^YearsThis is a separate calculation. It should not be mixed into the interest formula unless you are deliberately building a real-return model.
Using Excel for loans
The same time-value-of-money mathematics applies to debt, but the cash-flow interpretation changes. A savings formula is not a complete amortization calculator.
For loan models, Excel’s financial functions can help calculate:
PMTfor a periodic payment.IPMTfor interest in a particular period.PPMTfor principal repaid in a particular period.PVfor present value.FVfor a projected or remaining balance.
See Microsoft’s financial-functions reference for the related functions. Loan calculations must also reflect payment timing, fees, rate resets, extra payments, and the lender’s compounding terms.
Recommended Free Tools
Troubleshoot common Excel compound-interest errors
| Problem | Likely cause | Fix |
|---|---|---|
| Future value is negative | Cash-flow signs are reversed. | Use negative values for deposits or money paid out and a positive value for money received, or reverse the entire sign convention consistently. |
| Result is implausibly large | The annual rate was applied to every month. | For a nominal annual rate with monthly compounding, use annual rate/12. |
| Result is too small | The rate was converted but the number of periods was not. | Use years*12 for monthly periods, or the corresponding frequency. |
| Monthly-deposit result is wrong | The deposit timing is incorrect. | Use type=0 for end-of-period deposits and type=1 for beginning-of-period deposits. |
#VALUE! in FVSCHEDULE |
The rate schedule contains text or another invalid entry. | Use numeric rates such as 5%; remove unintended text and check blank cells. |
| Small difference from a bank statement | Fees, taxes, day-count rules, transaction timing, or rounding differ. | Match the product’s stated assumptions and model each relevant cash flow. |
| Rate seems wrong | An effective annual yield was treated as a nominal annual rate. | Check whether the quoted rate is nominal, effective, APR, APY, or another defined measure. |
| Function is unavailable or behaves differently | The Excel edition or environment differs. | Check Microsoft’s function and version documentation and test the workbook in the environment where it will be used. |
Which method should you use?
- Use the direct formula for one principal, one constant rate, and a fixed compounding frequency. It exposes the underlying mathematics and is easy to audit.
- Use
FVfor a constant rate with regular equal deposits or payments, especially when payment timing matters. - Use a period-by-period schedule for changing deposits, withdrawals, rates, fees, taxes, irregular dates, or different contribution and compounding frequencies.
- Use
FVSCHEDULEfor a principal exposed to a sequence of rates, but not as a substitute for a full variable-cash-flow model.
Excel for the web and supported versions
Microsoft lists FV and FVSCHEDULE as available in Excel for the web and in current desktop editions including Microsoft 365 and Excel 2024. Microsoft’s FV documentation also lists Excel 2021, Excel 2019, and Excel 2016, along with corresponding Mac editions.
Ordinary formulas are the most portable approach. Excel for the web resembles the desktop application but does not support every feature identically; for example, macros in macro-enabled workbooks do not run in the browser. See Microsoft’s guidance on differences between browser and desktop Excel.
You do not need macros for any calculation in this guide. A free browser spreadsheet may be sufficient for a basic formula, while desktop Excel is more useful for reusable schedules, charts, advanced models, or offline work.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
