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