Post
by **JWR1945** » Mon Dec 22, 2003 7:25 am

This lets you see what would have happened to the buying power of a pension with a constant dollar amount. That is, payments do not change to match inflation.

What I have done is copy the data summary section in columns S through AK and rows 10 through 144. Then I have calculated the inflation adjusted value of the initial amount for each sequence. This new data summary section has the start year of a retirement and the buying power of the initial balance after 10, 20, 30, 40, 50 and 60 years.

This modification may be made to the Retire Early Safe Withdrawal [Rate] Calculator and/or all of my modified versions.

First bring up the calculator that you wish to modify. You may prefer to do your work on a copy instead of the original.

**The first step is to copy and paste the data summary section into a new area. **I chose to paste my copy in columns BS through CK and in rows 10 through 144. (The original summary is in columns S through AK and in rows 10 through 144.)

Highlight (i.e., click) cell S10. Press the Shift key. Highlight cell AK144. Release the Shift key.

Click on Edit, then Copy. Click on an empty cell.

[When you have finished all copying tasks, click on a blank cell then click on the formula bar to complete copying. You do not have to do this until the final step.]

Highlight cell BS10. Press the Shift key. Highlight cell CK144. Release the Shift key.

Click on Edit, then Paste (not Paste Special). Click on an empty cell.

**The next step is to change the formulas in row 15.**

Highlight cell BV15. In the formula bar near the top, change the formula from **=BT15/BU15** to **=$B$4/BU15** and click the check mark.

Highlight cell BY15. Change the formula to **=$B$4/BX15** and click the check mark.

Highlight cell CB15. Change the formula to **=$B$4/CA15** and click the check mark.

Highlight cell CE15. Change the formula to **=$B$4/CD15** and click the check mark.

Highlight cell CH15. Change the formula to **=$B$4/CG15** and click the check mark.

Highlight cell CK15. Change the formula to **=$B$4/CJ15** and click the check mark.

**Next, use the fill handle to drag the formulas down.**

Start with cell BV15. Then repeat with cells BY15, CB15, CE15, CH15 and CK15.

Locate the fill handle on the lower right hand corner of cell BV15. Place the mouse cursor over the fill handle. The fat plus sign should change to narrow cross hairs. Click down on your mouse button and hold it down. Drag the fill handle (and the formula) down to row 144 (i.e., cell BV144). Release the mouse button. Click on any cell.

When you repeat this, drag the formula only as far down as it applies. That is, for column BY, drag it down to row 134. For column CB, drag it down to row 124. For column CE, drag it down to row 114. For column CH, drag it down to row 104. For column CK, drag it down to row 94. It is easy to tell how far a formula applies. It applies as long as the **Inflation Factor** in the column next to it (on the left) has data.

**The final step is optional.**

I wrote **Buying Power of the Initial Balance** into cells BU8, BX8, CA8, CD8, CG8 and CJ8.

This new data summary section always displays results based on the initial balance (that you enter in cell B4). Other settings do not affect these calculations.

Have fun.

John R.