How to Calculate Investment Growth in Excel

How to Calculate Investment Growth in
Excel

Investment growth refers to the growth of your capital within a certain period of time, assuming
that the profits were reinvested and the amount was allowed to compound.

A simple interest rate can be applied to a fixed principal amount for a certain number of years. It
is nothing but the percentage rule. But when the interest is reinvested, the investment growth
has to be calculated by taking the interest on the interest too, which is the concept of compound
interest.

Every individual with employment and some cash reserves uses a savings bank account, at the
least. Many people put their money in stocks and bonds for monthly or quarterly compound
interest. Estimating the future value of the investment, thus, is central to managing even the
smallest amounts.

In this article, you’ll know more about investment growth in detail and how to calculate
investment growth in Excel quickly.

Why do you need to Calculate Investment Growth?

Investment growth is a general term that indicates the compounding or growth of capital put in a
bond or fund with a certain profit or loss percentage. Compound annual growth rate (CAGR)
and return on investment (ROI) are dependable parameters that help an investor decide between stocks and investment options. All parameters eventually estimate the investment growth a stock can provide.

-Advertisement-

Manual calculations involve compounding interests and then calculating investment growth over
the compounded amount. A mathematical formula can be applied, but when there are numerous
initial investments with different interest rates and monthly contributions, a quick Excel function
is what you need.

Mathematical Formula for Calculating Investment Growth
in Excel

Investments in savings accounts and various bonds and securities are subject to compound
interest. And calculating the future value of investment should be no secret. Here is how you
can use your office Excel to do the calculations that you might think only your manager can do.
Let us go over the mathematical formula and relations of all the quantities involved.

Final value of Investment = PV * (1 + I/n)^nt
where
PV – Present value of an investment
I – Interest rate
n – Compounding frequency
t – Number of periods

The interest rate can be monthly, quarterly, or yearly. If the compounding interest rate is
monthly, say 2%, this means a 2% annual return on the capital is added to the original
investment divided into 12 equal portions. In other words, approximately 0.167% of the capital is
added every month.

This is important since, in the mathematical relation, we also divide the compound interest with
the compounding frequency, which will be 12 for monthly compounding.
Let’s see how it applies in Excel.

Applying Mathematical Formula to Calculate Investment Growth in Excel
Here is a scenario:
You have deposited $5000 at a monthly compound interest rate of 7%. Using the Excel
investment growth calculator, you want to calculate the future value of the investment if the
amount compounds for 8 years.

Let’s fill in the slots according to our scenario.

The Excel calculator will use the cell references instead of the actual values. For instance, in the
place of present value (PV) in the mathematical formula, Excel will take B2.

As the mathematical formula above demonstrates, the interest rates are divided by the
compounding frequency. Our scenario uses monthly compound interest, so the compounding
frequency here will be 12. To calculate investment growth in Excel for quarterly compound
interest, the compounding frequency will be 4.

-Advertisement-

Putting cell locations for the values in the mathematical formula, we get the growth in
investment:

$5000 at a monthly compound interest of 7% will grow and equal $8739.13 after 8 years if no
contributions in addition to the original investment are made.
For a compounding frequency of 4, the investment growth will be different.

For a quarterly interest rate of 7%, investment growth as calculated by the Excel calculator for
an initial investment of $5000 will equal $8711.07 after 8 years.

How to Calculate Investment Growth in Excel in the
presence of Monthly Contributions?

You can use the FV or future value function of Excel to calculate the investment growth when
periodic payments or contributions are involved.

Excel FV formula can be broken down as follows:
=FV(rate, nper, pmt, [pv],[type])
Where,

Rate= interest rate (monthly, quarterly, or yearly)
Nper= number of compounding periods
Pmt= periodic payment or the annuity amount contributed per period
[pv]= present value of investment
[type]= it can be ‘0’ or ‘1’. 0 indicates that the annuity amount or the periodic payment was
made at the end of the compounding period. 1 indicates that the periodic payment adds at the
beginning of the compounding period.

We will be using 0 for [type] to demonstrate the annuity amount added at the end of the period.
Let us start with $500 per month at an annual interest rate of 10%. The opening balance or the
initial amount will be 0.

Since the contributions are monthly, we will need a monthly interest rate. And hence, the
a number of compounding periods will also represent the number of months.
Let us make separate columns for the rate (monthly compound interest rate, in this case) and
per (number of compounding periods).

Note: You can derive the monthly interest and the nper directly in the FV formula too. These
separate columns have been made for understanding and to keep the FV formula simple.

Now that the data entry has been completed, we will move towards our FV Excel formula.
Go over the following step-by-step procedure to calculate investment growth in Excel using
the FV function.

Step 1:
Enter the FV function in a blank cell.

Step 2:
The Excel shows the FV arguments in order. Enter the values or formula arguments in the same
order.

Step 3:
Close bracket and press enter. And there, the Excel FV calculator gives the future value of the
investment.

To calculate investment growth in Excel for various periodic payments and interest rates,
drag the lower right corner of the cell where you had applied the FV formula. The FV function
will apply to the rest of the rows too.

So, for our initial scenario, the investment will grow and equal $20,890,78 after 3 years if a $500
payment is made every month at an interest rate of 10 per cent.

The Bottom Line: Calculate Investment Growth in Excel

The above tutorial is your key to using office Excel for solving compound interest and
investment calculations. Understanding the function or the mathematical formula for investment
growth is important to employ it adequately in various situations. Once you have that, the same
formula can be used in various ways for the error-free, efficient calculation of investment
growth.

Alternativly to using Excel you can use Google sheets which is an excelent online resource where you can edit on the move through your phone, laptop or computer.

Now you know how to calculate investment growth in excel check out what you can invest in for long term growth and use your new found skills to calculate potential growth.

What Should I Invest In For Long-Term Growth?

-Advertisement-

You might also like
dijital pazarlama