I'm having a problem with my spreadsheet (other columns don't dynamically autosize to the range inputs calculated above.
In the spreadsheet below, cell B20-B19 equals (22 years). I need to use that number as the beginning value in a future value formula (see fX top of sheet) that will dynamically match the number of periods in a range (22 years); however, as the formula moves down the age range (e.g., 68, 69, 70, ... , 90), then it will reduce the original period range (22 years) by one year for each row down the range of rows. To illustrate, J5 would be 21 years in the future value formula. J6 would be 20 years. And so on.
Any idea how I can create this formula? Thank you. Follow the columns over to cell J4. This is what I need to adjust dynamically to match number of years originally but decline in value as age nears "age at end of distribution". In this case, 90.
Tax and Lost Opportunity Cost Analysis | | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
Current Age | | ----> J4 formula is =fv($B$18,($D$26-D4),,-I4,1) | | | | | | | |
Account | | | | | | | | | |
Contributions (Annual) | | | | | | | | | |
Tax Rate | | | | | | | | | |
Nominal Return Rate | | | | | | | | | |
Retire Age | | | | | | | | | |
Years To Retirement | | | | | | | | | |
Future Value of Account | | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
Account Value | | | | | | | | | |
Distribution Percentage | | | | | | | | | |
Tax Rate | | | | | | | | | |
Nominal Return Rate | | | | | | | | | |
Lost Opportuntiy Cost Rate | | | | | | | | | |
Age at Distribution | | | | | | | | | |
Age at End of Distribution | | | | | | | | | |
Years of Total Distibution | | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
<colgroup><col style="width: 165px"><col width="100"><col width="42"><col width="29"><col width="70"><col width="79"><col width="84"><col width="68"><col width="58"><col width="84"><col width="89"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
[TD="bgcolor: #93c47d"]Total
Income
Distribution[/TD]
[TD="bgcolor: #93c47d"]Total Tax
Cost[/TD]
[TD="bgcolor: #93c47d"]Total LOC
Cost[/TD]
[TD="bgcolor: #93c47d"]Total Tax +
LOC Cost[/TD]
[TD="bgcolor: #93c47d, align: right"]$4,806,961[/TD]
[TD="bgcolor: #93c47d, align: right"]$1,586,297[/TD]
[TD="bgcolor: #93c47d, align: right"]$92,520,360[/TD]
[TD="bgcolor: #93c47d, align: right"]$94,106,657[/TD]
[TD="bgcolor: #93c47d"]Accumulation Phase[/TD]
[TD="bgcolor: #ffd966"]Age[/TD]
[TD="bgcolor: #ffd966"]Beginning
Account
Value[/TD]
[TD="bgcolor: #ffd966"]Account
Value Growth[/TD]
[TD="bgcolor: #ffd966"]Account Value
After Income
Distribution[/TD]
[TD="bgcolor: #ffd966"]Income
Distribution[/TD]
[TD="bgcolor: #ffd966"]Tax[/TD]
[TD="bgcolor: #ffd966"]LOC[/TD]
[TD="bgcolor: #ffd966"]Total[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #ffd966, align: right"]68[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,800,375[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,940,393[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,814,376[/TD]
[TD="bgcolor: #ffd966, align: right"]$126,017[/TD]
[TD="bgcolor: #ffd966, align: right"]$41,586[/TD]
[TD="bgcolor: #ffd966, align: right"]149,855.13[/TD]
[TD="bgcolor: #ffd966, align: right"]$191,441[/TD]
[TD="align: right"]$186,000[/TD]
[TD="bgcolor: #ffd966, align: right"]69[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,814,376[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,955,095[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,828,448[/TD]
[TD="bgcolor: #ffd966, align: right"]$126,647[/TD]
[TD="bgcolor: #ffd966, align: right"]$41,793[/TD]
[TD="bgcolor: #ffd966, align: right"]142,079.63[/TD]
[TD="bgcolor: #ffd966, align: right"]$183,873[/TD]
[TD="align: right"]$10,000[/TD]
[TD="bgcolor: #ffd966, align: right"]70[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,828,448[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,969,871[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,842,591[/TD]
[TD="bgcolor: #ffd966, align: right"]$127,280[/TD]
[TD="bgcolor: #ffd966, align: right"]$42,002[/TD]
[TD="bgcolor: #ffd966, align: right"]134,707.57[/TD]
[TD="bgcolor: #ffd966, align: right"]$176,710[/TD]
[TD="align: right"]28.00%[/TD]
[TD="bgcolor: #ffd966, align: right"]71[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,842,591[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,984,720[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,856,804[/TD]
[TD="bgcolor: #ffd966, align: right"]$127,917[/TD]
[TD="bgcolor: #ffd966, align: right"]$42,212[/TD]
[TD="bgcolor: #ffd966, align: right"]127,718.03[/TD]
[TD="bgcolor: #ffd966, align: right"]$169,931[/TD]
[TD="align: right"]7.00%[/TD]
[TD="bgcolor: #ffd966, align: right"]72[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,856,804[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,999,644[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,871,088[/TD]
[TD="bgcolor: #ffd966, align: right"]$128,556[/TD]
[TD="bgcolor: #ffd966, align: right"]$42,424[/TD]
[TD="bgcolor: #ffd966, align: right"]121,091.15[/TD]
[TD="bgcolor: #ffd966, align: right"]$163,515[/TD]
[TD="align: right"]67[/TD]
[TD="bgcolor: #ffd966, align: right"]73[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,871,088[/TD]
[TD="bgcolor: #ffd966, align: right"]$3,014,642[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,885,443[/TD]
[TD="bgcolor: #ffd966, align: right"]$129,199[/TD]
[TD="bgcolor: #ffd966, align: right"]$42,636[/TD]
[TD="bgcolor: #ffd966, align: right"]114,808.12[/TD]
[TD="bgcolor: #ffd966, align: right"]$157,444[/TD]
[TD="align: right"]32[/TD]
[TD="bgcolor: #ffd966, align: right"]74[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,885,443[/TD]
[TD="bgcolor: #ffd966, align: right"]$3,029,715[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,899,870[/TD]
[TD="bgcolor: #ffd966, align: right"]$129,845[/TD]
[TD="bgcolor: #ffd966, align: right"]$42,849[/TD]
[TD="bgcolor: #ffd966, align: right"]108,851.09[/TD]
[TD="bgcolor: #ffd966, align: right"]$151,700[/TD]
[TD="align: right"]$2,800,375[/TD]
[TD="bgcolor: #ffd966, align: right"]75[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,899,870[/TD]
[TD="bgcolor: #ffd966, align: right"]$3,044,864[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,914,370[/TD]
[TD="bgcolor: #ffd966, align: right"]$130,494[/TD]
[TD="bgcolor: #ffd966, align: right"]$43,063[/TD]
[TD="bgcolor: #ffd966, align: right"]103,203.16[/TD]
[TD="bgcolor: #ffd966, align: right"]$146,266[/TD]
[TD="bgcolor: #ffd966, align: right"]76[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,914,370[/TD]
[TD="bgcolor: #ffd966, align: right"]$3,060,088[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,928,941[/TD]
[TD="bgcolor: #ffd966, align: right"]$131,147[/TD]
[TD="bgcolor: #ffd966, align: right"]$43,278[/TD]
[TD="bgcolor: #ffd966, align: right"]97,848.28[/TD]
[TD="bgcolor: #ffd966, align: right"]$141,127[/TD]
[TD="bgcolor: #93c47d"]Distribution Phase[/TD]
[TD="bgcolor: #ffd966, align: right"]77[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,928,941[/TD]
[TD="bgcolor: #ffd966, align: right"]$3,075,389[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,943,586[/TD]
[TD="bgcolor: #ffd966, align: right"]$131,802[/TD]
[TD="bgcolor: #ffd966, align: right"]$43,495[/TD]
[TD="bgcolor: #ffd966, align: right"]92,771.25[/TD]
[TD="bgcolor: #ffd966, align: right"]$136,266[/TD]
[TD="align: right"]$2,800,375[/TD]
[TD="bgcolor: #ffd966, align: right"]78[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,943,586[/TD]
[TD="bgcolor: #ffd966, align: right"]$3,090,765[/TD]
[TD="bgcolor: #ffd966, align: right"]$2,958,304[/TD]
[TD="bgcolor: #ffd966, align: right"]$132,461[/TD]
[TD="bgcolor: #ffd966, align: right"]$43,712[/TD]
[TD="bgcolor: #ffd966, align: right"]87,957.64[/TD]
[TD="bgcolor: #ffd966, align: right"]$131,670[/TD]
[TD="align: right"]4.50%[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]$2,958,304[/TD]
[TD="align: right"]$3,106,219[/TD]
[TD="align: right"]$2,973,096[/TD]
[TD="align: right"]$133,124[/TD]
[TD="align: right"]$43,931[/TD]
[TD="align: right"]83,393.80[/TD]
[TD="align: right"]$127,325[/TD]
[TD="align: right"]33.00%[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]$2,973,096[/TD]
[TD="align: right"]$3,121,750[/TD]
[TD="align: right"]$2,987,961[/TD]
[TD="align: right"]$133,789[/TD]
[TD="align: right"]$44,150[/TD]
[TD="align: right"]79,066.77[/TD]
[TD="align: right"]$123,217[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]$2,987,961[/TD]
[TD="align: right"]$3,137,359[/TD]
[TD="align: right"]$3,002,901[/TD]
[TD="align: right"]$134,458[/TD]
[TD="align: right"]$44,371[/TD]
[TD="align: right"]74,964.25[/TD]
[TD="align: right"]$119,335[/TD]
[TD="align: right"]6.00%[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]$3,002,901[/TD]
[TD="align: right"]$3,153,046[/TD]
[TD="align: right"]$3,017,915[/TD]
[TD="align: right"]$135,131[/TD]
[TD="align: right"]$44,593[/TD]
[TD="align: right"]71,074.59[/TD]
[TD="align: right"]$115,668[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]$3,017,915[/TD]
[TD="align: right"]$3,168,811[/TD]
[TD="align: right"]$3,033,005[/TD]
[TD="align: right"]$135,806[/TD]
[TD="align: right"]$44,816[/TD]
[TD="align: right"]67,386.76[/TD]
[TD="align: right"]$112,203[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]$3,033,005[/TD]
[TD="align: right"]$3,184,655[/TD]
[TD="align: right"]$3,048,170[/TD]
[TD="align: right"]$136,485[/TD]
[TD="align: right"]$45,040[/TD]
[TD="align: right"]63,890.28[/TD]
[TD="align: right"]$108,930[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]$3,048,170[/TD]
[TD="align: right"]$3,200,578[/TD]
[TD="align: right"]$3,063,411[/TD]
[TD="align: right"]$137,168[/TD]
[TD="align: right"]$45,265[/TD]
[TD="align: right"]60,575.21[/TD]
[TD="align: right"]$105,841[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]$3,063,411[/TD]
[TD="align: right"]$3,216,581[/TD]
[TD="align: right"]$3,078,728[/TD]
[TD="align: right"]$137,853[/TD]
[TD="align: right"]$45,492[/TD]
[TD="align: right"]57,432.16[/TD]
[TD="align: right"]$102,924[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]$3,078,728[/TD]
[TD="align: right"]$3,232,664[/TD]
[TD="align: right"]$3,094,122[/TD]
[TD="align: right"]$138,543[/TD]
[TD="align: right"]$45,719[/TD]
[TD="align: right"]54,452.19[/TD]
[TD="align: right"]$100,171[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]$3,094,122[/TD]
[TD="align: right"]$3,248,828[/TD]
[TD="align: right"]$3,109,592[/TD]
[TD="align: right"]$139,235[/TD]
[TD="align: right"]$45,948[/TD]
[TD="align: right"]51,626.84[/TD]
[TD="align: right"]$97,575[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]$3,109,592[/TD]
[TD="align: right"]$3,265,072[/TD]
[TD="align: right"]$3,125,140[/TD]
[TD="align: right"]$139,932[/TD]
[TD="align: right"]$46,177[/TD]
[TD="align: right"]48,948.09[/TD]
[TD="align: right"]$95,126[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]$3,125,140[/TD]
[TD="align: right"]$3,281,397[/TD]
[TD="align: right"]$3,140,766[/TD]
[TD="align: right"]$140,631[/TD]
[TD="align: right"]$46,408[/TD]
[TD="align: right"]46,408.33[/TD]
[TD="align: right"]$92,817[/TD]
[TD="align: right"]$3,140,766[/TD]
[TD="align: right"]$3,297,804[/TD]
[TD="align: right"]$3,156,470[/TD]
[TD="align: right"]$141,334[/TD]
[TD="align: right"]$46,640[/TD]
[TD="align: right"]8,836,695.31[/TD]
[TD="align: right"]$8,883,336[/TD]
[TD="align: right"]$3,156,470[/TD]
[TD="align: right"]$3,314,293[/TD]
[TD="align: right"]$3,172,252[/TD]
[TD="align: right"]$142,041[/TD]
[TD="align: right"]$46,874[/TD]
[TD="align: right"]8,880,878.79[/TD]
[TD="align: right"]$8,927,752[/TD]
[TD="align: right"]$3,172,252[/TD]
[TD="align: right"]$3,330,865[/TD]
[TD="align: right"]$3,188,113[/TD]
[TD="align: right"]$142,751[/TD]
[TD="align: right"]$47,108[/TD]
[TD="align: right"]8,925,283.18[/TD]
[TD="align: right"]$8,972,391[/TD]
[TD="align: right"]$3,188,113[/TD]
[TD="align: right"]$3,347,519[/TD]
[TD="align: right"]$3,204,054[/TD]
[TD="align: right"]$143,465[/TD]
[TD="align: right"]$47,343[/TD]
[TD="align: right"]8,969,909.60[/TD]
[TD="align: right"]$9,017,253[/TD]
[TD="align: right"]$3,204,054[/TD]
[TD="align: right"]$3,364,256[/TD]
[TD="align: right"]$3,220,074[/TD]
[TD="align: right"]$144,182[/TD]
[TD="align: right"]$47,580[/TD]
[TD="align: right"]9,014,759.14[/TD]
[TD="align: right"]$9,062,339[/TD]
[TD="align: right"]$3,220,074[/TD]
[TD="align: right"]$3,381,078[/TD]
[TD="align: right"]$3,236,174[/TD]
[TD="align: right"]$144,903[/TD]
[TD="align: right"]$47,818[/TD]
[TD="align: right"]9,059,832.94[/TD]
[TD="align: right"]$9,107,651[/TD]
[TD="align: right"]$3,236,174[/TD]
[TD="align: right"]$3,397,983[/TD]
[TD="align: right"]$3,252,355[/TD]
[TD="align: right"]$145,628[/TD]
[TD="align: right"]$48,057[/TD]
[TD="align: right"]9,105,132.10[/TD]
[TD="align: right"]$9,153,189[/TD]
</tbody>