While a robust financial analysis example can teach you many things and span across numerous practical applications, there is none that is as relevant as the one involved in your own personal financial planning. This particular financial analysis example shows you how you can set up a spreadsheet model to evaluate and attain your personal financial goals. Relevant concepts such as Present Value, Future Value and Real Rate of Return, among others, are discussed. The exportable spreadsheet can be found at the end of the tutorial.
Although initial assumptions are forward looking, the way you structure your own model based on this financial analysis example will allow you to make modifications seamlessly in order to make new evaluations over time. It is important to note that financial markets are volatile and any assumptions you make in your analysis need to be tweaked as time goes on and more factual information is available to you.
*IMPORTANT NOTE*
This financial analysis example, as with any other that is robust (meaning concepts pertaining to time value of money is central to the analysis), involves :
- Making a set of assumptions
- Choosing the right analytical concepts to incorporate (I.E., Discounting vs. Compounding, PV, FV, NPV, IRR, etc)
- Building a financial model (It’s better to build a basic case first and then expand by adding complexity)
- Validating your work
- And finally, as mentioned earlier, tweak your assumptions as more information become available
There are a few basic concepts that you may find useful knowing beforehand as you read and work through the retirement planning financial analysis example below. First and foremost, understand that receiving$1 cash today is worth more than receiving the same $1 cash tomorrow, or anytime in the future for that matter. The reason is because inflation will reduce the purchasing power of that very dollar. Moreover, from an investment perspective, assuming that the markets will allow you to generate a positive return on your investment, you are better off taking that $1 today and investing it.
Secondly, understand that whenever you see a rate of return (APR, interest rate, etc) those are nominal rates. As with the cash example above, these rates need to be adjusted for inflation so that you get the true or “Real Rate of Return”. It makes no sense to put your money where you’re going to earn a 3% nominal return when the inflation rate is, say, 4%. Your money will actually be losing value with that investment.
One more thing that you should be aware of, if you’re already not, is that the younger you are, the more risks you can take when it comes to financial matters. You will have time to make adjustments, change game plan or do other things to recover. The older you are, the less flexibility you have and so it’s only natural to become risk averse over time.
With that said, let’s dive in and work through this personal financial analysis example. You may find it useful to plug the numbers in to a spreadsheet and build yourself a model as you go through the steps.
Financial Analysis Example Background Information:
Let’s assume that it’s Jan 1 and you are 30 years old today (your birthday). As you turned thirty, you reflected on your past and wondered about what the future holds. You come to a few realizations including the fact that you will definitely want to retire on the day you turn 65 and spend the next 20 years (your anticipated lifespan from retirement onward) traveling, spending time with grand kids and fishing. You realized that you’ve been a bit cavalier with your finances up to this point in your life and you need to make some changes that include putting money away for retirement. Thing is, you don’t have money to spare at the moment but you know your finances will free up late this year. So you plan to make your first contribution into a tax-free account towards your goal on your birthday next year and every year after that till your retirement.
During the day you think more about what you will need in the future and also do some research. You come to the conclusion that you can live comfortably on $100,000 a year (in today’s dollars) during retirement and expect inflation to run about 4% a year throughout your life. You also decide that you will take a riskier investment path up until 5 years before retiring and then shift gears into less risky investments. You expect that you can earn a 10% nominal rate of return per year up until the 5 years before retirement mark and then a 5% return onward till you’ve depleted your funds.
So how much will you need to contribute to that tax-free account every year until 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:
Let Z represent Annual Contributions
Z * (83.7375254) = Total Dollars Needed At Retirement
The next easiest thing to figure out is total dollars needed to meet your goal. Figuring out this number in a spreadsheet is pretty straight forward. We are interested in the Present Value of a future amount. To get this number, use the “PV” financial function in excel. Your inputs into an excel cell then would be the following based on the figures used in this financial analysis example:
As you can see even from the dialog above, the answer we are looking for is $1,828,982. Revisiting the previous formula for this financial analysis example, we now have
Let Z represent Annual Contributions
Z * (83.7375254) = $1,828,982
At this point, solving for “Z” or your annual contributions (in today’s dollars) is very easy. You just divide the total $ needed by the FV factor = $22,716.
There you have it – you will need to set aside $22, 716 yearly (in present value amount) in order to have the ability to have $100,000 (in today’s dollars) available to you annually during retirement.
Below is the spreadsheet that contains the model for this financial analysis example. You can export it as a spreadsheet and wait for it to load if your internet connection is slow.
Retirement Financial Analysis Example Spreadsheet
If you found financial analysis example helpful, you may also want to check out Cash Flow Analysis .