Making Excel Easy

Making Excel Simple And Easy

Future Value Formula Explained

| 0 comments

The future value formula in Excel will calculate the future value of an amount taking into account any interest rate and the amount of time taken. The easiest example of this to explain is lets say you want to invest $12,000 for 4 years with 10% interest pa. The Future value (FV) formula will give you the total amount that would be accumulated at the end of 4 years.

Formula

= FV ( Rate , Nper , PMT , [PV] , [type] )

Explained

= > Tells Excel there is a function/formulas to perform
fv > Tells Excel which function to perform

In The Brackets

Rate > This is the interest rate on the investment
Nper > This is the amount of periods to maturity or completion
Pmt > this is the amount of each invested payment made
Pv > This is the total original amount invested
Type > This is when the payments are due. 0 for the end, 1 for the beginning

Example

Using the brief example at the start of the article, I will go into more detail to give you a better understanding of this formula. We want to invest $12,000 in a fixed term deposit, we go to Bank XXX and they offer the following 4 investment options:

Fixed Term 1

$12,000 inital investment with 10% interest pa paid yearly over 4 years

Fixed Term 2

$12,000 inital investment but the interest is paid monthly

Fixed Term 3

$6,000 inital investment with 10% interest pa paid yearly over 4 years but also are yearly additional payments of $1,500

Fixed Term 4

$0 inital investment but a payments of $250 with 0.8333% interest over 48 months

To calculate fixed term 1

= FV ( Rate , Nper , , [PV] )

This variation of the Fv formula is straight forward. Select the cells relevant to the rate, nper and pv

In Excel

= FV ( F9 , F8 , , F7 )

To calculate fixed term 2

= FV ( Rate , Nper , , [PV] )

This variation of the Fv formula is a little more difficult. Select the cells relevant to the rate, nper and pv. If you have copied the cells across then you will need to multiply the periods (Nper) by 12, and divide the interest (Rate) by 12. You can do this in the cell or in the formula. See below.

In Excel

= FV ( I9 , I8 , , I7 )

With Additions

= FV ( Rate / 12 , Nper * 12 , , [PV] )

In Excel

= FV ( I9 / 12 , I8 * 12 , , I7 )

To calculate fixed term 3

= FV ( Rate , Nper , PMT , [PV] , [type] )

This variation of the Fv formula is similar to the first and is straight forward. Select the cells relevant to the rate, nper, pmt, pv and type. One thing to watch out for is the type. The type in the formula will determine how Excel will calculate the formula based on when the payment will be made. 0 is for the end of the period, 1 for the beginning of the period

In Excel

= FV ( L9 , L8 , L11 , L7 , L12 )

To calculate fixed term 4

= FV ( Rate , Nper , PMT , [PV] , [type] )

This variation of the Fv formula is a little more difficult than the second. Select the cells relevant to the rate, nper, pmt, pv and type. If you have copied the cells across then you will need to multiply the periods (Nper) by 12, and divide the interest (Rate) by 12. One thing to watch out for is the type. The type in the formula will determine how Excel will calculate the formula based on when the payment will be made. 0 is for the end of the period, 1 for the beginning of the period

In Excel

= FV ( O9 , O8 , O11 , O7 , O12 )

With Additions

= FV ( Rate / 12 , Nper * 12 , PMT , [PV] , [type] )

In Excel

= FV ( O9 / 12 , O8 * 12 , O11 , O7 , O12 )

Share

Leave a Reply

Required fields are marked *.

*