Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
- Power Bi (35+)
Compound Interest in Excel Formula
Compound interest is the addition of interest to the principal sum of a loan or deposit, or we can say, interest on interest. It is the outcome of reinvesting interest, rather than paying it out, so that interest in the next period is earned on the principal sum plus previously accumulated interest.
While simple interest is calculated only on the principal and (unlike compound interest) not on, principal plus interest earned or incurred in the previous period(s).
The total accumulated value, including the principal sum P plus, compounded interest I, is given by the formula:
- P is the original principal sum
- P’ is the new principal sum
- n is the compounding frequency
- r is the nominal annual interest rate
- t is the overall length of time the interest is applied (expressed using the same time units as r, usually years).
How to Calculate Compound Interest in Excel Formula? (with Examples)
Let us understand the same using some examples of Compound Interest formula in excel.
Example #1 – Using Mathematical Compound Interest Excel Formula
Suppose, we have the following information to calculate compound interest in excel.
Now as we have described the formula above also, we will implement the same in MS Excel using cell references and various operators.
Step 1 – As C2 cell contains principal amount (We can also call it as present value). We need to multiply this value with interest rate.
Step 2 – In our case, the interest is to be compounded quarterly (C5) that is why we need to divide the annual interest rate with cell C5
Step 3 – As interest is being compounded 4 times in a year, we need to give reference of a cell where the number of years is mentioned so that we can multiply 4 with a number of years. That is why the formula would be like this:
Step 4 – After pressing the Enter button, we will get the result as Rs. 15764.18 as the future value with compound interest.
This is like a compound interest calculator in excel now. We can change the value for Annual Interest Rate, the number of years and Compounding periods per year as below.
Example #2 – Using the Compound Interest Calculation Table in excel
Suppose we have the following information to calculate compound interest in a table excel format (systematically).
Step 1 – We need to name cell E3 as ‘Rate’ by selecting the cell and changing the name using Name Box.
Step 2 – We have principal value or present value as 15000 and the annual interest rate is 5%. To calculate the value of the investment at the end of quarter 1, we will add 5%/4 i.e., 1.25% interest to the principal value.
The result is shown below:
Step 3 – We just need to drag the formula till C6 cell by selecting the range C3: C6 and pressing Ctrl+D.
The future value after 4 quarters will be Rs. 15764.18.
Example #3 – Compound Interest Using FVSCHEDULE Excel Formula
Suppose, we have the following information to calculate compound interest in excel.
We will use FVSCHEDULE function to calculate future value. FVSCHEDULE formula returns the future value of an initial principal after applying a series of compound interest rates.
To do the same, the steps are:
Step 1 – We will initiate writing the FVSCHEDULE function into cell B6. The function takes two arguments i.e., principal and schedule.
- For the principal, we need to give the amount, which we are investing in.
- For the schedule, we need to provide the list of interest rates with commas in curly braces to calculate the value with compound interest.
Step 2 – For ‘principal’, we will provide the reference of B1 cell and for ‘schedule’, we will specify 0.0125 as this is the value we get when we divide the 5% with 4.
The result is shown below:
Now we apply the FVSCHEDULE formula in excel.
Step 3 – After pressing the Enter button, we get Rs. 15764.18 as the future value with compound interest in excel.
Example #4 – Compound Interest Using the FV Excel Formula
Suppose, we have the following data to calculate compound interest in excel.
We will use the FV excel formula to calculate compound interest.
FV function (stands for Future Value) returns the future value of an investment based on periodic, constant payments and a constant interest rate.
The syntax of the FV function is
The argument in the FV function is:
- Rate: Rate is the constant interest rate per period in an annuity.
- Nper: Nper stands for the total number of periods in an annuity.
- Pmt: PMT stands for payment. This indicates the amount, which we will be adding to the annuity every period. If we omit to mention this value, then it is mandatory to mention PV
- PV: PV stands for present value. This is the amount, which we are investing in. As this amount is going out from our pocket that is why by convention, this amount is mentioned with the negative sign.
- Type: This is an optional argument. We need to specify 0 if the amount is being added to the investment at the end of the period or 1 if the amount is being added to the investment at the beginning of the period.
We need to mention either the PMT or PV argument.
We will specify rate as ‘Annual Interest Rate (B2)/ Compounding periods per year (B4)’.
We need to specify nper as ‘Term (Years) * Compounding periods per year’.
As we will not be adding any additional amount to the principal value in between the investment period that is why we will specify ‘0’ for ‘pmt’.
As we have omitted the value for ‘pmt’ and we are investing Rs. 15000 as principal (present value), we will give reference of B1 cell with a negative sign for ‘PV’
After pressing the Enter button, we get Rs. 15764.18 as the future value with compound interest.
Things to Remember about Compound Interest Formula in Excel
- We need to enter the interest rate in percentage form (4%) or in decimal form (0.04).
- As ‘PMT’ and ‘PV’ argument in FV function is outflows in real, we need to mention them in the negative form (with minus (-) sign).
- FV function gives #VALUE! Error when any non-numeric value is given as argument.
- We need to mention either PMT or PV argument in FV function.
This has been a guide to Compound Interest formula in Excel. Here we discuss how to use Compound Interest formula in excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
- Formula of Monthly Compound Interest
- Daily Compound Interest Formula
- OR Function in Excel VBA
- What is Quartile Formula?
- Margin of Error Formula | Examples
- Excel Formula Not Working
- Relevance and Uses of Percent Error Formula
- QUARTILE Function in Excel
- INT Excel Function
- MAX Function in Excel
- ISERROR Function in Excel