FVSCHEDULE should allow cell reference for interest rate schedule

[Philipm]

To compute the future value of an investment by applying a constant
compounded annual growth rate (CAGR) over 8 years Excel requires that I type
in the actual interest rate for all 8 years using the FVSCHEDULE function.
FVSCHEDULE does not allow me to use a cell reference in the interest rate
array. Which means if I want to model an investment with different interest
rates - -I have to manually change all eight years of the interest rate
schedule every time. I would like Excel to allow the FVSchedule function to
use cell references for the interest rate array. Or create a true CAGR
function.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Philip J Smith]

Hi Philip

The function does allow you to give the array as a range of cells, named or
otherwise and will ignore blank cells in the calculation.

In the example below
Principle = 1
Schedule is a range of 4 cells

Assuming that you set the principle as 1 then the CAGR can be calulated as

=FVSCHEDULE(Principle,Schedule)^(1/COUNT(Schedule))-1

Alternatively The FVSCHEDULE function could be replaced by an array function.

{=Principle*PRODUCT(1+Schedule)}

Hope this helps.

Phil