Help with formula

E

Emma Hope

Hi,

I am looking for help on a formula, so far the only way i can figure it out
is to make a table and then do a vlookup.

The general gist of the formula is as follows - you make a set payment into
a bank account, you receive interest giving a higher total, the following
year you make an additional payment (same amount) and then the interest is
calculated on the total of the previous years total and this years payment,
this carries on for many years.

Cell B1 = Interest - 4.8%
Cell B2 = Annual Payment - £96,000

Year 1 = 96,000*(1+0.048) i.e. cell C1 = B2 * (1+B1) = £100,608
Year 2 = this total gets the interest i.e. (100,608 + 96,000)*(1+0.048) i.e.
cell C2 = (C1+B2)*(1+B1) = £206,045
Year 3 =

I make a table of these with 50 rows (50 years) and then vlookup the year i
want.

Please note that the formula includes the extra payment every year, i can
find a formula that just adds the interest every year to the basic initial
payment, but this does not cover my extra payments.

What i would like to be able to do is use one formula that can calculate
this figure for any given year. Preferably without an analysis toolpak
formulae as my clients may not have this installed.

Thanks for your help.
 
D

Duke Carey

look at the FV() function, which allows you to set an initial value (PV) an
interest rate and a periodic payment amount (all payments must be equal). It
will then return the value after whatever # of periods you require
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top