Constructing a VA Readjustment Spreadsheet

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The following spreadsheet shows the solution to the value averaging readjustment problem from Chapter 5, page 90. Start with $6,500, with 17 years (204 months) to attain a $100,000 goal, and add other inputs as shown. What would the value path look like for monthly value averaging?

Put in the 5 inputs you know, and the spreadsheet does all the rest. The outputs are calculated in cells B9-B12, with the value path formula in cell B14. The solution involves an artificial time index as described in the text. This is shown at the bottom of the spreadsheet, where each month is indexed by the solution t = 86.8. The value path is shown for current and selected future months; your spreadsheet could show the value path for all months. The spreadsheet and the formulas needed to construct the spreadsheet are shown on pages 98 and 99.

Of course, if you start with a "Value Now" of $0, this spreadsheet will calculate a standard value averaging value path, as discussed on pages 88-89.

This spreadsheet is on the Web at: www.wiley.com/go/valueaveraging.

Was this article helpful?

0 0

Post a comment