Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Future Value Function (FV)
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Future Value Function (FV)
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? "bosley_4" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Future Value Function (FV)
"bosley_4" wrote:
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
future value function for compound interest | Excel Worksheet Functions | |||
future value | Excel Discussion (Misc queries) | |||
Future Value function with differenct cash flows | Excel Discussion (Misc queries) | |||
Is there a function to show future date | Excel Worksheet Functions | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions |