I'm working with a family that has received a large inheritance. They want to know how many years the funds will last given an assumed rate of interest on the funds declining principal and an annual drawdown (which is roughly $350,000). The assumptions are shown in the first two columns (below). The table works off of the assumptions for Years and Est. Return (along with an incremental change for both).
I would like to create a what-if table that would allow them to adjust the assumptions (i.e., interest rate, years, drawdown and incremental changes).
Thank you in advance for your help.
| Current Balance | $ 6,000,000 | 15 | 17 | 19 | 21 | 23 | 25 | 27 | 29 | 31 | 33 | 35 | ||
| Est. Return | 1.00% | 1.00% | ||||||||||||
| Incremental change | 0.50% | 1.50% | ||||||||||||
| Annual Drawdown (a) | $ (350,000) | 2.00% | ||||||||||||
| Req. Yrs. For Drawdown | 15 | 2.50% | ||||||||||||
| Incremental change | 2 | 3.00% | ||||||||||||
| 3.50% | ||||||||||||||
| 4.00% | ||||||||||||||
| (a) Reduction in principal from all sources. | 4.50% | |||||||||||||
| 5.00% | ||||||||||||||
| 5.50% | ||||||||||||||
| 6.00% | ||||||||||||||
| 6.50% | ||||||||||||||
| 7.00% | ||||||||||||||
| 7.50% | ||||||||||||||
| 8.00% | ||||||||||||||
| 8.50% | ||||||||||||||
| 9.00% | ||||||||||||||
Thank you for your question.
I will answer the question in a way that all the readers could apply it to any decision making problem they may have.
The idea is to create a “Data Table”. Once you create the initial formula (in your case cell B7) – it will be duplicated using all the values on the row and column of you table.
In the intersection (north west point of the table D1 you type =B7.
Select the table (D1:O18) and go to the data ribbon>What-if-Analysis and choose Data Table…
Inset in the data table menu the relevant inputs for the row and column and the formula will be duplicated for the entire table.
You can always email me ([email protected]) the Excel files and the correct formula you will to insert and I will email it back to you.
See attached image.
