Financial Analysis Example – Planning for Retirement
The first thing you’d want to do is make an organized list of assumptions. For this particular financial analysis example based on the narrative above, your assumptions list might look like this one:
Retirement Financial Analysis Example Assumptions: | |
General: | |
Birthday | January 1 |
Today’s Date | 1-Jan-10 |
Current Age | 30 |
Start Date of Annual Retirement Savings Contribution | 1-Jan-11 |
Age at First Contribution | 31 |
Final Date of Annual Retirement Savings Contribution | 1-Jan-44 |
Total Number of Annual Contributions | 34 |
Age at Final Contribution | 64 |
Retirement Date – Date of First Annual Withdrwal From Savings | 1-Jan-45 |
Retirement Age | 65 |
Years in Retirement – Total Number Of Withdrawls | 20 |
Date of Final Annual Withdrawl from Savings | 1-Jan-64 |
Financial / Economic: | |
Annual Withdrawl Amount (In Today’s Dollars) | $100,000 |
Average Inflation Rate Throughout Lifetime | 4% |
Annual Withdrawl Amount (In Next Year Dollars / Inflation Adjusted) — YEAR 0 | $104,000 |
Nominal Rate of Return: Pre – Retirement | |
Phase 1 (Years 0 – 28) – Total 29 Contributions | 10% |
Phase 2 (Years 29 – 33) | 5% |
Nominal Rate of Return: During Retirement | |
Phase 3 (Years 34 – 53) | 5% |
Next, you will have to convert your nominal rates of return to Real Rates of Return (inflation adjusted). Based on the assumptions above, you expect your real rates to be:
Financial Analysis Example Calculations:
Calculations | Notes (Spreadsheet Formulas) | |||
Real Rate of Return – Pre-Retirement | ||||
Phase 1 | 5.7692% | < — (((1+Phase1 Nominal Rate)/(1+Inflation Rate))-1) | ||
Phase 2 | 0.9615% | < — (((1+Phase2 Nominal Rate)/(1+Inflation Rate))-1) | ||
Real Rate of Return – During Retirement | ||||
Phase 3 | 0.9615% | < — (((1+Phase3 Nominal Rate)/(1+Inflation Rate))-1) |
Taking the calculations you have above, you can now work towards finding out how much in today’ s dollars you will need in total at retirement and subsequently what your yearly contributions will have to be in today’s dollars in order to meet your goals. In order to do that we need to know what kind of effect compounding has on the account.
When you are contributing money for a period of time without withdrawing any of it, the interest (or rate of return) your money earns will also earn money for you over time. This is what compounding does. If that was a bit difficult to understand, consider the following:
You put $100 into a savings account that pays 5% interest annually. So at the end of the year your account will have $105. Since you decide not withdraw any of it, the following year you account will have the $105 + interest earned on the original $100 = $5 + interest earned on the $5 interest you earned the previous year = $.25. Therefore your ending balance at the end of two years will be $110.25.
In our retirement financial analysis example, it’s a bit more complicated than the above, but the principle is the same. The effect of compounding needs to be quantified. The resulting number we will get is called the Future Value (FV) factor. The FV factor x Annual Contributions will equal the total dollars needed at retirement.
Let Z represent Annual Contributions
Z * (FV Factor) = Total Dollars Needed At Retirement
Since we are interested in seeing contributions and future need in terms of present value dollars, our future value factor needs to take that into consideration also. We normalize for this necessity by representing yearly contributions with a value of “1″ as its shown in column F below.
Financial Analysis Example Future Value Factor Calculations:
A | B | C | D | E | F | G | H |
Future Value Factor | |||||||
Nominal | Real | ||||||
Year | Rate of Return | Inflation Rate | Rate of Return | Begin Balance | Contribution | ROI – (In Real Terms) | Ending Balance Factor |
((1+B)/(1+C))-1 | (E+F)*D | (E+F+G) | |||||
0 | 10% | 4% | 5.7692% | 0 | 1 | 0.057692308 | 1.057692308 |
1 | 10% | 4% | 5.7692% | 1.057692308 | 1 | 0.118713018 | 2.176405325 |
2 | 10% | 4% | 5.7692% | 2.176405325 | 1 | 0.183254153 | 3.359659479 |
3 | 10% | 4% | 5.7692% | 3.359659479 | 1 | 0.251518816 | 4.611178295 |
4 | 10% | 4% | 5.7692% | 4.611178295 | 1 | 0.323721825 | 5.93490012 |
5 | 10% | 4% | 5.7692% | 5.93490012 | 1 | 0.400090392 | 7.334990511 |
6 | 10% | 4% | 5.7692% | 7.334990511 | 1 | 0.480864837 | 8.815855348 |
7 | 10% | 4% | 5.7692% | 8.815855348 | 1 | 0.566299347 | 10.3821547 |
8 | 10% | 4% | 5.7692% | 10.3821547 | 1 | 0.656662771 | 12.03881747 |
9 | 10% | 4% | 5.7692% | 12.03881747 | 1 | 0.752239469 | 13.79105694 |
10 | 10% | 4% | 5.7692% | 13.79105694 | 1 | 0.853330208 | 15.64438714 |
11 | 10% | 4% | 5.7692% | 15.64438714 | 1 | 0.960253104 | 17.60464025 |
12 | 10% | 4% | 5.7692% | 17.60464025 | 1 | 1.07334463 | 19.67798488 |
13 | 10% | 4% | 5.7692% | 19.67798488 | 1 | 1.192960666 | 21.87094554 |
14 | 10% | 4% | 5.7692% | 21.87094554 | 1 | 1.319477628 | 24.19042317 |
15 | 10% | 4% | 5.7692% | 24.19042317 | 1 | 1.453293644 | 26.64371682 |
16 | 10% | 4% | 5.7692% | 26.64371682 | 1 | 1.594829816 | 29.23854663 |
17 | 10% | 4% | 5.7692% | 29.23854663 | 1 | 1.744531536 | 31.98307817 |
18 | 10% | 4% | 5.7692% | 31.98307817 | 1 | 1.902869894 | 34.88594806 |
19 | 10% | 4% | 5.7692% | 34.88594806 | 1 | 2.070343157 | 37.95629122 |
20 | 10% | 4% | 5.7692% | 37.95629122 | 1 | 2.24747834 | 41.20376956 |
21 | 10% | 4% | 5.7692% | 41.20376956 | 1 | 2.434832859 | 44.63860242 |
22 | 10% | 4% | 5.7692% | 44.63860242 | 1 | 2.632996293 | 48.27159871 |
23 | 10% | 4% | 5.7692% | 48.27159871 | 1 | 2.842592233 | 52.11419095 |
24 | 10% | 4% | 5.7692% | 52.11419095 | 1 | 3.064280247 | 56.17847119 |
25 | 10% | 4% | 5.7692% | 56.17847119 | 1 | 3.298757953 | 60.47722915 |
26 | 10% | 4% | 5.7692% | 60.47722915 | 1 | 3.54676322 | 65.02399237 |
27 | 10% | 4% | 5.7692% | 65.02399237 | 1 | 3.809076483 | 69.83306885 |
28 | 10% | 4% | 5.7692% | 69.83306885 | 1 | 4.086523203 | 74.91959205 |
29 | 5% | 4% | 0.9615% | 74.91959205 | 1 | 0.729996077 | 76.64958813 |
30 | 5% | 4% | 0.9615% | 76.64958813 | 1 | 0.746630655 | 78.39621878 |
31 | 5% | 4% | 0.9615% | 78.39621878 | 1 | 0.763425181 | 80.15964396 |
32 | 5% | 4% | 0.9615% | 80.15964396 | 1 | 0.780381192 | 81.94002516 |
33 | 5% | 4% | 0.9615% | 81.94002516 | 1 | 0.797500242 |
83.7375254 |
Completing this Future Value (FV) table gives a FV factor of 83.7375254. So to revisit the formula, we now have: (continue reading)