Future Value Function (FV)

[bosley_4]

I'm using an FV function to calculate returns over a period. I now require
to increase percentage growth rate by the Retail Price Index year on year
(i.e. Year 1 - 5%, Year 2 - 5% + 2% of 5% etc..) Anyone with a solution?

 

[Sam Wilson]

Is the 2% constant? ie does the interest rate increase by 2% of 5% each year.

Or, is it compound, ie is teh interest rate in year n 2% higher than the
rate in year n-1?

 

[JoeU2004]

I'm using an FV function to calculate returns over a period. I now
require
to increase percentage growth rate by the Retail Price Index year on year
(i.e. Year 1 - 5%, Year 2 - 5% + 2% of 5% etc..) Anyone with a solution?

Well, it would have been helpful if you had shown us Year 3. Is it
Year2rate*(1+2%) [compounding by constant RPI], Year2rate(1+RPI[3])
[compounding by variable RPI], 5%*(1+2*2%) [increasing by contant RPI], or
5%*(1+RPI[2]+RPI[3]) [increasing by variable RPI]?

If compounding by constant RPI, try the following array formula (commit with
ctrl-shift-Enter instead of Enter):

=10000*PRODUCT(1+5%*(1+2%)^(ROW(INDIRECT("$1:$5"))-1))

for years 1-5, where 10000 represents the PV. The use of INDIRECT prevents
the ROW argument from changing if rows are inserted in the range. It also
permits the use of a variable number of years, viz. INDIRECT("$1:$"&A1).

If compounding by variable RPI, I have not yet found a single formula. But
you could do it with a helper cells. Presumably, you have the RPIs in a
table, e.g. X3:X6 for years 2-5. Put 5% into Y2; then put =Y2*(1+X3) into
Y3, and copy down through Y6. Put 10000 into Z1; then put =Z1*(1+Y2) into
Z2, and copy down through Z6. Z6 contains the FV.


Note: PRODUCT can behave poorly when the number of products is large or
they are small in value. It is probably okay in this application. But
alternatively, use the following array formula (commit with ctrl-shift-Enter
instead of Enter):

=10000*10^SUM(LOG(1+5%*(1+2%)^(ROW(INDIRECT("$1:$5"))-1)))