I am trying to graph the periodic (yearly) future values of an investment growing with additional contributions and a fixed interest rate. I can use the FV formula to get the end result, but can't create the graph showing periodic values.
Apple Numbers
Hi,
The 'periodic values' are simply the 'end values' that would apply if the investment 'ended' at the end of that period, with the assumption that it could be cashed in at any of these 'ends' under the same rules that apply to doing so at the scheduled end.
Here's an example of a Line graph showing the future value of an investment using the FV function.
The calculation in column E is on an investment of $10,000, with $100 deposits made at the beginning of each month (including the first month). Annual interest rate is 4%.
There are two formulas on the table, one in cell C2, the other entered in cell E2, and filled down from there to the bottom row of column E.
C2: B2÷12
This calculates the periodic-rate (the interest paid per month) from the APR in B2.
Syntax: FV(periodic-rate, num-periods, payment, present-value, when-due)
E2: FV(C$2,A2,D$2,E$1,1)
FV gets the periodic rate from C2, the number of periods from 'this row' of column A, the monthly payment from D2, the initial deposit ('present-value) from E1, and is told when the payments are made (at the beginning of each period) by the last argument (1), then calculates the result.
The $ before the row number in three of the arguments keeps the formula looking at the same cell as it is filled down to other rows. The A2 reference (number of months) is a relative reference that changes the row number to make it continue to match the row on which the formula sits.
The Chart:
Because the month counts in column A are evenly spaced, I've chosen to put them in a Header column, where they can be used as Category labels in a Line graph.
The data to be charted is in column E.
To make the chart: Select the data cells, E2 - E14.
Click the Charts button to open a menu. Click 2D if it is not highlighted, then click the Liine chart in the right column.
The chart shown had no further editing (except to move it from below the table to beside the table) after the steps listed.
Regards,
Barry
Hi,
The 'periodic values' are simply the 'end values' that would apply if the investment 'ended' at the end of that period, with the assumption that it could be cashed in at any of these 'ends' under the same rules that apply to doing so at the scheduled end.
Here's an example of a Line graph showing the future value of an investment using the FV function.
The calculation in column E is on an investment of $10,000, with $100 deposits made at the beginning of each month (including the first month). Annual interest rate is 4%.
There are two formulas on the table, one in cell C2, the other entered in cell E2, and filled down from there to the bottom row of column E.
C2: B2÷12
This calculates the periodic-rate (the interest paid per month) from the APR in B2.
Syntax: FV(periodic-rate, num-periods, payment, present-value, when-due)
E2: FV(C$2,A2,D$2,E$1,1)
FV gets the periodic rate from C2, the number of periods from 'this row' of column A, the monthly payment from D2, the initial deposit ('present-value) from E1, and is told when the payments are made (at the beginning of each period) by the last argument (1), then calculates the result.
The $ before the row number in three of the arguments keeps the formula looking at the same cell as it is filled down to other rows. The A2 reference (number of months) is a relative reference that changes the row number to make it continue to match the row on which the formula sits.
The Chart:
Because the month counts in column A are evenly spaced, I've chosen to put them in a Header column, where they can be used as Category labels in a Line graph.
The data to be charted is in column E.
To make the chart: Select the data cells, E2 - E14.
Click the Charts button to open a menu. Click 2D if it is not highlighted, then click the Liine chart in the right column.
The chart shown had no further editing (except to move it from below the table to beside the table) after the steps listed.
Regards,
Barry
You can download an example of how to set this up with a simple table that doesn't use complicated financial formulas, just a handful of short, simple formulas here (Dropbox dowload).
It looks like this.
For a little more sophistication (optional) the second sheet 'Sensitivity Analysis' contains a simple example of using the FV function to set up a table that makes it easy to visualize what happens when, say, the rate of return changes (or the contribution amount, or starting amount, or number of years, etc).
Easily varying the assumptions like this and seeing "what happens" is where the true power of Numbers becomes evident.
SG
I am trying to graph the periodic (yearly) future values of an investment growing with additional contributions and a fixed interest rate. I can use the FV formula to get the end result, but can't create the graph showing periodic values.
