WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Compound Interest Formula in Excel

Compound Interest Formula in Excel

By Babita SehdevBabita Sehdev | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

The total accumulated value, including the principal sum P plus, compounded interest I, is given by the formula:

compound interest

Where,

  • 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).

Compound Interest in Excel

How to Calculate Compound Interest in Excel Formula? (with Examples)

Let us understand the same using some examples of the Compound Interest formula in excel.

You can download this Compound Interest Excel Template here – Compound Interest Excel Template

Example #1 – Using Mathematical Compound Interest Excel Formula

Suppose we have the following information to calculate compound interest in excel.

compound interest examples

Now, as we have described the formula above also, we will implement the same in MS Excel using cell references in excel and various operators.

Step 1 – As the C2 cell contains the principal amount (We can also call it as present value). We need to multiply this value with the interest rate.

compound interest examples 1

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

compound interest examples 1-2

Step 3 – As interest is being compounded four times in a year, we need to give reference to 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:

compound interest examples 1-3

Step 4 – After pressing the Enter button, we will get the result as Rs. 15764.18 as the future value with compound interest.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

compound interest examples 1-4

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.

compound interest examples 1-5

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).

compound interest examples 2

Step 1 – We need to name cell E3 as ‘Rate’ by selecting the cell and changing the name using Name Box.

compound interest examples 2-1

Step 2 – We have the 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.

compound interest examples 2-2

The result is shown below:

compound interest examples 2-3

Step 3 – We just need to drag the formula till C6 cell by selecting the range C3: C6 and pressing Ctrl+D.

Compound interest examples 2-5

The future value after four 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.

Compound interest examples 4

We will use the 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.

Compound interest examples 3-1

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.

Compound Interest examples 3-6

The result is shown below:

Compound interest examples 3-2

Now we apply the FVSCHEDULE formula in excel.

Compound interest examples 3-4

Step 3 – After pressing the Enter button, we get Rs. 15764.18 as the future value with compound interest in excel.

Compound interest examples 3-5

Example #4 – Compound Interest Using the FV Excel Formula

Suppose we have the following data to calculate compound interest in excel.

Compound interest examples 4

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

Compound interest examples 4-1

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 one 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 the rate as ‘Annual Interest Rate (B2)/ Compounding periods per year (B4)’.

Compound interest examples 4-2

We need to specify nper as ‘Term (Years) * Compounding periods per year.’

Compound interest examples 4-3

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.’

Compound interest examples 4-4

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.’

Compound interest examples 4-5

After pressing the Enter button, we get Rs. 15764.18 as the future value with compound interest.

Compound interest examples 4-6

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 the FV function.

Recommended Articles

This has been a guide to Compound Interest Formula in Excel. Here we discuss how to use the Compound Interest formula in excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Formula of Monthly Compound Interest
  • What is Quartile Formula?
  • QUARTILE Function in Excel
  • Excel NPER Function
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Compound Interest Excel Template

Coursera IPO Financial Model & Valuation Free Download