Financial Analysis Example – Planning for Retirement

Financial Analysis Example – Planning for Retirement

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 :

    1. Making a set of assumptions
    2. Choosing the right analytical concepts to incorporate (I.E., Discounting vs. Compounding, PV, FV, NPV, IRR, etc)
    3. Building a financial model (It’s better to build a basic case first and then expand by adding complexity)
    4. Validating your work
    5. 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:

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 .

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>