What does it do?

Calculates the compound interest

Formula breakdown:

=FV(rate, nper, pmt, [pv])

What it means:

=FV(interest rate, number of periods, periodic payment, initial amount)


Computing the compound interest of an initial investment is easy for a fixed number of years.  But let’s add an additional challenge.

What if you are also putting in monthly contributions to your investment? Now that’s a lot more challenging to compute now!

How much would be available for you at the end of your investment?

Thankfully there is an easy way to calculate this with Excel’s FV formula! FV stands for Future Value.

In our example below, we have the table of values that we need to get the compound interest or Future Value from:

Calculate your Monthly Investment with FV Formula

There are two important concepts we need to use since we are using monthly contributions:

  • Since our interest rate is the annual rate, we will have to divide it by 12 to make it monthly
  • We will need to convert our number of years into number of months by multiplying it by 12

 

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the FV function in a blank cell:

=FV(

Calculate your Monthly Investment with FV Formula

 

STEP 2: The FV arguments:

rate

What is the rate of the interest?

Select the cell containing the interest rate and divide it by 12 to get the monthly interest rate (make sure that this is in a percentage):

=FV(B9/12,

Calculate your Monthly Investment with FV Formula

nper

How many periods?

Select the cell containing the number of years and multiply it by 12 to get the number of months:

=FV(B9/12, C9*12,

Calculate your Monthly Investment with FV Formula

pmt

What is the periodic payment?

Select the cell that contains your monthly contribution (this is your periodic payment):

=FV(B9/12, C9*12, D9,

Calculate your Monthly Investment with FV Formula

pv

What is the initial amount?

PV stands for present value, the initial amount. Multiply the entire result by -1.

=FV(B9/12, C9*12, D9, A9) * -1

Calculate your Monthly Investment with FV Formula

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

You now have all of the compound interest results!

Calculate your Monthly Investment with FV Formula

 

Calculate the Monthly Investment with Excel’s FV Formula

HELPFUL RESOURCE:

oztraining_728x90

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+